[20211221]提示precompute_subquery補充2.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2848575/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20211215]提示precompute_subquery補充.txt
- [20211221]記錄使用sqlplus的小問題補充.txtSQL
- [20181227]簡單探究cluster table(補充)2.txt
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- linux命令補充Linux
- 聯通性補充
- JVM補充篇JVM
- 負載均衡補充負載
- explian type extra補充
- step1 補充
- lambda（持續補充）
- 博弈補充練習
- while迴圈補充While
- redis筆記補充Redis筆記
- ping（未完待補充）
- ReadFile 和 補充CreateFile
- Servlet學習補充Servlet
- css雜項補充CSS
- 網路流概念補充
- [Java併發]ThreadLocal補充Javathread
- 二分圖補充
- Spring註解補充（一）Spring
- 前端補充：url編碼前端
- Jaeger知識點補充
- 有關元件的補充~~~~~~~元件
- 陣列常用方法補充陣列
- [20230329]利用bind_aware提示最佳化案例2.txt
- PS的一些補充
- omnet6.0.1安裝補充
- java 註解學習補充Java
- CC1補充-LazyMap利用
- Scrapy 常用方法以及其補充
- MybatisPlus的一些補充MyBatis
- Golang基礎語法補充Golang
- Python補充02 Python小技巧Python
- 面試題抽答（補充）面試題
- 【排序】氣泡排序（待補充）排序
- Apollo 分散式配置中心（補充）分散式