Monday, September 11, 2017

Result Cache: RC Latch Contention and RESULT_CACHE_MAX_SIZE (II)

Result Cache memory is limited by RESULT_CACHE_MAX_SIZE. Its default value is 1% of SHARED_POOL_SIZE. When allocated space reaches RESULT_CACHE_MAX_SIZE, any new request can cause contention of "Result Cache: RC Latch". RC Latch "ksl_get_shared_latch" Gets jumps from an order of magnitude Microseconds (us) to Millisecond (ms). The single RC Latch could eventually serialize the system ("Result Cache: Serialization12").

Each RC row takes at least one RC BLOCK (1K). In case of caching SQL Collection, it can occupy more than 1 BLOCK.

In the previous Blog, we discussed the RC Latch Contention caused by RC invalidation triggered by DML statements (Blog: PL/SQL Function Result Cache Invalidation)

Note: all tests are done in Oracle 11.2.0.4, 12.1.0.2, 12.2.0.1 on AIX, Solaris, Linux.

Update (2017-Sep-18): Oracle MOS: SQL Query Result Cache. Includes: [Video] (Doc ID 1108133.1)
RESULT_CACHE_MAX_SIZE defines the memory allocated to the result cache. When set to 0, the Result cache is disabled. The Default value is either 0.25% of MEMORY_TARGET or 0.5% of SGA_TARGET or 1% of SHARED_POOL_SIZE and is chosen by the database based on total memory available to the SGA and on the memory management method currently in use.


1. Test_1: Under Memory Limit


Test DB runs on a UNIX machine with 6 physical processors, and configured with:
 
  shared_pool_size         1408M
  
  result_cache_max_size    14432K
  result_cache_max_result  5
  result_cache_mode        MANUAL
When not specially set, result_cache_max_size is derived from shared_pool_size, which is about 1% of shared_pool_size in default.

Each RC row requires at least one RC BLOCK (1K), and with result_cache_max_size = 14432K, we can have maximum about 14432 RC Rows (ignoring some overhead).

Run test with 4 parallel sessions to create 13000 RC Rows (see appended Test Code):
 
exec clean_jobs;
exec dbms_result_cache.flush;
exec dbms_result_cache.memory_report(TRUE);
exec rc_fun_jobs(4, 13000);
Run query below, we can hardly observe RC Latch Sleeps increasing:
 
select latch#, name, gets, misses, spin_gets, sleeps, wait_time 
from v$latch where name = 'Result Cache: RC Latch'; 
-- latch# in (436, 559, 665) for Oracle 11.2.0.4/12.1.0.2, 12.2.0.1
Latch Misses is comprised of Spin_gets and Sleeps (see Blog: Is latch misses statistic gathered or deduced ? ), and the long wait is exposed by Sleeps.

In AWR report - "Top 10 Foreground Events by Total Wait Time" Section, it is presented as column "Wait Avg(ms)". In case of RC Latch, we can see it in the line "latch free". AWR "Latch Sleep Breakdown" Section contains more details.

Crosscheck RC memory usage by:
 
