8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Decoupling to Improve Performance

No matter how much time you spend tuning some processes you just can't make them any faster. In some cases you can give the impression of better performance by decoupling the processes. This article gives an example of how to decouple a process and discusses the pros and cons.

The Problem
Decoupling Setup
Decoupling API
Decoupling Test
Cleanup
Conclusions

The Problem

Let's assume we have an application that calls a database procedure to perform an action, but the users are complaining the screen is too slow. We've spent time trying to tune the process, but it is still considered too slow. We will represent this process with the following procedure, using DBMS_LOCK.SLEEP to simulate a workload taking an excessive amount of time.

CONN / AS SYSDBA

-- DROP USER testuser1 CASCADE;

-- Create test user.
CREATE USER testuser1 IDENTIFIED BY testuser1
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE PROCEDURE TO testuser1;
GRANT EXECUTE ON DBMS_LOCK TO testuser1;


CONN testuser1/testuser1

CREATE OR REPLACE PROCEDURE slow_request (p_username      IN  VARCHAR2,
                                          p_request_info  IN  VARCHAR2) AS
BEGIN
  -- Pretend to do some work for 10 seconds.
  DBMS_LOCK.sleep(10);
END slow_request;
/
SHOW ERRORS

When we call the procedure it takes approximately 10 seconds to complete, which is why the users find it unacceptable.

SQL> SET TIMING ON
SQL> EXEC slow_request('Tim', 'Pay me lots of money!');

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.05
SQL> SET TIMING OFF
SQL>

We consult the users and they do not care if the processing is done immediately, they just want to guarantee their request has been made and will be processed at some point in the future, so we decide to decouple the request from the processing.

Decoupling Setup

The first thing we need to do is to decide how we are going to decouple the process. There are several methods available to us including:

Insert the user requests into a table to be processed later.
Use Oracle Advanced Queuing to queue the user requests.
Use the Oracle scheduler to fire one-off jobs for each request, so they start to be processed instantly, but the user does not have to wait for them to complete before having control returned to them.

In this example I will be using Oracle Advanced Queuing to manage the user requests.

Make sure the test user has the correct privileges to manage queues.

-- (adjust usernames as required).
CONNECT / AS SYSDBA
GRANT CREATE TYPE TO testuser1;
GRANT AQ_ADMINISTRATOR_ROLE, AQ_USER_ROLE TO testuser1;
GRANT EXECUTE ON DBMS_AQ TO testuser1;

Define a payload object that matches the parameters to the slow_request procedure, then use that payload to create a queue table. Create a queue within the queue table and start the queue.

CONNECT testuser1/testuser1

-- Create payload database type.
CREATE OR REPLACE TYPE slow_request_type AS OBJECT (
  username      VARCHAR2(30),
  request_info  VARCHAR2(2000)
);
/

-- Create and start queue.
BEGIN
  DBMS_AQADM.create_queue_table (
    queue_table        => USER || '.slow_request_queue_tab',
    queue_payload_type => USER || '.slow_request_type');
  
  DBMS_AQADM.create_queue (queue_name  => USER || '.slow_request_queue',
                           queue_table => USER || '.slow_request_queue_tab');
  
  
  DBMS_AQADM.start_queue (queue_name => USER || '.slow_request_queue',
                          enqueue    => TRUE);
END;
/

Decoupling API

With the basic setup in place, it's time to define an API to manage the user requests. The following package contains procedures to enqueue and dequeue messages along with a procedure to actually process the contents of the queue.

CREATE OR REPLACE PACKAGE slow_request_api AS
  PROCEDURE enqueue (p_username      IN  VARCHAR2,
                     p_request_info  IN  VARCHAR2);
  PROCEDURE dequeue (p_username      OUT  VARCHAR2,
                     p_request_info  OUT  VARCHAR2);
  PROCEDURE process_requests_job;
