[20180819]关于父子游标问题(11g).txt
–//sql语句存在父子游标,子游标堆6在父游标堆0里面.
–//如果存在许多子游标的情况下,父游标堆0是否大小是发生变化呢.测试看看.
–//另外11g引入参数_cursor_obsolete_threshold限制子光标的数量,测试它的一些控制机制.
1.环境:
–//session 1:
scott@book> @ ver1
port_string version banner
—————————— ————– ——————————————————————————–
x86_64/linux 2.4.xx 11.2.0.4.0 oracle database 11g enterprise edition release 11.2.0.4.0 – 64bit production
scott@book> select count(*) from dept where deptno=10;
count(*)
———-
1
–//确定sql_id=2xw4k6w7wc5ka.
–//session 2:
sys@book> @ &r/hide _cursor_obsolete_threshold
name description default_value session_value system_value
————————– ———————————————- ————- ————- ————
_cursor_obsolete_threshold number of cursors per parent before obsoletion true 1024 1024
–//退出session 1,刷新共享池,清除该语句在共享池.这样才能清除干净.
sys@book> alter system flush shared_pool;
system altered.
2.建立测试脚本:
$ cat aa.sql
declare
l_count pls_integer;
begin
for i in 1..&&2
loop
execute immediate ‘alter session set optimizer_index_caching = ‘||i;
for j in 1..&&1
loop
execute immediate ‘alter session set optimizer_index_cost_adj = ‘||j;
execute immediate ‘select count(*) from dept where deptno=10’ into l_count;
end loop;
end loop;
end;
/
–//执行如上脚本,能产生许多子光标.主要是因为环境变量发生了变化.
–//session 1:
scott@book> @ aa.sql 1 64
pl/sql procedure successfully completed.
3.查看父子游标情况:
–//session 2:
sys@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
text kglhdadr kglhdpar c40 kglhdlmd kglhdpmd kglobhd0 kglobhd6 kglobhs0 kglobhs6 kglobt16 n0_6_16 n20 kglnahsh kglobt03 kglobt09
————– —————- —————- —————————————- ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
父游标句柄地址 000000007cbc2c60 000000007cbc2c60 select count(*) from dept where deptno=1 1 0 000000007cbc2ba8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
–//查看父游标堆0的chunk:
select a.* from x$ksmsp a where a.ksmchpar=hextoraw(‘000000007cbc2ba8’)
addr indx inst_id ksmchidx ksmchdur ksmchcom ksmchptr ksmchsiz ksmchcls ksmchtyp ksmchpar
—————- ———- ———- ———- ———- —————- —————- ———- ——– ———- —————-
00007f8f6af85568 1634 1 1 1 kglh0^fc6164a 000000007db3c420 4096 freeabl 0 000000007cbc2ba8
00007f8f6af5a1f8 2515 1 1 1 kglh0^fc6164a 000000007d879970 4096 freeabl 0 000000007cbc2ba8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
00007f8f6af5ea40 2676 1 1 1 kglh0^fc6164a 000000007d7c2f20 4096 freeabl 0 000000007cbc2ba8
00007f8f6af55250 3713 1 1 1 kglh0^fc6164a 000000007d342488 4096 freeabl 0 000000007cbc2ba8
00007f8f6af450f0 4407 1 1 1 kglh0^fc6164a 000000007ce8c5f8 4096 freeabl 0 000000007cbc2ba8
00007f8f6af47ac0 4471 1 1 1 kglh0^fc6164a 000000007ce6d850 4096 freeabl 0 000000007cbc2ba8
00007f8f6af30440 4856 1 1 1 kglh0^fc6164a 000000007cc973c8 4096 freeabl 0 000000007cbc2ba8
00007f8f6af33e80 4965 1 1 1 kglh0^fc6164a 000000007cbd41f0 4096 freeabl 0 000000007cbc2ba8
00007f8f6b0a1400 5593 1 1 1 kglh0^fc6164a 000000007c7f4f60 4096 freeabl 0 000000007cbc2ba8
00007f8f6b08cd28 6025 1 1 1 kglh0^fc6164a 000000007c55fc10 4096 freeabl 0 000000007cbc2ba8
00007f8f6b0907b0 6226 1 1 1 kglh0^fc6164a 000000007c442f48 4096 freeabl 0 000000007cbc2ba8
00007f8f6b07a300 6652 1 1 1 kglh0^fc6164a 000000007c137798 4096 freeabl 0 000000007cbc2ba8
00007f8f6b070cd8 7591 1 1 1 kglh0^fc6164a 000000007bc7d898 4096 freeabl 0 000000007cbc2ba8
00007f8f6b072158 7717 1 1 1 kglh0^fc6164a 000000007bb93bc0 4096 freeabl 0 000000007cbc2ba8
00007f8f6b074238 7807 1 1 1 kglh0^fc6164a 000000007bb49798 4096 freeabl 0 000000007cbc2ba8
00007f8f6b0754b8 7846 1 1 1 kglh0^fc6164a 000000007bb19348 4096 freeabl 0 000000007cbc2ba8
00007f8f6b058c38 8653 1 1 1 kglh0^fc6164a 000000007b62c700 4096 freeabl 0 000000007cbc2ba8
00007f8f6b05afb8 8778 1 1 1 kglh0^fc6164a 000000007b5a06e8 4096 freeabl 0 000000007cbc2ba8
00007f8f6b05c6b0 8804 1 1 1 kglh0^fc6164a 000000007b588c38 4096 freeabl 0 000000007cbc2ba8
00007f8f6b05dda8 8830 1 1 1 kglh0^fc6164a 000000007b55fd78 4096 recr 4095 000000007cbc2ba8
00007f8f6b02a960 8930 1 1 1 kglh0^fc6164a 000000007b4d8640 4096 freeabl 0 000000007cbc2ba8
00007f8f6b02a490 8944 1 1 1 kglh0^fc6164a 000000007b4aaff0 4096 freeabl 0 000000007cbc2ba8
22 rows selected.
–//可以发现如果产生子光标很多,父游标堆0的chunk也会很多,不像1个子光标的情况下仅仅1个chunk.
–//查看父游标堆0的描述符chunk:
select * from x$ksmsp where to_number (‘000000007cbc2ba8’, ‘xxxxxxxxxxxxxxxx’) between to_number(ksmchptr, ‘xxxxxxxxxxxxxxxx’) and to_number(ksmchptr, ‘xxxxxxxxxxxxxxxx’)+ksmchsiz
addr indx inst_id ksmchidx ksmchdur ksmchcom ksmchptr ksmchsiz ksmchcls ksmchtyp ksmchpar
—————- ———- ———- ———- ———- —————- —————- ———- ——– ———- —————-
00007f8f6af337f8 4997 1 1 1 kglda 000000007cbc2b40 240 freeabl 0 00
sys@book> @ &r/sharepool/shp4 2xw4k6w7wc5ka 0
text kglhdadr kglhdpar c40 kglhdlmd kglhdpmd kglhdivc kglobhd0 kglobhd6 kglobhs0 kglobhs6 kglobt16 n0_6_16 n20 kglnahsh kglobt03 kglobt09
————– —————- —————- —————————————- ———- ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
子游标句柄地址 000000007cbc27e0 000000007cbc2c60 select count(*) from dept where deptno=1 0 0 0 000000007cbc2728 000000007b5604e8 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 0
子游标句柄地址 000000007c6c4a90 000000007cbc2c60 select count(*) from dept where deptno=1 0 0 0 000000007c6c49d8 000000007b560ab0 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 1
子游标句柄地址 000000007da59628 000000007cbc2c60 select count(*) from dept where deptno=1 0 0 0 000000007da59570 000000007b4d89c0 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 2
子游标句柄地址 000000007d66e770 000000007cbc2c60 select count(*) from dept where deptno=1 0 0 0 000000007d66e6b8 000000007b4d8e80 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 3
…//太长
子游标句柄地址 000000007b693320 000000007cbc2c60 select count(*) from dept where deptno=1 1 0 0 000000007b693268 000000007d87a1b0 4488 8088 80634 93210 93210 264640074 2xw4k6w7wc5ka 63
父游标句柄地址 000000007cbc2c60 000000007cbc2c60 select count(*) from dept where deptno=1 1 0 0 000000007cbc2ba8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
65 rows selected.
–//随便看看一个子光标堆6的描述符chunk:(kglobhd6=000000007d87a1b0)
select * from x$ksmsp where to_number (‘000000007d87a1b0’, ‘xxxxxxxxxxxxxxxx’) between to_number(ksmchptr, ‘xxxxxxxxxxxxxxxx’) and to_number(ksmchptr, ‘xxxxxxxxxxxxxxxx’)+ksmchsiz
addr indx inst_id ksmchidx ksmchdur ksmchcom ksmchptr ksmchsiz ksmchcls ksmchtyp ksmchpar
—————- ———- ———- ———- ———- —————- —————- ———- ——– ———- —————-
00007f8f6af6dd40 2621 1 1 1 kglh0^fc6164a 000000007d879970 4096 freeabl 0 000000007cbc2ba8
–//可以发现堆6的描述符chunk与前面的父游标堆0的chunk相同,注意看前面下划线内容.也就是子游标堆6的描述符chunk在父游标堆0的chunk中.
4.继续测试_cursor_obsolete_threshold限制子光标的数量.
–//退出session 1,刷新共享池.
–//session 2:
sys@book> alter system flush shared_pool;
system altered.
scott@book> alter session set “_cursor_obsolete_threshold”=64;
session altered.
–//缺省参数1024,有点大,减少到64,这样好测试一些.
–//session 1:
scott@book> @ aa.sql 1 65
pl/sql procedure successfully completed.
–//session 2
sys@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
text kglhdadr kglhdpar c40 kglhdlmd kglhdpmd kglobhd0 kglobhd6 kglobhs0 kglobhs6 kglobt16 n0_6_16 n20 kglnahsh kglobt03 kglobt09
————– —————- —————- —————————————- ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
父游标句柄地址 000000007bbf0758 000000007bbf0758 select count(*) from dept where deptno=1 1 0 000000007da01ce8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007d0716a0 000000007d0716a0 select count(*) from dept where deptno=1 1 0 000000007b442b50 00 4736 0 0 4736 4736 264640074 2xw4k6w7wc5ka 65535
–//产生2个父游标,注意2条记录的kglhdlmd=1.表示还没有释放游标.
–//注:我开始以为会出现多父多子的情况.实际上并不是,查看v$sql视图就很容易明白.
sys@book> select address,child_number,is_obsolete from v$sql where sql_id=’2xw4k6w7wc5ka’ and is_obsolete=’n’;
address child_number i
—————- ———— –
000000007d0716a0 0 n
sys@book> select address,is_obsolete,count(*) from v$sql where sql_id=’2xw4k6w7wc5ka’ group by address ,is_obsolete;
address i count(*)
—————- – ———-
000000007d0716a0 n 1
000000007bbf0758 y 64
–//可以发现仅仅1个子光标是is_obsolete=’n’.其它is_obsolete=’y’,共有64个子光标,而且地址也不同(这个地址对应父游标的地址).继续测试:
–//父游标地址000000007bbf0758下的子光标都是is_obsolete=’y’.
–//session 1:
scott@book> @ aa.sql 1 65
pl/sql procedure successfully completed.
–//session 2:
sys@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
text kglhdadr kglhdpar c40 kglhdlmd kglhdpmd kglobhd0 kglobhd6 kglobhs0 kglobhs6 kglobt16 n0_6_16 n20 kglnahsh kglobt03 kglobt09
————– —————- —————- —————————————- ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
父游标句柄地址 000000007bbf0758 000000007bbf0758 select count(*) from dept where deptno=1 0 0 000000007da01ce8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007d0716a0 000000007d0716a0 select count(*) from dept where deptno=1 1 0 000000007b442b50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007b59e0f8 000000007b59e0f8 select count(*) from dept where deptno=1 1 0 000000007b59e040 00 4736 0 0 4736 4736 264640074 2xw4k6w7wc5ka 65535
sys@book> select address,is_obsolete,count(*) from v$sql where sql_id=’2xw4k6w7wc5ka’ group by address ,is_obsolete;
address i count(*)
—————- – ———-
000000007d0716a0 y 64
000000007b59e0f8 n 2
000000007bbf0758 y 64
–//产生2个父游标,注意后2条记录的kglhdlmd=1. 而地址000000007b59e0f8对应的is_obsolete=’n’,其它都是is_obsolete=’y’.
–//表示父游标句柄地址=000000007b59e0f8,当前有效(is_obsolete=’n’).继续测试:
–//session 1:
scott@book> @ aa.sql 1 65
pl/sql procedure successfully completed.
–//session 2:
sys@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
text kglhdadr kglhdpar c40 kglhdlmd kglhdpmd kglobhd0 kglobhd6 kglobhs0 kglobhs6 kglobt16 n0_6_16 n20 kglnahsh kglobt03 kglobt09
————– —————- —————- —————————————- ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
父游标句柄地址 000000007bbf0758 000000007bbf0758 select count(*) from dept where deptno=1 0 0 000000007da01ce8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007d0716a0 000000007d0716a0 select count(*) from dept where deptno=1 0 0 000000007b442b50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007b59e0f8 000000007b59e0f8 select count(*) from dept where deptno=1 1 0 000000007b59e040 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007cb65cb0 000000007cb65cb0 select count(*) from dept where deptno=1 1 0 000000007cb65bf8 00 8808 0 0 8808 8808 264640074 2xw4k6w7wc5ka 65535
sys@book> select address,is_obsolete,count(*) from v$sql where sql_id=’2xw4k6w7wc5ka’ group by address ,is_obsolete;
address i count(*)
—————- – ———-
000000007d0716a0 y 64
000000007bbf0758 y 64
000000007b59e0f8 y 64
000000007cb65cb0 n 3
–//大家自己看,不再说明.
–//可以发现1个规律.如果当前父游标下存在64个子光标的情况下,再有子光标产生,该父游标下的子游标无效(is_obsolete=’y’),建立新的父游标.
–//我前面调用的脚本@ aa.sql 1 65,每次都有1个子光标无法容纳,产生1个新的父游标,这样3次,这样新建立的父游标下就存在3个子光标.
–//如果执行如下,就不会建立新的父游标.
–//session 1:
scott@book> @ aa.sql 1 61
pl/sql procedure successfully completed.
–//session 2:
sys@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
old 21: where kglobt03 = ‘&1′ or kglhdpar=’&1′ or kglhdadr=’&1’ or kglnahsh= &2
new 21: where kglobt03 = ‘2xw4k6w7wc5ka’ or kglhdpar=’2xw4k6w7wc5ka’ or kglhdadr=’2xw4k6w7wc5ka’ or kglnahsh= 0
text kglhdadr kglhdpar c40 kglhdlmd kglhdpmd kglobhd0 kglobhd6 kglobhs0 kglobhs6 kglobt16 n0_6_16 n20 kglnahsh kglobt03 kglobt09
————– —————- —————- —————————————- ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
父游标句柄地址 000000007bbf0758 000000007bbf0758 select count(*) from dept where deptno=1 0 0 000000007da01ce8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007d0716a0 000000007d0716a0 select count(*) from dept where deptno=1 0 0 000000007b442b50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007b59e0f8 000000007b59e0f8 select count(*) from dept where deptno=1 0 0 000000007b59e040 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007cb65cb0 000000007cb65cb0 select count(*) from dept where deptno=1 1 0 000000007cb65bf8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
sys@book> select address,is_obsolete,count(*) from v$sql where sql_id=’2xw4k6w7wc5ka’ group by address ,is_obsolete;
address i count(*)
—————- – ———-
000000007d0716a0 y 64
000000007bbf0758 y 64
000000007b59e0f8 y 64
000000007cb65cb0 n 64
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
–//注意看kglhdlmd=1那行,父游标句柄地址=000000007cb65cb0.与下划线看到的地址一致(is_obsolete=’n’).如果我继续执行
–//session 1:
scott@book> @ aa.sql 1 64
pl/sql procedure successfully completed.
–//session 2:
sys@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
text kglhdadr kglhdpar c40 kglhdlmd kglhdpmd kglobhd0 kglobhd6 kglobhs0 kglobhs6 kglobt16 n0_6_16 n20 kglnahsh kglobt03 kglobt09
————– —————- —————- —————————————- ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
父游标句柄地址 000000007bbf0758 000000007bbf0758 select count(*) from dept where deptno=1 0 0 000000007da01ce8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007d0716a0 000000007d0716a0 select count(*) from dept where deptno=1 0 0 000000007b442b50 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007b59e0f8 000000007b59e0f8 select count(*) from dept where deptno=1 0 0 000000007b59e040 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007cb65cb0 000000007cb65cb0 select count(*) from dept where deptno=1 1 0 000000007cb65bf8 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007ce9efb8 000000007ce9efb8 select count(*) from dept where deptno=1 1 0 000000007ce9ef00 00 8808 0 0 8808 8808 264640074 2xw4k6w7wc5ka 65535
sys@book> select address,is_obsolete,count(*) from v$sql where sql_id=’2xw4k6w7wc5ka’ group by address ,is_obsolete;
address i count(*)
—————- – ———-
000000007ce9efb8 n 3
000000007cb65cb0 y 64
000000007d0716a0 y 64
000000007bbf0758 y 64
000000007b59e0f8 y 64
–//奇怪竟然又生产新的父游标,下面有3个子光标.why?
–//中午自己认真看一遍,突然明白为什么存在3个子光标,is_obsolete=’n’.
5.一步一步来解析:
–//执行@ aa.sql 1 65, 一个父游标仅仅有64个子游标,这样最后1个语句,生成新父游标,对应的optimizer_index_caching=65.
–//执行@ aa.sql 1 65, 执行到optimizer_index_caching=64时,该父游标无法再加入子游标,生成新父游标,对应的optimizer_index_caching=64,65.存在2个子光标.
–//执行@ aa.sql 1 65, 执行到optimizer_index_caching=63时,该父游标无法再加入子游标,生成新父游标,对应的optimizer_index_caching=63,64,65.
–//执行@ aa.sql 1 61, 对于父游标正好有64个子游标.不会生成新的父游标.而对应子游标的optimizer_index_caching=63,64,65,1,2,….,61
–//执行@ aa.sql 1 64, optimizer_index_caching从1,2,..,61都能找到对应的子光标.而当执行optimizer_index_caching=62时,全部子游标不合适.
–//而且该父游标下已经存在64个子游标,这样该父游标下全部子游标变成is_obsolete=’y’.生成新的父游标.对应的optimizer_index_caching=62,63,64.存在3个子光标.
–//可以通过一个简单的测试证明自己的判断:
–//退出sessioin 1,刷新共享池.
–//session 2:
sys@book> alter system flush shared_pool;
system altered.
–//session 1,顺序执行如下:
scott@book> alter session set “_cursor_obsolete_threshold”=64;
session altered.
@ aa.sql 1 65
@ aa.sql 1 65
@ aa.sql 1 65
@ aa.sql 1 61
–//建立测试脚本ab.sql:
$ cat ab.sql
declare
l_count pls_integer;
begin
for i in 62..64
loop
execute immediate ‘alter session set optimizer_index_caching = ‘||i;
for j in 1..&&1
loop
execute immediate ‘alter session set optimizer_index_cost_adj = ‘||j;
execute immediate ‘select count(*) from dept where deptno=10’ into l_count;
end loop;
end loop;
end;
/
–//注:仅仅调用执行optimizer_index_caching=62,63,64的情况.
–//session 1
scott@book> @ ab.sql 1
pl/sql procedure successfully completed.
–//session 2:
sys@book> @ &r/sharepool/shp4z 2xw4k6w7wc5ka 0
text kglhdadr kglhdpar c40 kglhdlmd kglhdpmd kglobhd0 kglobhd6 kglobhs0 kglobhs6 kglobt16 n0_6_16 n20 kglnahsh kglobt03 kglobt09
————– —————- —————- —————————————- ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
父游标句柄地址 000000007d3e0d18 000000007d3e0d18 select count(*) from dept where deptno=1 0 0 000000007d3e0c60 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007cd26cf0 000000007cd26cf0 select count(*) from dept where deptno=1 0 0 000000007cd26c38 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007b4808c0 000000007b4808c0 select count(*) from dept where deptno=1 0 0 000000007b480808 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007d14f128 000000007d14f128 select count(*) from dept where deptno=1 1 0 000000007d14f070 00 82104 0 0 82104 82104 264640074 2xw4k6w7wc5ka 65535
父游标句柄地址 000000007b9760c8 000000007b9760c8 select count(*) from dept where deptno=1 1 0 000000007b976010 00 8808 0 0 8808 8808 264640074 2xw4k6w7wc5ka 65535
sys@book> select address,is_obsolete,count(*) from v$sql where sql_id=’2xw4k6w7wc5ka’ group by address ,is_obsolete;
address i count(*)
—————- – ———-
000000007d3e0d18 y 64
000000007cd26cf0 y 64
000000007b9760c8 n 3
000000007d14f128 y 64
000000007b4808c0 y 64
–//还有3个子游标,is_obsolete=’n’.
–//测试有点乱,不过还是能基本说明问题.oracle各个版本_cursor_obsolete_threshold参数一直的不断调整.
–//看来家里的windows系统12.1.0.1:
sys@test> @ ver1
port_string version banner con_id
—————————— ————– ——————————————————————————– ———-
ibmpc/win_nt64-9.1.0 12.1.0.1.0 oracle database 12c enterprise edition release 12.1.0.1.0 – 64bit production 0
sys@test> @ hide _cursor_obsolete_threshold
name description default_value session_value system_value
————————– ———————————————– ————- ————- ————
_cursor_obsolete_threshold number of cursors per parent before obsoletion. false 64 64
–//据说12.2.0.1版本修改为8192.当然重点定位为什么子光标太多,定位问题很关键.
–//我个人感觉1024还是比较合理.
6.附上测试脚本:
–//shp4.sql
column n0_6_16 format 99999999
select decode (kglhdadr,
kglhdpar, ‘父游标句柄地址’,
‘子游标句柄地址’)
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
kglhdlmd,
kglhdpmd,
— kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 n0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 n20,
kglnahsh,
kglobt03 ,
kglobt09
from x$kglob
where kglobt03 = ‘&1′ or kglhdpar=’&1′ or kglhdadr=’&1’ or kglnahsh= &2;
–//shp4z.sql
column n0_6_16 format 99999999
select * from (
select decode (kglhdadr,
kglhdpar, ‘父游标句柄地址’,
‘子游标句柄地址’)
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
kglhdlmd,
kglhdpmd,
— kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 n0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 n20,
kglnahsh,
kglobt03 ,
kglobt09
from x$kglob
where kglobt03 = ‘&1′ or kglhdpar=’&1′ or kglhdadr=’&1’ or kglnahsh= &2
) where kglhdadr=kglhdpar;