amazing server side result cache (1)

We are facing a functioned called about 5 thousands times per second, by hundreds of stored procedure from 20+ app server.

The result is static, so server side result cache come into my picture.

started from  PL/SQL result cache after I learned it is different from sql*plus.  (I am not PL/SQL expert).

a.)  RESULT_CACHE is the key word when defining a funciton.


i.e below is wrong

set serveroutput on
DECLARE
   x NUMBER := 3000000;
   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 /*+ RESULT_CACHE */ COUNT(*) into c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
   END LOOP;

   dbms_output.put_line( dbms_utility.get_time - n1 );
END;
/

b.)  DBMS_RESULT_CACHE.memory_report is immediate tool I like, as long as I see few rows as output -- nothing is cached.

-- simple function for test

CREATE OR REPLACE FUNCTION count_crm_date
  2    RETURN NUMBER
  3    RESULT_CACHE
  4  AS
  5    c number;
  6  BEGIN
  7    SELECT count(*) INTO c FROM C_DATE WHERE EXPIRY_DT > SYSDATE;
  8    --C :=P1;
  9    -- Pause for 1 second.
 10    --DBMS_LOCK.sleep(1);
 11    RETURN c;
 12  END count_crm_date;
 13  /

Function created.


set serveroutput on

> var r number;
> print r;

         R
----------


> 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 = 166400 bytes [0.031% 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 = 161048 bytes [0.030% of the Shared Pool]
....... Overhead = 128280 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 25880 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 3 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)

PL/SQL procedure successfully completed.

> EXEC DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

>  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 = 5352 bytes [0.001% 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 = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

> exec :r :=count_crm_date;

PL/SQL procedure successfully completed.

> 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 = 165152 bytes [0.031% 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 = 159800 bytes [0.030% of the Shared Pool]
....... Overhead = 127032 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 24632 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 28 blocks
........... Used Memory = 4 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 1 blocks
................... PLSQL   = 1 blocks (1 count)

PL/SQL procedure successfully completed.

> print r;

         R
----------
         0