 |
|
Using Package Variables
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:
The previous section described the performance
advantages associated with caching frequently accessed lookup data in
package variables. A similar technique can be employed for
individual global variables. The examples in this section rely
on the presence of the system_parameters table, which is created and
populated using the create_system_parameters_tab.sql script.
create_system_parameters_tab.sql
CREATE TABLE
system_parameters (
audit_on VARCHAR2(1) NOT NULL,
trace_on VARCHAR2(1) NOT NULL,
debug_on VARCHAR2(1) NOT NULL
);
INSERT INTO
system_parameters VALUES ('Y', 'N', 'N');
COMMIT;
The system_parameters table holds the default
values for system parameters relating to the audit, trace and debug
functionality. When using this data, a choice is available of
checking the table values each time an operation is performed or
loading it into package variables and relying on those values.
The global_data_api.sql script creates a package specification and
body to cache the system_parameters data.
global_data_api.sql
CREATE OR
REPLACE PACKAGE global_data_api AS
audit_on
system_parameters.audit_on%TYPE;
trace_on system_parameters.trace_on%TYPE;
debug_on system_parameters.debug_on%TYPE;
PROCEDURE
initialize;
END
global_data_api;
/
SHOW ERRORS
CREATE OR
REPLACE PACKAGE BODY global_data_api AS
-
-----------------------------------------------------------------
PROCEDURE initialize AS
-- -----------------------------------------------------------------
BEGIN
SELECT audit_on,
trace_on,
debug_on
INTO audit_on,
trace_on,
debug_on
FROM system_parameters;
END initialize;
-- -----------------------------------------------------------------
BEGIN
initialize;
END global_data_api;
/
SHOW ERRORS
The package variables are initialized from the base table data
by the initialize procedure executed in the initialization block of
the package body.
The global_data_api_test.sql script contains
an anonymous block showing how the global data can be accessed.
global_data_api_test.sql
SET
SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.put_line('Show global data');
DBMS_OUTPUT.put_line('audit_on: ' || global_data_api.audit_on);
DBMS_OUTPUT.put_line('trace_on: ' || global_data_api.trace_on);
DBMS_OUTPUT.put_line('debug_on: ' || global_data_api.debug_on);
DBMS_OUTPUT.put_line('Reset global data');
global_data_api.audit_on := 'N';
global_data_api.trace_on := 'Y';
global_data_api.debug_on := 'Y';
DBMS_OUTPUT.put_line('audit_on: ' || global_data_api.audit_on);
DBMS_OUTPUT.put_line('trace_on: ' || global_data_api.trace_on);
DBMS_OUTPUT.put_line('debug_on: ' || global_data_api.debug_on);
END;
/
The values of the package variables can be set
to any value in addition to being read. This is because the
package variables are defined in the package specification so they are
publicly accessible. The output from the
global_data_api_test.sql script is listed below.
SQL> @global_data_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_data_api.sql script presents
an alternate method whereby the package variables are defined in the
package body and are protected from outside interference. The
values of all the variables are read via get_% functions, while the
value of the debug flag is set via set_% procedures which validate the
new value.
secure_global_data_api.sql
CREATE OR
REPLACE PACKAGE secure_global_data_api AS
PROCEDURE
initialize;
FUNCTION get_audit_on RETURN system_parameters.audit_on%TYPE;
FUNCTION get_trace_on RETURN system_parameters.trace_on%TYPE;
PROCEDURE set_debug_on (p_debug_on IN
system_parameters.debug_on%TYPE);
FUNCTION get_debug_on RETURN system_parameters.debug_on%TYPE;
END
secure_global_data_api;
/
SHOW ERRORS
CREATE OR
REPLACE PACKAGE BODY secure_global_data_api AS
audit_on
system_parameters.audit_on%TYPE;
trace_on system_parameters.trace_on%TYPE;
debug_on system_parameters.debug_on%TYPE;
--
-----------------------------------------------------------------
PROCEDURE initialize AS
-- -----------------------------------------------------------------
BEGIN
SELECT audit_on,
trace_on,
debug_on
INTO audit_on,
trace_on,
debug_on
FROM system_parameters;
END initialize;
-- -----------------------------------------------------------------
--
-----------------------------------------------------------------
FUNCTION get_audit_on RETURN system_parameters.audit_on%TYPE AS
-- -----------------------------------------------------------------
BEGIN
RETURN audit_on;
END get_audit_on;
-- -----------------------------------------------------------------
--
-----------------------------------------------------------------
FUNCTION get_trace_on RETURN system_parameters.trace_on%TYPE AS
-- -----------------------------------------------------------------
BEGIN
RETURN trace_on;
END get_trace_on;
-- -----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE set_debug_on (p_debug_on IN
system_parameters.debug_on%TYPE) AS
-- -----------------------------------------------------------------
BEGIN
IF p_debug_on NOT IN ('Y', 'N') THEN
RAISE_APPLICATION_ERROR(-20000, 'debug_on must be set to Y or
N.');
END IF;
debug_on := p_debug_on;
END set_debug_on;
-- -----------------------------------------------------------------
--
-----------------------------------------------------------------
FUNCTION get_debug_on RETURN system_parameters.debug_on%TYPE AS
-- -----------------------------------------------------------------
BEGIN
RETURN debug_on;
END get_debug_on;
-- -----------------------------------------------------------------
BEGIN
initialize;
END secure_global_data_api;
/
SHOW ERRORS
The
secure_global_data_api_test.sql script shows how the global variables
are now referenced.
secure_global_data_api_test.sql
SET
SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.put_line('Show global data');
DBMS_OUTPUT.put_line('audit_on: ' || secure_global_data_api.get_audit_on);
DBMS_OUTPUT.put_line('trace_on: ' || secure_global_data_api.get_trace_on);
DBMS_OUTPUT.put_line('debug_on: ' || secure_global_data_api.get_debug_on);
DBMS_OUTPUT.put_line('Reset global data where possible');
secure_global_data_api.set_debug_on(p_debug_on => 'Y');
DBMS_OUTPUT.put_line('audit_on: ' || secure_global_data_api.get_audit_on);
DBMS_OUTPUT.put_line('trace_on: ' || secure_global_data_api.get_trace_on);
DBMS_OUTPUT.put_line('debug_on: ' || secure_global_data_api.get_debug_on);
DBMS_OUTPUT.put_line('Reset debug incorrectly');
secure_global_data_api.set_debug_on(p_debug_on => 'A');
END;
/
The output from this script shows that only
the debug flag is altered because it is the only one with a set
method, and altering it to an invalid value results in an error.
SQL> @secure_global_data_api_test.sql
Show global data
audit_on: Y
trace_on: N
debug_on: N
Reset global data where possible
audit_on: Y
trace_on: N
debug_on: Y
Reset debug incorrectly
BEGIN
*
ERROR at line 1:
ORA-20000: debug_on must be set to Y or N.
ORA-06512: at "TEST.SECURE_GLOBAL_DATA_API", line 45
ORA-06512: at line 14
The global_data_comparison.sql script compares
the performance of a query, a direct package variable read and get_%
function read.
global_data_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 := global_data_api.debug_on;
END LOOP;
DBMS_OUTPUT.put_line('Variable 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 := secure_global_data_api.get_debug_on;
END LOOP;
DBMS_OUTPUT.put_line('Function Lookup (' || l_loops || ' rows): ' ||
(DBMS_UTILITY.get_time - l_start));
END;
/
The increased speed of the two package
variable approaches is evident from the results of this script,
although a small performance overhead is associated with wrapping the
package variable read in a function.
SQL> @global_data_comparison.sql
10
DB Lookup (10 rows) : 1
Variable Lookup (10 rows): 0
Function Lookup (10 rows): 0
PL/SQL
procedure successfully completed.
SQL> @global_data_comparison.sql
100
DB Lookup (100 rows) : 2
Variable Lookup (100 rows): 0
Function Lookup (100 rows): 0
PL/SQL
procedure successfully completed.
SQL> @global_data_comparison.sql
1000
DB Lookup (1000 rows) : 18
Variable Lookup (1000 rows): 0
Function Lookup (1000 rows): 1
PL/SQL
procedure successfully completed.
SQL> @global_data_comparison.sql
10000
DB Lookup (10000 rows) : 181
Variable Lookup (10000 rows): 0
Function Lookup (10000 rows): 2
PL/SQL
procedure successfully completed.
QSL> @global_data_comparison.sql
100000
DB Lookup (100000 rows) : 1817
Variable Lookup (100000 rows): 0
Function Lookup (100000 rows): 22
PL/SQL
procedure successfully completed.
* Personally I think this has more to do with
programming style than performance since all the numbers are small
when considering the total volume of reads.
In addition to using package variables, global
data can be stored using contexts; the subject of the next section.
|
and include the URL for the page.