END slow_request_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY slow_request_api AS

  g_schema            VARCHAR2(30) := USER;
  g_process_job_loop  NUMBER       := 100;

  -- -----------------------------------------------------------------
  PROCEDURE enqueue (p_username      IN  VARCHAR2,
                     p_request_info  IN  VARCHAR2) AS
  -- -----------------------------------------------------------------
    l_enqueue_options     DBMS_AQ.enqueue_options_t;
    l_message_properties  DBMS_AQ.message_properties_t;
    l_message_handle      RAW(16);
    l_slow_request_msg    slow_request_type;
  BEGIN
    l_slow_request_msg := slow_request_type(p_username, p_request_info);

    DBMS_AQ.enqueue(queue_name          => g_schema || '.slow_request_queue',        
                    enqueue_options     => l_enqueue_options,     
                    message_properties  => l_message_properties,   
                    payload             => l_slow_request_msg,             
                    msgid               => l_message_handle);
  END enqueue;
  -- -----------------------------------------------------------------


  -- -----------------------------------------------------------------
  PROCEDURE dequeue (p_username      OUT  VARCHAR2,
                     p_request_info  OUT  VARCHAR2) AS
  -- -----------------------------------------------------------------
    l_dequeue_options     DBMS_AQ.dequeue_options_t;
    l_message_properties  DBMS_AQ.message_properties_t;
    l_message_handle      RAW(16);
    l_slow_request_msg    slow_request_type;
  BEGIN
    -- Don't wait on the queue for a message to arrive.
    l_dequeue_options.wait := DBMS_AQ.no_wait;
    
    DBMS_AQ.dequeue(queue_name          => g_schema || '.slow_request_queue',
                    dequeue_options     => l_dequeue_options,
                    message_properties  => l_message_properties,
                    payload             => l_slow_request_msg,
                    msgid               => l_message_handle);

    p_username      := l_slow_request_msg.username;
    p_request_info  := l_slow_request_msg.request_info;
  END dequeue;
  -- -----------------------------------------------------------------


  -- -----------------------------------------------------------------
  PROCEDURE process_requests_job AS
  -- -----------------------------------------------------------------
    l_username      VARCHAR2(30);
    l_request_info  VARCHAR2(2000);
    
    ex_timeout      EXCEPTION;
    PRAGMA EXCEPTION_INIT(ex_timeout, -25228);
  BEGIN
    -- Dequeuing one message at a time. Could improve performance using
    -- DBMS_AQ.DEQUEUE_ARRAY for array processing.
    FOR i IN 1 .. g_process_job_loop LOOP
      dequeue(p_username => l_username,
              p_request_info => l_request_info);
              
      slow_request(p_username => l_username,
                   p_request_info => l_request_info);
    END LOOP;
  EXCEPTION
    WHEN ex_timeout THEN
      -- Queue is empty.
      NULL;
  END process_requests_job;
  -- -----------------------------------------------------------------

END slow_request_api;
/
SHOW ERRORS

Decoupling Test

With the API in place we can test the impact of decoupling the process on the user experience.

SET SERVEROUTPUT ON
DECLARE
  l_start  NUMBER;
  l_loops  NUMBER := 5;
BEGIN
  -- Time the original method.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    slow_request(p_username     => 'Tim',
                 p_request_info => 'Pay me lots of money!');
  END LOOP;
  
  DBMS_OUTPUT.put_line('Original Method : ' || 
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  -- Time the decoupled method.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    slow_request_api.enqueue(
      p_username    => 'Tim',
     p_request_info => 'This is dummy data');
  END LOOP;
  
  DBMS_OUTPUT.put_line('Decoupled Method: ' || 
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  -- Time the background processing.
  l_start := DBMS_UTILITY.get_time;

  slow_request_api.process_requests_job;
  
  DBMS_OUTPUT.put_line('Background Proc : ' || 
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
Original Method : 5006 hsecs
Decoupled Method: 1 hsecs
Background Proc : 5010 hsecs

PL/SQL procedure successfully completed.

Elapsed: 00:01:40.21
SQL>

Not surprisingly, five requests to the original procedure took approximately 50 seconds to process, while five decoupled requests completed almost instantly. This marks a dramatic improvement in user experience of our application, but we have to remember the work must get done at some point! As we can see, the processing still takes the same length of time once we process the queue.

Cleanup

The following code removes the sample code and the queue definition.

DROP PACKAGE slow_request_api;
DROP PROCEDURE slow_request;

BEGIN
  DBMS_AQADM.stop_queue (queue_name => USER || '.slow_request_queue',
                         enqueue    => TRUE);
  
  DBMS_AQADM.drop_queue (queue_name => USER || '.slow_request_queue');
  
  DBMS_AQADM.drop_queue_table (queue_table => USER || '.slow_request_queue_tab');
END;
/

DROP TYPE slow_request_type;

Conclusions

Here are a few of the conclusions we can draw from this experience of decoupling.

Decoupling processes allows us to greatly improve the user experience of our applications by making the processing asynchronous.
The users must understand the processing is decoupled or they may get confused when their changes are not instantly apparent.
We are not actually improving the performance of the processing, just moving the problem somewhere else.
We should only queue requests if the effort of queuing the request is significantly less than the effort of performing the processing immediately.
We would need to decide on a schedule for running the process_requests_job procedure.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.