 |
|
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;
|
and include the URL for the page.
