Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | 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

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
GRANT EXECUTE ON DBMS_LOCK TO test;
CONN test/test

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:

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 test;
GRANT aq_administrator_role TO test;
GRANT aq_user_role TO test;
GRANT CREATE PROCEDURE TO test;
GRANT EXECUTE ON dbms_aq TO test;

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 test/test

-- 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        => 'test.slow_request_queue_tab',
    queue_payload_type => 'test.slow_request_type');
  
  DBMS_AQADM.create_queue (queue_name  => 'test.slow_request_queue',
                           queue_table => 'test.slow_request_queue_tab');
  
  
  DBMS_AQADM.start_queue (queue_name => 'test.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 => 'test.slow_request_queue',
                         enqueue    => TRUE);
  
  DBMS_AQADM.drop_queue (queue_name => 'test.slow_request_queue');
  
  DBMS_AQADM.drop_queue_table (queue_table => 'test.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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.