Using Contexts to Store Global Data

from http://www.dba-oracle.com/plsql/t_plsql_contexts.htm

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

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.

posted @ 2014-07-17 15:13 princessd8251 阅读(149) 评论(0) 收藏举报

刷新页面返回顶部