Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Decoupling (Cheating) for Performance


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

Application performance is a relative thing.  A user may find an application irritatingly slow, while a developer is in aw of how fast the application is considering the processing requirements.  When an application has been tuned but the user experience is still negative, one option is to decouple the process making it asynchronous.  The reason I call this cheating is that it is not actually improving the speed of the process, it simply hides it from the user.

As a means of demonstrating this letís assume we have a stored procedure called slow_request.sql that performs all the processing for a specific user request that is considered too slow. 

slow_request.sql

-- *****************************************************************
-- Requirements:
--   GRANT EXECUTE ON dbms_lock TO username;
-- *****************************************************************

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

Rather than call this directly from the user process we can decouple it by adding the request onto a queue to be processed later.  The slow_request_api_setup.sql script creates a suitable queue table using the Oracle advanced queuing feature.

slow_request_api_setup.sql

-- Grant the relevant privileges (adjust usernames as required).

CONNECT sys/password 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;

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

The script starts by granting the relevant privileges to the test user, adjust the usernames and passwords as needed.  It then creates the slow_request_type database type which acts as the payload for the queue.  Finally the queue is created and started.

With the queue in place the next step is to create an API which will allow us to add and remove from the queue.  The slow_request_api.sql script does this by creating a package specification and body which requires the presence of the queue table and the slow_request procedure.

slow_request_api.sql

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

The enqueue procedure adds messages to the queue while the dequeue procedure removes them.  The process_requests_job procedure is an example of the way the decoupled processing could be handled in the background.

With the API in place we can now do some performance comparisons between the original and the decoupled methods using the slow_request_api_test.sql script.

slow_request_api_test.sql

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

  FOR i IN 1 .. 10 LOOP
    slow_request(p_username     => 'Tim',
                 p_request_info => 'This is dummy data');
  END LOOP; 

  DBMS_OUTPUT.put_line('Original Method : ' ||
                       (DBMS_UTILITY.get_time - l_start));

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

  FOR i IN 1 .. 10 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));

  -- 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));
END;
/

The script displays the time taken to perform 10 of the original requests, followed by 10 of the decoupled requests and finally the time take to process the decouple requests.  The output from the script is displayed below.

SQL> @slow_request_api_test.sql
Original Method : 10241
Decoupled Method: 1
Background Proc : 10242

PL/SQL procedure successfully completed.

From this we can see that the original requests took approximately 10 seconds per request to complete, while the decoupled requests took one hundredth of a second in total.  This represents a massive improvement in terms of user experience.  The final timing shows that the background processing of the decoupled requests took approximately 10 seconds per request.

There are some important points to consider before using this technique:

  • You are not improving the speed of the process, just the perception of the speed to the user.

  • Users must be aware that their requests have been queued, not completed.  Some application functionality cannot be decoupled due to the synchronous nature of the requirement.

  • The background processing should be automated such that the queue does not build up to an unacceptable level.  The required schedule depends on the nature of the task being decoupled.

If youíve not used queue tables before the cleanup process can be a little confusing, so the slow_request_api_cleanup.sql script shows how to remove the example objects.

slow_request_api_cleanup.sql

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;

 

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational