 |
|
Using Contexts to Store
Global Data
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:
An alternative to the previous method is to
use contexts to store the global data. A context is a set of
application-defined attributes associated with a namespace that is
linked to a managing package. A context is created using the
CREATE CONTEXT statement in which the namespace equates to the context
name.
CREATE
CONTEXT namespace USING package-name;
The managing package does not have to be
present at the point when the context is created, but must be present
before it is referenced. The dbms_session.set_context procedure
is used to associate name-value pairs with the context, but for
security purposes this procedure can only be used from within the
package associated with the context.
Context attributes can be read using the
sys_context function available from SQL and PL/SQL. The
following example uses this function to retrieve the current session
identifier from the default userenv context.
SQL> SELECT
SYS_CONTEXT('USERENV', 'SESSIONID') FROM dual;
SYS_CONTEXT('USERENV','SESSION
--------------------------------------------------------------------
328385
1 row
selected.
To show how contexts can be used to cache
global session data, the examples from the previous section using
contexts instead of package variables will be recreated. In
order to do this, the CREATE ANY CONTEXT privilege must be granted to
the owner of the context, and just for the purpose of this example,
the CREATE TRIGGER privilege is also necessary.
GRANT CREATE
ANY CONTEXT TO username;
GRANT CREATE TRIGGER TO username;
The global_context.sql script creates a
context named global_context along with a supporting package
specification, and the body named global_context_api referenced by the
context. It relies on the presence of the system_parameters
table created previously using the create_cached_lookup_tab.sql
script. In addition it creates a logon trigger to initialize the
package as soon as the session logon occurs.
global_context.sql
-- Create
the context referencing global_api.
CREATE OR REPLACE CONTEXT global_context USING global_context_api;
-- Create the package specification and body to support the context.
CREATE OR REPLACE PACKAGE global_context_api AS
PROCEDURE set_parameter(p_name IN VARCHAR2,
p_value IN VARCHAR2);
PROCEDURE initialize;
END global_context_api;
/
SHOW ERRORS
CREATE OR
REPLACE PACKAGE BODY global_context_api IS
-- -----------------------------------------------------------------
PROCEDURE set_parameter (p_name IN VARCHAR2,
p_value IN VARCHAR2) IS
-- -----------------------------------------------------------------
BEGIN
DBMS_SESSION.set_context('global_context', p_name, p_value);
END set_parameter;
-- -----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE initialize AS
-- -----------------------------------------------------------------
l_audit_on system_parameters.audit_on%TYPE;
l_trace_on system_parameters.trace_on%TYPE;
l_debug_on system_parameters.debug_on%TYPE;
BEGIN
SELECT audit_on,
trace_on,
debug_on
INTO l_audit_on,
l_trace_on,
l_debug_on
FROM system_parameters;
set_parameter(‘audit_on’, l_audit_on);
set_parameter(‘trace_on’, l_trace_on);
set_parameter(‘debug_on’, l_debug_on);
END initialize;
-- -----------------------------------------------------------------
END
global_context_api;
/
SHOW ERRORS
-- Create
trigger to initialize the collection.
CREATE OR REPLACE TRIGGER after_logon_trg AFTER
LOGON ON SCHEMA BEGIN
global_context_api.initialize;
END;
/
SHOW ERRORS
The initialize procedure and trigger are only
necessary for this example and is not a prerequisite for context usage
in general. The initialize procedure retrieves the data from the
system_parameters table and sets this in the context. In order
for the following examples to work correctly, the context must be
initialized by either reconnecting to the database or manually calling
the initialize procedure.
SQL> EXEC
global_context_api.initialize;
PL/SQL procedure successfully completed.
All context data for the current session can
be displayed using the session_context view, as demonstrated by the
session_context.sql script below.
session_context.sql
SET LINESIZE
200
COLUMN
namespace FORMAT A20
COLUMN attribute FORMAT A20
COLUMN value FORMAT A20
SELECT
namespace,
attribute,
value
FROM session_context
ORDER BY namespace, attribute;
The output from this script shows that the
context has been initialized as expected.
SQL> @session_context.sql
NAMESPACE
ATTRIBUTE
VALUE
-------------------- -------------------- --------------------
GLOBAL_CONTEXT AUDIT_ON
Y
GLOBAL_CONTEXT DEBUG_ON
N
GLOBAL_CONTEXT TRACE_ON
N
3 rows
selected.
SQL>
The global_context_api_test.sql script shows
how the values of the context variables can be set and retrieved using
the set_parameter procedure and the sys_context function.
global_context_api_test.sql
SET
SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.put_line('Show global data');
DBMS_OUTPUT.put_line('audit_on: ' || SYS_CONTEXT('global_context', 'audit_on'));
DBMS_OUTPUT.put_line('trace_on: ' || SYS_CONTEXT('global_context', 'trace_on'));
DBMS_OUTPUT.put_line('debug_on: ' || SYS_CONTEXT('global_context', 'debug_on'));
DBMS_OUTPUT.put_line('Reset global data');
global_context_api.set_parameter('audit_on','N');
global_context_api.set_parameter('trace_on','Y');
global_context_api.set_parameter('debug_on','Y');
DBMS_OUTPUT.put_line('audit_on: ' || SYS_CONTEXT('global_context', 'audit_on'));
DBMS_OUTPUT.put_line('trace_on: ' || SYS_CONTEXT('global_context', 'trace_on'));
DBMS_OUTPUT.put_line('debug_on: ' || SYS_CONTEXT('global_context', 'debug_on'));
END;
/
The results of this script clearly show that the global
variables are both initialized and reset as expected.
SQL> @global_context_api_test.sql
Show global
data
audit_on: Y
trace_on: N
debug_on: N
Reset global data
audit_on: N
trace_on: Y
debug_on: Y
PL/SQL
procedure successfully completed.
The secure_global_context_api_body.sql script
provides an alternate package body that includes validation within the
set_parameter procedure to restrict access to the audit and trace
variables and to validate the debug setting.
secure_global_context_api_body.sql
CREATE OR
REPLACE PACKAGE BODY global_context_api IS
--
-----------------------------------------------------------------
PROCEDURE set_parameter (p_name IN VARCHAR2,
p_value IN VARCHAR2) IS
-- -----------------------------------------------------------------
BEGIN
IF UPPER(p_name) IN ('AUDIT_ON', 'TRACE_ON') THEN
RAISE_APPLICATION_ERROR(-20000, 'Alteration of ' || p_name || ' value
is prohibited.');
ELSIF UPPER(p_name) = 'DEBUG_ON'
AND p_value NOT IN ('Y', 'N') THEN
RAISE_APPLICATION_ERROR(-20001, 'debug_on must be set to Y or
N.');
END IF;
DBMS_SESSION.set_context('global_context', p_name, p_value);
END set_parameter;
-- -----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE initialize AS
-- -----------------------------------------------------------------
l_audit_on system_parameters.audit_on%TYPE;
l_trace_on system_parameters.trace_on%TYPE;
l_debug_on system_parameters.debug_on%TYPE;
BEGIN
SELECT audit_on,
trace_on,
debug_on
INTO l_audit_on,
l_trace_on,
l_debug_on
FROM system_parameters;
DBMS_SESSION.set_context('global_context', 'audit_on', l_audit_on);
DBMS_SESSION.set_context('global_context', 'trace_on', l_trace_on);
DBMS_SESSION.set_context('global_context', 'debug_on', l_debug_on);
END initialize;
-- -----------------------------------------------------------------
END
global_context_api;
/
SHOW ERRORS
While running the global_context_api_test.sql
script against this alternate package body, an error results when an
attempt is made to amend the audit variable.
SQL> @global_context_api_test.sql
Show global data
audit_on: Y
trace_on: N
debug_on: N
Reset global data
BEGIN
ERROR at
line 1:
ORA-20000: Alteration of audit_on value is prohibited.
ORA-06512: at "TEST.GLOBAL_CONTEXT_API", line 9
ORA-06512: at line 8
The global_context_comparison.sql script
compares the performance of a database query with a context read.
global_context_comparison.sql
--
*****************************************************************
-- Parameters: 1) loops - The number of loop iterations.
-- *****************************************************************
SET VERIFY
OFF
SET SERVEROUTPUT ON
DECLARE
l_start
NUMBER;
l_loops NUMBER := &1;
l_debug_on system_parameters.debug_on%TYPE;
BEGIN
-- Time the DB lookup.
l_start := DBMS_UTILITY.get_time;
FOR i
IN 1 .. l_loops LOOP
SELECT debug_on
INTO l_debug_on
FROM system_parameters;
END LOOP;
DBMS_OUTPUT.put_line('DB Lookup (' || l_loops || ' rows)
: ' ||
(DBMS_UTILITY.get_time
- l_start));
--
Time the Cached lookup.
l_start := DBMS_UTILITY.get_time;
FOR i
IN 1 .. l_loops LOOP
l_debug_on := SYS_CONTEXT(‘global_context’, ‘debug_on’);
END LOOP;
DBMS_OUTPUT.put_line('Context Lookup (' || l_loops || ' rows): ' ||
(DBMS_UTILITY.get_time - l_start));
END; /
The increased speed of the context approach is evident from the
results of this script.
SQL> @global_context_comparison.sql
10
DB Lookup (10 rows) : 0
Context Lookup (10 rows): 0
PL/SQL
procedure successfully completed.
SQL> @global_context_comparison.sql
100
DB Lookup (100 rows) : 2
Context Lookup (100 rows): 0
PL/SQL
procedure successfully completed.
SQL> @global_context_comparison.sql
1000
DB Lookup (1000 rows) : 19
Context Lookup (1000 rows): 1
PL/SQL
procedure successfully completed.
SQL> @global_context_comparison.sql
10000
DB Lookup (10000 rows) : 190
Context Lookup (10000 rows): 8
PL/SQL
procedure successfully completed.
SQL> @global_context_comparison.sql
100000
DB Lookup (100000 rows) : 1892
Context Lookup (100000 rows): 75
PL/SQL
procedure successfully completed.
As with the use of package variables, although
a definite performance improvement can be seen, the level to which
contexts affects the overall system performance depends on the extent
to which you are already using global variables and how you are
currently implementing them.
In addition to caching session data, contexts
can be used to cache information for the whole instance. Adding the
ACCESSED GLOBALLY clause to the end of the context creation statement
means the context data is available to all sessions.
CREATE
CONTEXT context-name USING package-name ACCESSED GLOBALLY;
This ability makes it possible to use this
feature to provide inter-session communication.
|
and include the URL for the page.
