set serveroutput on
DECLARE
x NUMBER := 1000000;
CRM_DATE varchar2(20) ;
n1 number;
BEGIN
n1 := dbms_utility.get_time;
FOR i IN 1..x LOOP
--CRM_DATE :=TO_char(SYSDATE);
select sysdate into CRM_DATE from dual;
END LOOP;
dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/
4041
count a empty table for 1 millions times - 61.28 seconds
set serveroutput on
DECLARE
x NUMBER := 1000000;
CRM_DATE varchar2(20) ;
n1 number;
c number;
BEGIN
n1 := dbms_utility.get_time;
FOR i IN 1..x LOOP
--CRM_DATE :=TO_char(SYSDATE);
-- select sysdate into CRM_DATE from dual;
SELECT COUNT(*) into c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
END LOOP;
dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/
6128
18057 for looping 3 millions times
now test a function and cache it
CREATE OR REPLACE FUNCTION count_crm_date
RETURN NUMBER
RESULT_CACHE
AS
c number;
BEGIN
SELECT count(*) INTO c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
RETURN c;
END count_crm_date;
/
ensure it works , meanwhile cached it.
> var r number;
> exec :r :=count_crm_date(1);
PL/SQL procedure successfully completed.
> set serveroutput on
> print r
R
----------
0
create a procedure to loop the function.
CREATE OR REPLACE PROCEDURE run_test AS
l_start NUMBER;
l_loops NUMBER := 1000000;
l_value number;
BEGIN
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
l_value := count_crm_date;
END LOOP;
DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. l_loops LOOP
l_value := count_crm_date;
END LOOP;
DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END run_test;
/
Procedure created.
> EXEC run_test;
First Loop: 86 hsecs (0.86 second)
Second Loop: 81 hsecs (0.81 second)
PL/SQL procedure successfully completed.
That is much faster , 0.81 seconds compare to not-cached 61 seconds, about 75x faster
SELECT DBMS_RESULT_CACHE.status FROM dual;
STATUS
------------------------------------------------------------------------------------------------------------------------------------------------------
ENABLED
SYS@VCRMDV> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
PL/SQL procedure successfully completed.
> set serveroutput on
> l
1* SELECT DBMS_RESULT_CACHE.status FROM dual
> EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 5516768 bytes [1.028% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 5511416 bytes [1.027% of the Shared Pool]
....... Overhead = 137464 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 35064 bytes
....... Cache Memory = 5248K bytes (5248 blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 5248 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 5246 blocks
................... PLSQL = 5246 blocks (5246 count)
PL/SQL procedure successfully completed.
open another session to see if caching works globally.
--in another session
> conn perfstat
Enter password:
Connected.
> set serveroutput on
> exec run_test;
First Loop: 92 hsecs
Second Loop: 83 hsecs
PL/SQL procedure successfully completed.
It works !
loopig for 3000000 times,
exec run_test;
First Loop: 267 hsecs
Second Loop: 276 hsecs
PL/SQL procedure successfully completed.
SQL> SELECT * FROM V$RESULT_CACHE_STATISTICS;
ID
----------
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1
Block Size (Bytes)
1024
2
Block Count Maximum
3936
3
Block Count Current
32
4
Result Size Maximum (Blocks)
196
5
Create Count Success
1
6
Create Count Failure
0
7
Find Count
6000001
8
Invalidation Count
0
9
Delete Count Invalid
0
10
Delete Count Valid
0
11
Hash Chain Length
1
12
Find Copy Count
6000001
13
Latch (Share)
0
13 rows selected.
I also captured AWR snapshot before and after executing test .
From AWR report, I don't see millions of executions of such query.
This helped me to solve a production issue due to such query selecting from DUAL table, which DUAL table is a dictionary table causing latch contention (row cache objects) in extreme high concurrency environment.
Nice feature in 11gR2 comes default - no caching related parameter to adjust in my case.