SQL > exec dbms_result_cache.memory_report(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  = 14432K bytes (14432 blocks)
Maximum Result Size = 721K bytes (721 blocks)
[Memory]
Total Memory = 13540016 bytes [0.917% of the Shared Pool]

... Fixed Memory = 46272 bytes [0.003% of the Shared Pool]
....... Memory Mgr = 208 bytes
....... Cache Mgr  = 256 bytes
....... Bloom Fltr = 2K bytes

....... State Objs = 43760 bytes
... Dynamic Memory = 13493744 bytes [0.914% of the Shared Pool]
....... Overhead = 157168 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 157168 bytes
....... Cache Memory = 13024K bytes (13024 blocks)
........... Unused Memory = 23 blocks
........... Used Memory = 13001 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 13000 blocks
................... PLSQL   = 13000 blocks (13000 count)


SQL > select id, name, value from v$result_cache_statistics order by name;

   ID NAME                           VALUE
----- ------------------------------ ----------
    3 Block Count Current            13024
    2 Block Count Maximum            14432
    1 Block Size (Bytes)             1024
    6 Create Count Failure           0
    5 Create Count Success           13321
    9 Delete Count Invalid           321
   10 Delete Count Valid             0
   12 Find Copy Count                34959999
    7 Find Count                     34370958
   11 Hash Chain Length              1-5
    8 Invalidation Count             0
   13 Latch (Share)                  0
    4 Result Size Maximum (Blocks)   721


SQL > select name, bytes from v$sgastat where name in ('Result Cache');

NAME               BYTES
------------- ----------
Result Cache    13493744


SQL > column name     format a50
SQL > column cache_id format a30
SQL > select name, cache_id, type, status, namespace, sum(block_count) blocks, sum(ceil(row_count*row_size_avg/1024)) est_blocks
      from v$result_cache_objects
      group by name, cache_id, type, status, namespace;

NAME                                               CACHE_ID                       TYPE       STATUS    NAMESPACE      BLOCKS EST_BLOCKS
-------------------------------------------------- ------------------------------ ---------- --------- ---------- ---------- ----------
K.RC_FUN                                           K.RC_FUN                       Dependency Published                     1          0
"K"."RC_FUN"::8."RC_FUN"#8cd0e193d8974f19 #1       cyma7sjgpkr5zbtbp804tt27r7     Result     Published PLSQL           13000      13000
RC memory is under its maximum Limit.

"Block Count Current: 13024" < "Block Count Maximum: 14432", and "Delete Count Invalid: 0".

Measuring latch Get time by dtrace:
 
sudo dtrace -n \
'BEGIN {self->start_wts = walltimestamp; self->start_ts = timestamp;}
pid$target::qesrcCM_Acquire:entry /execname == "oracle"/ { self->rc = 1; }
pid$target::ksl_get_shared_latch:entry /execname == "oracle" && self->rc == 1/ { self->ts = timestamp; }
pid$target::ksl_get_shared_latch:return /self->ts > 0/ {
        @lquant["ns"] = lquantize(timestamp - self->ts, 0, 10000, 1000);
        @avgs["AVG_ns"] = avg(timestamp - self->ts);
        @mins["MIN_ns"] = min(timestamp - self->ts);
        @maxs["MAX_ns"] = max(timestamp - self->ts);
        @sums["SUM_ms"] = sum((timestamp - self->ts)/1000000);
        @counts[ustack(10, 0)] = count();
        self->rc = 0; self->ts = 0;}
END { printf("Start: %Y, End: %Y, Elapsed_ms: %d\n", self->start_wts, walltimestamp, (timestamp - self->start_ts)/1000000);}
' -p 13945


:BEGIN dtrace started: 2017 Sep 11 07:11:57
:END dtrace ended: 2017 Sep 11 07:11:59 ,Elapsed_ms: 1978
  ns
           value  ------------- Distribution ------------- count
             < 0 |                                         0
               0 |@@@@@@@@@@@@@@                           102706
            1000 |@@@@@@@@@@@@@@@@@@@@@@@@@@               186749
            2000 |                                         2193
            3000 |                                         54
            4000 |                                         49
            5000 |                                         42
            6000 |                                         28
            7000 |                                         16
            8000 |                                         9
            9000 |                                         10
        >= 10000 |                                         107

  AVG_ns     1167
  MIN_ns      827
  MAX_ns   169083
  SUM_ms        0

           oracle`ksl_get_shared_latch+0x1b8
           oracle`qesrcCM_Acquire+0x354
           oracle`psdfrcLookup+0x12d
           oracle`pevm_ENTERX+0x143
           oracle`pfrinstr_ENTERX+0x47
           oracle`pfrrun_no_tool+0x12a
           oracle`pfrrun+0x4c0
           oracle`plsql_run+0x288
           oracle`peicnt+0x946
           oracle`kkxexe+0x2f3
  291963
In average, each Get takes 1,167 Nanoseconds:


2. Test_2: Over Memory Limit


Stop above tests, re-run the test to create 15000 RC Rows, make the same checks:
 
exec clean_jobs;
exec dbms_result_cache.flush;
exec dbms_result_cache.memory_report(TRUE);
exec rc_fun_jobs(4, 15000);

SQL > exec dbms_result_cache.memory_report(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  = 14432K bytes (14432 blocks)
Maximum Result Size = 721K bytes (721 blocks)
[Memory]
Total Memory = 14984624 bytes [1.015% of the Shared Pool]

... Fixed Memory = 46272 bytes [0.003% of the Shared Pool]
....... Memory Mgr = 208 bytes
....... Cache Mgr  = 256 bytes
....... Bloom Fltr = 2K bytes

....... State Objs = 43760 bytes
... Dynamic Memory = 14938352 bytes [1.012% of the Shared Pool]
....... Overhead = 159984 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 159984 bytes
....... Cache Memory = 14432K bytes (14432 blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 14432 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 14431 blocks
................... PLSQL   = 14431 blocks (14431 count)


SQL > select id, name, value from v$result_cache_statistics order by name;

   ID NAME                           VALUE
----- ------------------------------ ----------
    3 Block Count Current            14432
    2 Block Count Maximum            14432
    1 Block Size (Bytes)             1024
    6 Create Count Failure           0
    5 Create Count Success           143128
    9 Delete Count Invalid           169
   10 Delete Count Valid             128528
   12 Find Copy Count                80928
    7 Find Count                     86758
   11 Hash Chain Length              1-5
    8 Invalidation Count             0
   13 Latch (Share)                  0
    4 Result Size Maximum (Blocks)   721


SQL > select name, bytes from v$sgastat where name in ('Result Cache');

NAME                   BYTES
----------------- ----------
Result Cache        14938352


SQL > select name, cache_id, type, status, namespace, sum(block_count) blocks, sum(ceil(row_count*row_size_avg/1024)) est_blocks
      from v$result_cache_objects
      group by name, cache_id, type, status, namespace;

NAME                                               CACHE_ID                       TYPE       STATUS    NAMESPACE      BLOCKS EST_BLOCKS
-------------------------------------------------- ------------------------------ ---------- --------- ---------- ---------- ----------
"K"."RC_FUN"::8."RC_FUN"#8cd0e193d8974f19 #1       cyma7sjgpkr5zbtbp804tt27r7     Result     New       PLSQL               1          0
K.RC_FUN                                           K.RC_FUN                       Dependency Published                     1          0
"K"."RC_FUN"::8."RC_FUN"#8cd0e193d8974f19 #1       cyma7sjgpkr5zbtbp804tt27r7     Result     Published PLSQL           14430      14430
dbms_result_cache.memory_report shows RC Memory reaches result_cache_max_size:
 
 Total Memory = 14984624 bytes [1.015% of the Shared Pool]
"Block Count Current: 14432" = "Block Count Maximum: 14432", and "Delete Count Invalid: 128528".

Output of query on v$result_cache_objects occasionally contains one line with Status "NEW" when repeatedly running.

Perform the latch Sleeps check:
 
select latch#, name, gets, misses, spin_gets, sleeps, wait_time 
from v$latch where name = 'Result Cache: RC Latch';

 LATCH# NAME                         GETS     MISSES  SPIN_GETS     SLEEPS  WAIT_TIME
------- ---------------------- ---------- ---------- ---------- ---------- ----------
    559 Result Cache: RC Latch  696395773 3217974991 2909396116 1023307692 7.9453E+11
It shows that Sleeps goes up steadily when each time run the query.

Again measuring latch Get time by dtrace:
 
Start: 2017 Sep 11 07:40:44, End: 2017 Sep 11 07:40:46, Elapsed_ms: 2333
  ns
           value  ------------- Distribution ------------- count
               0 |                                         0
            1000 |@                                        15
            2000 |                                         1
            3000 |                                         0
            4000 |                                         0
            5000 |                                         0
            6000 |                                         0
            7000 |                                         0
            8000 |                                         0
            9000 |                                         0
        >= 10000 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  747

  AVG_ns     3029147
  MIN_ns        1031
  MAX_ns    87959260
  SUM_ms        1951

            oracle`ksl_get_shared_latch+0x1b8
            oracle`qesrcCM_Acquire+0x354
            oracle`psdfrcLookup+0x12d
            oracle`pevm_ENTERX+0x143
            oracle`pfrinstr_ENTERX+0x47
            oracle`pfrrun_no_tool+0x12a
            oracle`pfrrun+0x4c0
            oracle`plsql_run+0x288
            oracle`peicnt+0x946
            oracle`kkxexe+0x2f3
  763
In average, each get takes 3,029,147 Nanoseconds (3 Microseconds).

RC Latch "ksl_get_shared_latch" Gets jumps from an order of magnitude Microseconds (us) to Millisecond (ms).

In real applications, we can also build a histogram on RC creation_timestamp to track RC usages. (see Blog: Latch free waits, Dynamic Statistics, and the Result Cache and MOS Doc ID 2002089.1: High "Latch Free" Waits with Contention on 'Result Cache: RC Latch' when RESULT_CACHE_MODE = MANUAL on Oracle 12c)
 
select trunc(creation_timestamp, 'MI') rc_create_ts
      ,name, cache_id, type, status, namespace
      ,count(*) rc_count, round(avg(scan_count)) avg_scan_cnt, sum(block_count) blocks, sum(ceil(row_count*row_size_avg/1024)) est_blocks 
from   v$result_cache_objects t
where  lru_number > 0
group by name, cache_id, type, status, namespace, trunc(creation_timestamp, 'MI')
order by 1, rc_count desc, avg_scan_cnt desc; 
Blog: PL/SQL Function Result Cache Invalidation contains 5 views in Section: Causal Analysis of Result Cache, and provides some code for Result Cache History Recording.


3. RC Latch Contention and CPU Usage


There exists one common understanding that Latch Contention is manifested in UNIX as high CPU Usage. In both above tests, we have a machine with 6 physical processors, and started 4 parallel sessions, so there is no CPU saturation.

When creating 13000 RC Rows, we observe our 4 processes at the TOP of CPU usage:
 
load averages:  3.98,  2.85,  2.09;                    up 148+17:47:10 07:55:59
73 processes: 68 sleeping, 5 on cpu
CPU states: 32.9% idle, 66.8% user,  0.3% kernel,  0.0% iowait,  0.0% swap
Memory: 24G phys mem, 9991M free mem, 30G total swap, 30G free swap

   PID USERNAME LWP PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
 16030 oracle     1   3    0 4712M 4632M cpu/5    4:50 16.75% oracle
 16032 oracle     1   1    0 4712M 4632M cpu/4    4:50 16.69% oracle
 16026 oracle     1   1    0 4712M 4632M cpu/2    4:50 16.45% oracle
 16028 oracle     1   1    0 4712M 4632M cpu/1    4:50 16.39% oracle
When creating 15000 RC Rows:
 
load averages:  1.16,  1.69,  1.84;                    up 148+17:54:36 08:03:25
73 processes: 71 sleeping, 2 on cpu
CPU states: 81.7% idle, 17.5% user,  0.8% kernel,  0.0% iowait,  0.0% swap
Memory: 24G phys mem, 9991M free mem, 30G total swap, 30G free swap

   PID USERNAME LWP PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
 16134 oracle     1   1    0 4712M 4632M cpu/2    1:35  6.52% oracle
 16132 oracle     1   1    0 4712M 4637M sleep    1:31  4.76% oracle
 16138 oracle     1   1    0 4712M 4632M sleep    1:39  3.42% oracle
 16136 oracle     1   1    0 4712M 4632M sleep    1:36  3.23% oracle
The differences are:
 
Creating 13000 RC Rows has:
  load averages:  3.98
  5 on cpu
  66.8% user
  
Creating 15000 RC Rows has:
  load averages:  1.16
  2 on cpu
  17.5% user
Probably Latch Spins is burning CPU; whereas Latch Sleeps yields CPU. Therefore when talking about Latch contentions, it would be necessary to distinguish between Spin_gets and Sleeps. As tested, usually Spin_gets of Latch Misses are caused by frequently concurrent access; whereas Sleeps of Latch Misses are triggered by Invalidations or DML Modifications.

On an AIX machine with 6 cores and SMT=4 (see Blog: IBM AIX POWER7 CPU Usage and Throughput), run test:
 
  exec rc_fun_jobs(4, 15000);
and list call stacks of all 4 processes for 4 running Oracle sessions (common code removed):
 
$ > procstack 52166700 57278636 58982430 62521360

52166700: ora_j004_aix3
  thread_wait(0xbb800000bb8) + 0x22c
  sskgpwwait(??, ??, ??, ??, ??) + 0x54
  skgpwwait(??, ??, ??, ??, ??) + 0x12c
  ksliwat(??, ??, ??) + 0x1f08
  kslwaitctx(??, ??) + 0xe0
  ksqcmi(??, ??, ??, ??, ??, ??, ??, ??) + 0x6b4c
  ksqgtlctx(??, ??, ??, ??, ??, ??, ??, ??) + 0x10bc
  ksqgelctx(??, ??, ??, ??, ??, ??, ??, ??) + 0x21c
  qesrcCM_Wait(??, ??, ??) + 0x3c4
  qesrcCM_Acquire(??, ??, ??, ??, ??, ??) + 0x474
  psdfrcLookup(??, ??, ??, ??, ??, ??) + 0x124
  pevm_ENTERX(??, ??, ??, ??) + 0x150
  pfrinstr_ENTERX(0xfffffffffff44a0, 0x7000100e5e4e618, 0x122053f58) + 0x60
  pfrrun_no_tool(??, ??, ??) + 0x7c

57278636: ora_j003_aix3
  sskgpwpost(??, ??, ??, ??) + 0x44
  sskgpwwait(??, ??, ??, ??, ??) + 0x54
  skgpwwait(??, ??, ??, ??, ??) + 0x12c
  kslgess(0x0, 0x1600000010, 0x200000002, 0x1, 0x14fe000014fe) + 0x9c8
  ksl_get_shared_latch(??, ??, ??, ??, ??, ??) + 0x460
  qesrcCM_Release(??, ??, ??) + 0x18c
  psdfrcWriteResult(??, ??, ??, ??) + 0x360
  pevm_RET(??, ??) + 0x380
  pfrinstr_RET(0xfffffffffff4490, 0x7000100e5e4e658, 0x122053f58) + 0x4c
  pfrrun_no_tool(??, ??, ??) + 0x7c

58982430: ora_j001_aix3
  thread_wait(0x10e0000012c) + 0x22c
  sskgpwwait(??, ??, ??, ??, ??) + 0x54
  skgpwwait(??, ??, ??, ??, ??) + 0x12c
  kslgess(0x8, 0x8e00000008, 0x200000002, 0x1, 0x14ee000014ee) + 0x9c8
  ksl_get_shared_latch(??, ??, ??, ??, ??, ??) + 0x460
  qesrcPin_Get(??, ??, ??, ??, ??, ??, ??, ??) + 0x218
  qesrcCM_Acquire(??, ??, ??, ??, ??, ??) + 0x368
  psdfrcLookup(??, ??, ??, ??, ??, ??) + 0x124
  pevm_ENTERX(??, ??, ??, ??) + 0x150
  pfrinstr_ENTERX(0xfffffffffff4490, 0x7000100e5e4e618, 0x122053f58) + 0x60

62521360: ora_j000_aix3
  qesrcCM_MakeRoom(??, ??, ??) + 0xec
  qesrcMM_Alloc(??, ??, ??) + 0x16c
  qesrcRO_New(??, ??, ??, ??) + 0xa9c
  qesrcPin_Get(??, ??, ??, ??, ??, ??, ??, ??) + 0x6f4
  qesrcCM_Acquire(??, ??, ??, ??, ??, ??) + 0x368
  psdfrcLookup(??, ??, ??, ??, ??, ??) + 0x124
  pevm_ENTERX(??, ??, ??, ??) + 0x150
  pfrinstr_ENTERX(0xfffffffffff4590, 0x7000100e5e4e618, 0x122053f58) + 0x60
  pfrrun_no_tool(??, ??, ??) + 0x7c
  pfrrun(??) + 0x136c
  plsql_run(??, ??, ??) + 0x310
  peicnt(??, ??) + 0x284
  kkxexe(??) + 0x2b4
  opiexe(??, ??, ??) + 0x4594
  opiodr(??, ??, ??, ??) + 0x3d4
  rpidrus(??) + 0x21c
  skgmstack(??, ??, ??, ??, ??) + 0xc0
  rpidru(??) + 0xa8
  rpiswu2(??, ??, ??, ??, ??, ??, ??, ??) + 0x5ac
  rpidrv(??, ??, ??, ??) + 0xe14
  rpiexe(??) + 0x80
  kkjex1e(??) + 0x1824
  kkjex1e_cdb(??, ??, ??, ??, ??) + 0x1d8
  kkjsexe() + 0x4f4
  kkjrdp() + 0x384
  opirip(??, ??, ??) + 0x408
  opidrv(??, ??, ??) + 0x484
  sou2o(??, ??, ??, ??) + 0x110
  opimai_real(??, ??) + 0x144
  ssthrdmain(0x0, 0x300000003, 0xfffffffffffebf8) + 0x1e4
  main(??, ??) + 0xf0
  __start() + 0x70
We can see that 4 processes are executing respectively:
 
  thread_wait(0xbb800000bb8)
  sskgpwpost(??, ??, ??, ??)
  thread_wait(0x10e0000012c)
  qesrcCM_MakeRoom(??, ??, ??) 
Two processes are waiting with thread_wait. AIX Document said:
  int thread_wait(timeout):  Specifies the maximum length of time, in milliseconds, to wait for a posting.
and two timeout values are:
 
  0x12c =  300
  0xbb8 = 3000
One speculation is that the first timeout is 300, that is, thread_wait(300). In case of later Sleep (not got RC Latch after first 300ms sleeping), timeout is multiplied by 10 (3000).

The thread_post subroutine posts the thread the occurrence of an event, hence waking up the sleeping threads.

The last process (Oracle session) is creating a new RC Row after holding Latch (qesrcPin_Get):
 
  qesrcCM_MakeRoom(??, ??, ??) 
  qesrcMM_Alloc(??, ??, ??)
  qesrcRO_New(??, ??, ??, ??) 
  qesrcPin_Get(??, ??, ??, ??, ??, ??, ??, ??)
  qesrcCM_Acquire(??, ??, ??, ??, ??, ??)


4. Big Rows


In above tests, each RC row has a size less than 1024 Bytes. When caching SQL Collections, we can make the row size more than 1K. Here two similar tests (see appended Collection Test Code) with each row taking about 2K (ROW_SIZE_AVG = 1099 Bytes, i.e 2 RC Blocks). one is under result_cache_max_size, another is beyond that.
 
exec clean_jobs;
exec dbms_result_cache.flush;
exec dbms_result_cache.memory_report(TRUE);
exec rc_tab_jobs(4, 6500, 100);

exec clean_jobs;
exec dbms_result_cache.flush;
exec dbms_result_cache.memory_report(TRUE);
exec rc_tab_jobs(4, 7500, 100);


5. Possible Solutions


Adapt (increase) result_cache_max_size to satisfy cache requests or reduce cache usage. It can be dynamically increased by:
 
alter system set result_cache_max_size = 30M;
We can also disable Result Cache by either dynamically setting result_cache_max_size = 0, or bypass RC:
 
alter system set result_cache_max_size = 0;

exec dbms_result_cache.bypass(true);
exec dbms_result_cache.bypass(false);
The difference is that "result_cache_max_size = 0" releases RC memory, but bypass instructs code bypassing RC, but still keeps the RC memory.

Oracle 12.2 introduced RC Black List, and dbms_result_cache is enhanced with 4 APIs:
 
  exec dbms_result_cache.Black_List_Add('cyma7sjgpkr5zbtbp804tt27r7', TRUE);     -- Add cache_id to black-list
  exec dbms_result_cache.Black_List_Remove('cyma7sjgpkr5zbtbp804tt27r7', TRUE);  -- Remove cache_id from black-list
  exec dbms_result_cache.Black_List_clear(TRUE);                                 -- Remove all cache_id's from black-list
  select * from table(dbms_result_cache.Black_List);                             -- List all contents of the black-list
They allow us to regulate RC usage in granularity of each single cache_id, instead of toggling whole RC Cache.


6. Test Code


 
create or replace function rc_fun(p_var_1 varchar2, p_var_2 varchar2) return pls_integer result_cache as
  l_ret pls_integer;
begin
  l_ret := length(p_var_1) + 1e3*length(p_var_2);
  return l_ret;
end;
/

create or replace procedure rc_proc(p_rc_limit number) as
  l_ret pls_integer;
begin
  for i in 1..p_rc_limit loop
    l_ret := rc_fun('p1_'||i, 'p2_'||(i + p_rc_limit));
  end loop;
end;
/

create or replace procedure rc_fun_jobs(p_job_cnt number, p_rc_limit number) as
  l_job_id pls_integer;
begin
  for i in 1.. p_job_cnt loop
    dbms_job.submit(l_job_id, 'begin while true loop rc_proc('||p_rc_limit||'); end loop; end;');
  end loop;
  commit;
end;    
/

create or replace procedure clean_jobs as
begin
  for c in (select job from dba_jobs) loop
    begin
       dbms_job.remove (c.job);
    exception when others then null;
    end;
    commit;
  end loop;

  for c in (select d.job, d.sid, (select serial# from v$session where sid = d.sid) ser 
              from dba_jobs_running d) loop
    begin
      execute immediate
             'alter system kill session '''|| c.sid|| ',' || c.ser|| ''' immediate';
      dbms_job.remove (c.job);
    exception when others then null;
    end;
    commit;
  end loop;
  
  -- select * from dba_jobs;
  -- select * from dba_jobs_running;
end;
/


7. Collection Test Code


 
create or replace type num_tab force is table of number(10)
/

create or replace function rc_fun_tab(p_id number, p_tab_cnt number) return num_tab result_cache as
  l_ret  num_tab := new num_tab();
begin
  for i in 1..p_tab_cnt loop
    l_ret.extend;
    l_ret(i) := p_id + i;
  end loop;
  return l_ret;
end;
/

create or replace procedure rc_proc_tab(p_rc_limit number, p_tab_cnt number) as
  l_ret num_tab := new num_tab();
begin
  for i in 1..p_rc_limit loop
    l_ret := rc_fun_tab(i, p_tab_cnt);
  end loop;
end;
/

create or replace procedure rc_tab_jobs(p_job_cnt number, p_rc_limit number, p_tab_cnt number) as
  l_job_id pls_integer;
begin
  for i in 1.. p_job_cnt loop
    dbms_job.submit(l_job_id, 'begin while true loop rc_proc_tab('||p_rc_limit||', '||p_tab_cnt||'); end loop; end;');
  end loop;
  commit;
end;    
/