A package-based cache consists of one or more variables declared at the package level,rather than in any subprogram of the package.Package-level data is a candidate for caching, because this kind of data persists throughout a session,even if programs in that session are not currently using the data or calling any of the subprograms in the package.In other words,if you declare a variable at the package level,once you assign a value to that variable it keeps that value until you disconnect,recompile the package,or change the value.
SQL> CREATE OR REPLACE PACKAGE thisuser
2 IS
3 cname CONSTANT VARCHAR2(30) := USER;
4 FUNCTION name RETURN VARCHAR2;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY thisuser
2 IS
3 g_user VARCHAR2(30) := USER;
4 FUNCTION name RETURN VARCHAR2
5 IS
6 BEGIN
7 RETURN g_user;
8 END;
9 END;
10 /
Package body created.
SQL> CREATE OR REPLACE PROCEDURE test_thisuser(count_in IN PLS_INTEGER)
2 IS
3 l_name all_users.username%TYPE;
4 l_start_time NUMBER;
5 l_end_time NUMBER;
6 l_diff NUMBER;
7 BEGIN
8 l_start_time := DBMS_UTILITY.GET_TIME;
9
10 FOR indx IN 1 .. count_in
11 LOOP
12 l_name := thisuser.name;
13 END LOOP;
14
15 l_end_time := DBMS_UTILITY.GET_TIME;
16
17 l_diff := l_end_time - l_start_time;
18
19 DBMS_OUTPUT.PUT_LINE('Package Function (BODY) Elapsed ::: '||l_diff);
20
21 l_start_time := 0;
22 l_end_time := 0;
23 l_diff := 0;
24
25 l_start_time := DBMS_UTILITY.GET_TIME;
26
27 FOR indx IN 1 .. count_in
28 LOOP
29 l_name := thisuser.cname;
30 END LOOP;
31
32 l_end_time := DBMS_UTILITY.GET_TIME;
33
34 l_diff := l_end_time - l_start_time;
35
36 DBMS_OUTPUT.PUT_LINE('Package Constant (SPEC) Elapsed ::: '||l_diff);
37
38 l_start_time := 0;
39 l_end_time := 0;
40 l_diff := 0;
41
42 l_start_time := DBMS_UTILITY.GET_TIME;
43
44 FOR indx IN 1 .. count_in
45 LOOP
46 l_name := USER;
47 END LOOP;
48
49 l_end_time := DBMS_UTILITY.GET_TIME;
50
51 l_diff := l_end_time - l_start_time;
52
53 DBMS_OUTPUT.PUT_LINE('USER Function Elapsed ::: '||l_diff);
54 END test_thisuser;
55 /
Procedure created.
SQL> EXEC test_thisuser(100);
Package Function (BODY) Elapsed ::: 0
Package Constant (SPEC) Elapsed ::: 0
USER Function Elapsed ::: 0
PL/SQL procedure successfully completed.
SQL> EXEC test_thisuser(1000000);
Package Function (BODY) Elapsed ::: 14
Package Constant (SPEC) Elapsed ::: 2
USER Function Elapsed ::: 129
PL/SQL procedure successfully completed.
--For small numbers of iterations,the advantage of caching is not apparent.However,for large numbers of iterations,the package-based cache is dramatically faster than going through the SQL layer and the SGA.Accessing the constant is faster than calling a function that returns the value.So why use a function? The function version offers this advantage over the constant : it hides the value.So,if for any reason the value must be changed,you can do so without recompiling the package specification,which would force recompilation of all programs dependent on this package.
SQL> CREATE TABLE products
2 (
3 product_number INTEGER PRIMARY KEY,
4 description VARCHAR2(1000)
5 );
Table created.
SQL> DECLARE
2 TYPE products_t IS TABLE OF products%ROWTYPE INDEX BY PLS_INTEGER;
3 l_rows products_t;
4 BEGIN
5 FOR indx IN 1 .. 10000
6 LOOP
7 l_rows (indx).product_number := indx;
8 l_rows (indx).description := 'Product ' || TO_CHAR (indx);
9 END LOOP;
10
11 FORALL indx IN 1 .. 10000
12 INSERT INTO products
13 VALUES l_rows (indx);
14 COMMIT;
15 END;
16 /
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PACKAGE products_cache
2 IS
3 FUNCTION with_sql (product_number_in IN products.product_number%TYPE) RETURN products%ROWTYPE;
4 FUNCTION from_cache (product_number_in IN products.product_number%TYPE) RETURN products%ROWTYPE;
5 FUNCTION jit_from_cache (product_number_in IN products.product_number%TYPE) RETURN products%ROWTYPE;
6 END products_cache;
7 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY products_cache
2 IS
3 TYPE cache_t IS TABLE OF products%ROWTYPE INDEX BY PLS_INTEGER;
4 g_cache cache_t;
5
6 FUNCTION with_sql (product_number_in IN products.product_number%TYPE)
7 RETURN products%ROWTYPE
8 IS
9 l_row products%ROWTYPE;
10 BEGIN
11 SELECT *
12 INTO l_row
13 FROM products
14 WHERE product_number = product_number_in;
15
16 RETURN l_row;
17 END with_sql;
18
19 FUNCTION from_cache (product_number_in IN products.product_number%TYPE)
20 RETURN products%ROWTYPE
21 IS
22 BEGIN
23 RETURN g_cache (product_number_in);
24 END from_cache;
25
26 FUNCTION jit_from_cache (product_number_in IN products.product_number%TYPE
27 )
28 RETURN products%ROWTYPE
29 IS
30 l_row products%ROWTYPE;
31 BEGIN
32 IF g_cache.EXISTS (product_number_in)
33 THEN
34 l_row := g_cache (product_number_in);
35 ELSE
36 l_row := with_sql (product_number_in);
37 g_cache (product_number_in) := l_row;
38 END IF;
39
40 RETURN l_row;
41 END jit_from_cache;
42 BEGIN
43 SELECT *
44 BULK COLLECT
45 INTO g_cache
46 FROM products;
47 END products_cache;
48 /
Package body created.
SQL> DECLARE
2 l_row products%ROWTYPE;
3 l_start_time NUMBER;
4 l_end_time NUMBER;
5 l_diff NUMBER;
6 BEGIN
7 l_start_time := DBMS_UTILITY.GET_TIME;
8
9 FOR indx IN 1 .. 100000
10 LOOP
11 l_row := products_cache.from_cache (5000);
12 END LOOP;
13
14 l_end_time := DBMS_UTILITY.GET_TIME;
15
16 l_diff := l_end_time - l_start_time;
17
18 DBMS_OUTPUT.PUT_LINE('FROM_CACHE ::: '||l_diff);
19
20 l_start_time := 0;
21 l_end_time := 0;
22 l_diff := 0;
23
24 l_start_time := DBMS_UTILITY.GET_TIME;
25
26 FOR indx IN 1 .. 100000
27 LOOP
28 l_row := products_cache.with_sql (5000);
29 END LOOP;
30
31 l_end_time := DBMS_UTILITY.GET_TIME;
32
33 l_diff := l_end_time - l_start_time;
34
35 DBMS_OUTPUT.PUT_LINE('WITH_SQL ::: '||l_diff);
36
37 l_start_time := 0;
38 l_end_time := 0;
39 l_diff := 0;
40
41 l_start_time := DBMS_UTILITY.GET_TIME;
42
43 FOR indx IN 1 .. 100000
44 LOOP
45 l_row := products_cache.jit_from_cache (5000);
46 END LOOP;
47
48 l_end_time := DBMS_UTILITY.GET_TIME;
49
50 l_diff := l_end_time - l_start_time;
51
52 DBMS_OUTPUT.PUT_LINE('JIT_FROM_CACHE ::: '||l_diff);
53 END;
54 /
--OUTPUT :::
FROM_CACHE ::: 9
WITH_SQL ::: 120
JIT_FROM_CACHE ::: 13
