[20211221]提示precompute_subquery补充2.txt
--//前几天测试提示precompute_subquery,发现使用它的缺点:
--//1每次运行都产生1个新的子光标，每次都是一次"硬分析"。
--//2.使用范围很窄，一旦内层使用绑定变量，提示失效。
--//其实每次都是硬解析很好理解,毕竟每次运行可能内层算出的值不同,可以查询fliter或者access条件确定.
--//测试看看这些值在子光标的什么位置,堆0还是堆6.
--//我以前做过测试绑定变量的值在子光标堆0中,而这些值是fliter或者access条件,猜测应该在堆6,也就是执行计划里面.
--//参考链接 [20191213]共享池绑定变量的值在哪里.txt
--//理论讲按照以前的测试,应该在堆6,测试看看。
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
2.测试1:
SCOTT@book> create table deptx as select * from dept;
Table created.
SCOTT@book> select * from dept where dname in (select /*+ precompute_subquery */ dname from deptx where deptno=30);
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
--//sql_id=b72msdh4r55yc,deptno=30,带入dname的值是SALES.
SYS@book> @ sharepool/shp4 b72msdh4r55yc 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007C1F0CC8 000000007E366B80 select * from dept where dname in (selec 1 0 0 000000007E364B78 000000007EA43F88 4528 12144 3135 19807 19807 158504908 b72msdh4r55yc 0
parent handle address 000000007E366B80 000000007E366B80 select * from dept where dname in (selec 1 0 0 000000007D2B2A58 00 4784 0 0 4784 4784 158504908 b72msdh4r55yc 65535
)
SYS@book> oradebug setmypid
Statement processed.
SYS@book> @ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_26919_0001.trc
SYS@book> oradebug dump heapdump_addr 2 0x000000007E364B78
Statement processed.
SYS@book> @ ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_26919_0002.trc
SYS@book> oradebug dump heapdump_addr 2 0x000000007EA43F88
Statement processed.
--//检查发现：
$ grep -i SALES /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_26919_0001.trc
$ grep -i SALES /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_26919_0002.trc
07E1D9C70 0A64A9B0 00000000 454C4153 00000053 [..d.....SALES...]
07E1DAAE0 454C4153 00000053 [SALES...]
07E1DAB00 454C4153 00000053 00000000 00000000 [SALES...........]
--//可以大致确定在堆6.
--//在换一个语句：
SCOTT@book> select deptno,loc from dept where dname in (select /*+ precompute_subquery */ dname from deptx where deptno in (10,30));
DEPTNO LOC
---------- -------------
10 NEW YORK
30 CHICAGO
--//sql_id=6m03r797wymw2.dname = ACCOUNTING,SALES
SYS@book> @ sharepool/shp4 6m03r797wymw2 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007D8440C0 000000007D44A910 select deptno,loc from dept where dname 1 0 0 000000007D44A858 000000007BED0EC8 4528 12144 3152 19824 19824 1338986370 6m03r797wymw2 0
parent handle address 000000007D44A910 000000007D44A910 select deptno,loc from dept where dname 1 0 0 000000007CD07E78 00 4800 0 0 4800 4800 1338986370 6m03r797wymw2 65535
SYS@book> @ ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_26919_0003.trc
SYS@book> oradebug dump heapdump_addr 2 0x000000007BED0EC8
Statement processed.
$ egrep -n -C6 "SALES|ACCO" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_26919_0003.trc | egrep "Chunk|SALES|ACCO"
302- Chunk 07c78fb88 sz= 32 freeable "ub1[]: qkexrXfo"
306:07C78FBA0 454C4153 00000053 [SALES...]
307- Chunk 07c78fba8 sz= 88 freeable "opn: qkexrInitO"
325- Chunk 07c78fc80 sz= 40 freeable "ub1[]: qkexrXfo"
328:07C78FC90 0A64A9B0 00000000 4F434341 49544E55 [..d.....ACCOUNTI]
330- Chunk 07c78fca8 sz= 88 freeable "opn: qkexrInitO"
646- Chunk 07c6f2810 sz= 32 freeable "strdef_buf : kk"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
649:07C6F2820 0A766B5C 00000000 454C4153 00000053 [\kv.....SALES...]
650- Chunk 07c6f2830 sz= 120 freeable "optdef: qcopCre"
660- Chunk 07c6f28a8 sz= 40 freeable "strdef_buf : kk"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
664:07C6F28C0 4F434341 49544E55 0000474E 00000000 [ACCOUNTING......]
665- Chunk 07c6f28d0 sz= 40 freeable "strdef_buf : kk"
668:07C6F28E0 0A766B5C 00000000 454C4153 49544E53 [\kv.....SALESNTI]
670- Chunk 07c6f28f8 sz= 80 freeable "ctxqrol : kkqsr"
--//猜测这两个chunk用来接受过滤值。总之在堆6信息里面。
3.测试2:
--//前面的测试知道变量的位置,既然内层展开后直接带入带入,如果参数很多,相应的堆占用内存空间很大.
SCOTT@book> create table tx as select object_id deptno from all_objects;
Table created.
SCOTT@book> @ gts tx
Gather Table Statistics for table tx...
PL/SQL procedure successfully completed.
SCOTT@book> select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10000);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--//sql_id=1xzm1bn3ru86q.传入10000个参数值,看看堆6占用空间.
SYS@book> @ sharepool/shp4 1xzm1bn3ru86q 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007E1B10B8 000000007E1B1538 select * from dept where deptno in (sel 1 0 0 000000007E1B1000 000000007DED5E20 4488 4115824 3139 4123451 4123451 125640918 1xzm1bn3ru86q 0
parent handle address 000000007E1B1538 000000007E1B1538 select * from dept where deptno in (sel 1 0 0 000000007E1B1480 00 4800 0 0 4800 4800 125640918 1xzm1bn3ru86q 65535
--//注意看KGLOBHS6占用的大小达到了4115824.
SYS@book> select count(*),sum(KSMCHSIZ),avg(KSMCHSIZ) from x$ksmsp a where a.ksmchpar=hextoraw('000000007DED5E20');
COUNT(*) SUM(KSMCHSIZ) AVG(KSMCHSIZ)
---------- ------------- -------------
996 4155688 4172.37751
--//占用996个chunk，平均大小4172. 当然我带入的参数数量达到10000不是1000，如果像平时的1000，至少也消耗4123451/10 = 412345.1,大约410K。
--//可以看出实际上precompute_subquery使用范围很窄，缺点多多。
SYS@book> @ curheaps.sql 125640918 0
KGLNAHSH KGLHDPAR SQL_ID CHILD# KGLHDADR KGLOBHD0 SIZE0 SIZE1 SIZE2 SIZE3 KGLOBHD4 SIZE4 SIZE5 KGLOBHD6 SIZE6 SIZE7 STATUS
---------- ---------------- ------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ----------
125640918 000000007E1B1538 1xzm1bn3ru86q 0 000000007E1B10B8 000000007E1B1000 4488 0 0 0 00 0 0 000000007DED5E20 4115824 0 1
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP0 perm permanent memor 2672 2
HEAP0 free free memory 752 1
HEAP0 freeabl kgltbtab 608 4
no rows selected
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP6 freeabl qeeOpt: qeesCre 1360264 10002
HEAP6 freeabl optdef: qcopCre 1201200 10000
HEAP6 freeabl opn: qkexrInitO 890104 10005
HEAP6 freeabl ub1[]: qkexrXfo 326264 10000
HEAP6 freeabl strdef_buf : kk 323680 10001
HEAP6 freeabl kksol : kksnsg 5056 79
HEAP6 freeabl kctdef : qcdlgo 1224 3
HEAP6 freeabl ctxdef:kksLoadC 936 1
HEAP6 free free memory 776 1
HEAP6 freeabl kccdef: qkxrMem 672 3
HEAP6 freeabl idndef : qcuAll 560 14
HEAP6 freeabl audRegFro:audta 544 4
HEAP6 freeabl opixpop:kctdef 400 1
HEAP6 freeabl kctdef : qcsfps 400 1
HEAP6 freeabl qertbs:qertbIAl 360 1
HEAP6 freeabl chedef : qcuatc 240 6
HEAP6 freeabl pqctx:kkfdParal 232 1
HEAP6 freeabl idndef*[]: qkex 224 4
HEAP6 freeabl kggsmInitCompac 184 5
HEAP6 freeabl ctxqrol : kkqsr 160 2
HEAP6 freeabl qcctx : kkmqccr 152 1
HEAP6 freeabl kggsmCommonInit 144 1
HEAP6 freeabl qeSel: qkxrXfor 144 3
HEAP6 freeabl kksol : kkscuf 128 2
HEAP6 freeabl kafco : qkacol 120 1
HEAP6 freeabl opiprwd : opitc 104 1
HEAP6 freeabl ktamd : ktagmd 96 2
HEAP6 freeabl qkaapd : qkaqkn 96 1
HEAP6 freeabl ctxPlanSig:qksc 88 1
HEAP6 freeabl KGHSC_ALLOC_BUF 88 1
HEAP6 freeabl qcpctx: kkmqccr 80 1
HEAP6 freeabl qertbAllocatePa 80 1
HEAP6 perm permanent memor 80 1
HEAP6 freeabl kggsmInit:sm 80 1
HEAP6 freeabl qcsctx: kkmqccr 72 1
HEAP6 freeabl qksmm: qksmmCs 72 1
HEAP6 freeabl kobjn : kkdcchs 64 2
HEAP6 freeabl unmdef in opipr 64 1
HEAP6 freeabl cxach : opiSem 64 1
HEAP6 freeabl qeeRwo: qeeCrea 56 1
HEAP6 freeabl qcmemctx : kkmq 56 1
HEAP6 freeabl kggac: kggacCre 56 1
HEAP6 freeabl qksrcMarkQB:qks 48 1
HEAP6 freeabl kksoff : opitca 48 1
HEAP6 freeabl qctctx: kkmqccr 48 1
HEAP6 freeabl qkaEnableWide:c 40 1
HEAP6 freeabl qesmaInitTblCtx 40 1
HEAP6 freeabl qcptgc: kkmqccr 40 1
HEAP6 freeabl opixfalo:froaty 32 1
HEAP6 freeabl opixfalo:ctxkct 32 1
HEAP6 freeabl xplGenXpl:planL 32 1
51 rows selected.
--//等上一小会,让子光标堆6空间释放.
SYS@book> @ sharepool/shp4 1xzm1bn3ru86q 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007E1B10B8 000000007E1B1538 select * from dept where deptno in (sel 1 0 0 000000007E1B1000 00 4488 0 3139 7627 7627 125640918 1xzm1bn3ru86q 0
parent handle address 000000007E1B1538 000000007E1B1538 select * from dept where deptno in (sel 1 0 0 000000007E1B1480 00 4800 0 0 4800 4800 125640918 1xzm1bn3ru86q 65535
SYS@book> select count(*),sum(KSMCHSIZ),avg(KSMCHSIZ) from x$ksmsp a where a.ksmchpar=hextoraw('000000007DED5E20');
COUNT(*) SUM(KSMCHSIZ) AVG(KSMCHSIZ)
---------- ------------- -------------
0
--//共享内存已经释放子光标0的堆6占用空间。再次执行：
SCOTT@book> select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10000);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SYS@book> @ sharepool/shp4 1xzm1bn3ru86q 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007E1B10B8 000000007E1B1538 select * from dept where deptno in (sel 1 0 0 000000007E1B1000 000000007DED5E20 4488 4115808 3139 4123435 4123435 125640918 1xzm1bn3ru86q 0
parent handle address 000000007E1B1538 000000007E1B1538 select * from dept where deptno in (sel 1 0 0 000000007E1B1480 00 4800 0 0 4800 4800 125640918 1xzm1bn3ru86q 65535
--//堆6的地址描述符没有变还是000000007DED5E20。
SYS@book> select count(*),sum(KSMCHSIZ),avg(KSMCHSIZ) from x$ksmsp a where a.ksmchpar=hextoraw('000000007DED5E20');
COUNT(*) SUM(KSMCHSIZ) AVG(KSMCHSIZ)
---------- ------------- -------------
996 4155672 4172.36145
SYS@book> @ heap6.sql 1xzm1bn3ru86q
CHUNK_COM ALLOC_CL TOTSIZE COUNT(*) DIFF_SIZES AVGSZ MINSZ MAXSZ
---------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
qeeOpt: qeesCre freeabl 1360240 10002 2 136 128 80112
optdef: qcopCre freeabl 1201200 10000 2 120 120 136
opn: qkexrInitO freeabl 890112 10005 3 89 88 120
ub1[]: qkexrXfo freeabl 326320 10000 3 33 32 56
strdef_buf : kk freeabl 323680 10001 4 32 32 64
kksol : kksnsg freeabl 5056 79 1 64 64 64
kctdef : qcdlgo freeabl 1224 3 2 408 400 424
ctxdef:kksLoadC freeabl 936 1 1 936 936 936
free memory free 720 1 1 720 720 720
kccdef: qkxrMem freeabl 672 3 1 224 224 224
idndef : qcuAll freeabl 560 14 1 40 40 40
audRegFro:audta freeabl 544 4 1 136 136 136
opixpop:kctdef freeabl 400 1 1 400 400 400
kctdef : qcsfps freeabl 400 1 1 400 400 400
qertbs:qertbIAl freeabl 360 1 1 360 360 360
chedef : qcuatc freeabl 240 6 1 40 40 40
pqctx:kkfdParal freeabl 232 1 1 232 232 232
idndef*[]: qkex freeabl 224 4 1 56 56 56
kggsmInitCompac freeabl 184 5 3 37 32 48
ctxqrol : kkqsr freeabl 160 2 1 80 80 80
qcctx : kkmqccr freeabl 152 1 1 152 152 152
kggsmCommonInit freeabl 144 1 1 144 144 144
qeSel: qkxrXfor freeabl 144 3 1 48 48 48
kksol : kkscuf freeabl 128 2 1 64 64 64
kafco : qkacol freeabl 120 1 1 120 120 120
opiprwd : opitc freeabl 104 1 1 104 104 104
ktamd : ktagmd freeabl 96 2 1 48 48 48
qkaapd : qkaqkn freeabl 96 1 1 96 96 96
KGHSC_ALLOC_BUF freeabl 88 1 1 88 88 88
ctxPlanSig:qksc freeabl 88 1 1 88 88 88
kggsmInit:sm freeabl 80 1 1 80 80 80
permanent memor perm 80 1 1 80 80 80
qertbAllocatePa freeabl 80 1 1 80 80 80
qcpctx: kkmqccr freeabl 80 1 1 80 80 80
qksmm: qksmmCs freeabl 72 1 1 72 72 72
qcsctx: kkmqccr freeabl 72 1 1 72 72 72
unmdef in opipr freeabl 64 1 1 64 64 64
kobjn : kkdcchs freeabl 64 2 1 32 32 32
cxach : opiSem freeabl 64 1 1 64 64 64
qcmemctx : kkmq freeabl 56 1 1 56 56 56
kggac: kggacCre freeabl 56 1 1 56 56 56
qeeRwo: qeeCrea freeabl 56 1 1 56 56 56
qksrcMarkQB:qks freeabl 48 1 1 48 48 48
kksoff : opitca freeabl 48 1 1 48 48 48
qctctx: kkmqccr freeabl 48 1 1 48 48 48
qcptgc: kkmqccr freeabl 40 1 1 40 40 40
qesmaInitTblCtx freeabl 40 1 1 40 40 40
qkaEnableWide:c freeabl 40 1 1 40 40 40
opixfalo:froaty freeabl 32 1 1 32 32 32
xplGenXpl:planL freeabl 32 1 1 32 32 32
opixfalo:ctxkct freeabl 32 1 1 32 32 32
51 rows selected.
--//顺便贴一个生产系统in 有100个值的语句。
> @ sharepool/shp4 b23m1hg6cm11f 0
KGLHDADR KGLHDPAR KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000001053F2CE50 0 0 0 000000105A0AACD0 00000010F8A29BC8 20816 109488 9548 139852 139852 3435758638 b23m1hg6cm11f 0
parent handle address 0000001053F2CE50 0 0 0 000000105D4AE0C8 00 12529 0 0 12529 12529 3435758638 b23m1hg6cm11f 65535
--//堆6 109488
3.总结：
总之一点不实用。
heap6.sql,curheaps.sql来之tpt包。
$ cat sharepool/shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
kglhdpar, 'parent handle address',
'child handle address')
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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2848575/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
