[20211221]提示precompute_subquery补充2.txt

来源：这里教程网时间：2026-03-03 17:20:51 作者：

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

编辑推荐：

返回列表

[20211221]提示precompute_subquery补充2.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推