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;
/
