[20221230]提示precompute_subquery补充3.txt
[20221230]提示precompute_subquery补充3.txt
--//补充提示precompute_subquery的测试.
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SCOTT@test01p> create table tx as select object_id deptno from all_objects;
Table created.
SCOTT@test01p> @ tpt/gts tx
Gather Table Statistics for table tx...
exec dbms_stats.gather_table_stats(null, upper('tx'), null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
2.测试1:
SCOTT@test01p> 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
SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
125640918 1xzm1bn3ru86q 0 73942 3383998547 77d20d6 2023-01-22 10:01:20 16777216
--//sql_id=1xzm1bn3ru86q.传入10000个参数值,看看堆6占用空间.
SYS@test> @ 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 000007FF15AE9610 000007FF15AC0708 select * from dept where deptno in (sel 0 0 0 000007FF1588A2A8 000007FF155C9E80 4032 5077120 3243 5084395 5084395 125640918 1xzm1bn3ru86q 0
parent handle address 000007FF15AC0708 000007FF15AC0708 select * from dept where deptno in (sel 0 0 0 000007FF000CA430 00 4072 0 0 4072 4072 125640918 1xzm1bn3ru86q 65535
--//可以发现堆6占用5077120 , 5077120/1024/1024 = 4.84 接近5M. )
SYS@test> @ tpt/curheaps.sql 125640918 0
KGLNAHSH KGLHDPAR SQL_ID CHILD# KGLHDADR KGLOBHD0 SIZE0 SIZE1 SIZE2 SIZE3 KGLOBHD4 SIZE4 SIZE5 KGLOBHD6 SIZE6 SIZE7 STATUS
---------- ---------------- ------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ----------
125640918 000007FF15AC0708 1xzm1bn3ru86q 0 000007FF15AE9610 000007FF1588A2A8 4032 0 0 0 00 0 0 000007FF155C9E80 5077120 0 ##########
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP0 perm permanent memor 2968 2
HEAP0 freeabl kgltbtab 608 4
HEAP0 free free memory 456 1
no rows selected
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP6 freeabl qeeOpt: qeesCre 1760944 10002
HEAP6 freeabl optdef: qcopCre 1360000 10000
HEAP6 freeabl opn: qkexrInitO 1126416 10005
HEAP6 freeabl ub1[]: qkexrXfo 407456 10000
HEAP6 freeabl strdef_buf : kk 403536 10001
HEAP6 freeabl kksol : kksnsg 5120 80
HEAP6 freeabl kctdef : qcdlgo 1296 3
HEAP6 freeabl 16322.kgght 1152 2
HEAP6 freeabl ctxdef:kksLoadC 1024 1
HEAP6 freeabl 181.kggfa 824 3
HEAP6 freeabl kccdef: qkxrMem 792 3
HEAP6 freeabl audRegFro:audta 672 4
HEAP6 recr 181.kggfa 576 1
HEAP6 freeabl idndef : qcuAll 560 14
HEAP6 freeabl qosdInitExprCtx 552 10
HEAP6 free free memory 512 1
HEAP6 freeabl opixpop:kctdef 432 1
HEAP6 freeabl kctdef : qcsfps 432 1
HEAP6 freeabl qertbs:qertbIAl 432 1
HEAP6 freeabl qosdUpdateExprM 384 4
HEAP6 freeabl pqctx:kkfdParal 296 1
HEAP6 freeabl unmdef in opipr 296 1
HEAP6 freeabl chedef : qcuatc 280 6
HEAP6 freeabl idndef*[]: qkex 224 4
HEAP6 freeabl kggsmInitCompac 208 5
HEAP6 freeabl ctxqrol : kkqsr 192 2
HEAP6 freeabl qcctx : kkmqccr 152 1
HEAP6 freeabl qeSel: qkxrXfor 144 3
HEAP6 freeabl kggsmCommonInit 144 1
HEAP6 freeabl kafco : qkacol 144 1
HEAP6 freeabl kksol : kkscuf 128 2
HEAP6 perm permanent memor 112 1
HEAP6 freeabl opiprwd : opitc 104 1
HEAP6 freeabl qkaapd : qkaqkn 96 1
HEAP6 freeabl ktamd : ktagmd 96 2
HEAP6 freeabl ctxPlanSig:qksc 88 1
HEAP6 freeabl qcsctx: kkmqccr 88 1
HEAP6 freeabl qertbAllocatePa 88 1
HEAP6 freeabl KGHSC_ALLOC_BUF 88 1
HEAP6 freeabl kobjn : kkdcchs 80 2
HEAP6 freeabl kggsmInit:sm 80 1
HEAP6 freeabl qcpctx: kkmqccr 80 1
HEAP6 freeabl qksmm: qksmmCs 72 1
HEAP6 freeabl qeeRwo: qeeCrea 64 1
HEAP6 freeabl cxach : opiSem 64 1
HEAP6 freeabl kggac: kggacCre 64 1
HEAP6 freeabl qesmaInitTblCtx 64 1
HEAP6 freeabl kggslHd:Init 64 1
HEAP6 freeabl qctctx: kkmqccr 56 1
HEAP6 freeabl qcmemctx : kkmq 56 1
HEAP6 freeabl kksoff : opitca 48 1
HEAP6 freeabl qksrcMarkQB:qks 48 1
HEAP6 freeabl opixfalo:froaty 40 1
HEAP6 freeabl xplGenXpl:planL 40 1
HEAP6 freeabl qkaEnableWide:c 40 1
HEAP6 freeabl opixfalo:ctxkct 40 1
HEAP6 freeabl qcptgc: kkmqccr 40 1
57 rows selected.
--//可以发现前面ALLOC_COMMENT的chunk的数量接近10000个.
3.测试2:
SCOTT@test01p> select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10001);
DEPTNO DNAME LOC
---------- -------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
3971792467 3rwsvv3qbtgkm 0 48723 176097179 ecbcbe53 2023-01-22 10:09:43 16777217
--//sql_id=3rwsvv3qbtgkm.传入10001个参数值,看看堆6占用空间.
SYS@test> @ sharepool/shp4 3rwsvv3qbtgkm 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000007FF003B0200 000007FF157317B8 select * from dept where deptno in (sel 0 0 0 000007FF156E4190 000007FF00BAE1B8 4072 1784704 3243 1792019 1792019 3971792467 3rwsvv3qbtgkm 0
parent handle address 000007FF157317B8 000007FF157317B8 select * from dept where deptno in (sel 0 0 0 000007FF122BB3A8 00 4072 0 0 4072 4072 3971792467 3rwsvv3qbtgkm 65535
--//可以发现堆6占用1784704,1784704/1024/1024 = 1.70,接近1.7M,明显比前面少 )
SYS@test> @ tpt/curheaps.sql 3971792467 0
KGLNAHSH KGLHDPAR SQL_ID CHILD# KGLHDADR KGLOBHD0 SIZE0 SIZE1 SIZE2 SIZE3 KGLOBHD4 SIZE4 SIZE5 KGLOBHD6 SIZE6 SIZE7 STATUS
---------- ---------------- ------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ----------
3971792467 000007FF157317B8 3rwsvv3qbtgkm 0 000007FF003B0200 000007FF156E4190 4072 0 0 0 00 0 0 000007FF00BAE1B8 1784704 0 ##########
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP0 perm permanent memor 3272 2
HEAP0 freeabl kgltbtab 760 5
no rows selected
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP6 freeabl optdef: qcopCre 1360000 10000
HEAP6 freeabl strdef_buf : kk 403624 10001
HEAP6 free free memory 1840 1
HEAP6 freeabl idndef : qcuAll 1416 35
HEAP6 freeabl kctdef : qcdlgo 1296 3
HEAP6 freeabl opn: qkexrInitO 1208 9
HEAP6 recr 181.kggfa 1152 2
HEAP6 freeabl 16322.kgght 1152 2
HEAP6 freeabl kccdef: qkxrMem 1056 4
HEAP6 freeabl ctxdef:kksLoadC 1024 1
HEAP6 freeabl 181.kggfa 1008 4
HEAP6 freeabl qertbs:qertbIAl 864 2
HEAP6 freeabl opixpop:kctdef 864 2
HEAP6 freeabl audRegFro:audta 672 4
HEAP6 freeabl qeeOpt: qeesCre 656 4
HEAP6 freeabl qosdInitExprCtx 592 11
HEAP6 freeabl kctdef : qcsfps 432 1
HEAP6 freeabl qosdUpdateExprM 384 4
HEAP6 freeabl qeeRwo: qeeCrea 320 6
HEAP6 freeabl chedef : qcuatc 320 7
HEAP6 freeabl unmdef in opipr 296 1
HEAP6 freeabl pqctx:kkfdParal 296 1
HEAP6 freeabl idndef*[]: qkex 280 5
HEAP6 freeabl kksol : kksnsg 256 4
HEAP6 freeabl rfldef : qerflA 224 2
HEAP6 freeabl kggsmInitCompac 216 5
HEAP6 freeabl kafco : qkacol 208 2
HEAP6 freeabl ctxqrol : kkqsr 192 2
HEAP6 freeabl qertbAllocatePa 176 2
HEAP6 freeabl KGHSC_ALLOC_BUF 152 1
HEAP6 freeabl qcctx : kkmqccr 152 1
HEAP6 freeabl qeSel: qkxrXfor 144 3
HEAP6 freeabl kggsmCommonInit 144 1
HEAP6 freeabl kggslHd:Init 128 2
HEAP6 freeabl qesmaInitTblCtx 128 2
HEAP6 freeabl kksol : kkscuf 128 2
HEAP6 perm permanent memor 112 1
HEAP6 freeabl kggac: kggacCre 112 2
HEAP6 freeabl qercos : qercoA 112 1
HEAP6 freeabl qecsub : qkxrPX 104 1
HEAP6 freeabl opiprwd : opitc 104 1
HEAP6 freeabl ktamd : ktagmd 96 2
HEAP6 freeabl ctxPlanSig:qksc 88 1
HEAP6 freeabl qcsctx: kkmqccr 88 1
HEAP6 freeabl kggsmInit:sm 80 1
HEAP6 freeabl kobjn : kkdcchs 80 2
HEAP6 freeabl qcpctx: kkmqccr 80 1
HEAP6 freeabl qksmm: qksmmCs 72 1
HEAP6 freeabl cxach : opiSem 64 1
HEAP6 freeabl xplGenXpl:planL 64 1
HEAP6 freeabl qcmemctx : kkmq 56 1
HEAP6 freeabl qctctx: kkmqccr 56 1
HEAP6 freeabl qksrcMarkQB:qks 48 1
HEAP6 freeabl kksoff : opitca 48 1
HEAP6 freeabl ub1[]: qkexrXfo 40 1
HEAP6 freeabl opixfalo:froaty 40 1
HEAP6 freeabl qcptgc: kkmqccr 40 1
HEAP6 freeabl qeeOpn*[]: qkex 40 1
HEAP6 freeabl opixfalo:ctxkct 40 1
HEAP6 freeabl qeePrm: qkxrXfo 40 1
60 rows selected.
--//可以发现仅仅2个ALLOC_COMMENT= optdef: qcopCre,strdef_buf : kk的chunk数量大于10000.
--//也就是当出现带入参数数量超过10000时,执行计划停止转换,采用另外的执行方式.
--//这是因为PRECOMPUTE_SUBQUERY提示能接受in的变量数量是10000.超过10000,执行计划提示无效.
--//对比两者的执行计划:
SCOTT@test01p> 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
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1xzm1bn3ru86q, child number 3
-------------------------------------
select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */
deptno from tx where rownum<=10000)
Plan hash value: 3383998547
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 4 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 8 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("DEPTNO"=2 OR "DEPTNO"=3 OR "DEPTNO"=4 OR "DEPTNO"=5 OR "DEPTNO"=6 OR "DEPTNO"=7 OR
"DEPTNO"=8 OR "DEPTNO"=9 OR "DEPTNO"=10 OR "DEPTNO"=11 OR "DEPTNO"=12 OR "DEPTNO"=13 OR "DEPTNO"=14 OR
"DEPTNO"=15 OR "DEPTNO"=16 OR "DEPTNO"=17 OR "DEPTNO"=18 OR "DEPTNO"=19 OR "DEPTNO"=20 OR "DEPTNO"=21 OR
"DEPTNO"=22 OR "DEPTNO"=23 OR "DEPTNO"=24 OR "DEPTNO"=25 OR "DEPTNO"=26 OR "DEPTNO"=27 OR "DEPTNO"=28 OR
"DEPTNO"=29 OR "DEPTNO"=30 OR "DEPTNO"=31 OR "DEPTNO"=32 OR "DEPTNO"=33 OR "DEPTNO"=34 OR "DEPTNO"=35 OR
"DEPTNO"=36 OR "DEPTNO"=37 OR "DEPTNO"=38 OR "DEPTNO"=39 OR "DEPTNO"=40 OR "DEPTNO"=41 OR "DEPTNO"=42 OR
"DEPTNO"=43 OR "DEPTNO"=44 OR "DEPTNO"=45 OR "DEPTNO"=46 OR "DEPTNO"=47 OR "DEPTNO"=48 OR "DEPTNO"=49 OR
"DEPTNO"=50 OR "DEPTNO"=51 OR "DEPTNO"=52 OR "DEPTNO"=53 OR "DEPTNO"=54 OR "DEPTNO"=55 OR "DEPTNO"=56 OR
"DEPTNO"=57 OR "DEPTNO"=58 OR "DEPTNO"=59 OR "DEPTNO"=60 OR "DEPTNO"=61 OR "DEPTNO"=62 OR "DEPTNO"=63 OR
"DEPTNO"=64 OR "DEPTNO"=65 OR "DEPTNO"=66 OR "DEPTNO"=67 OR "DEPTNO"=68 OR "DEPTNO"=69 OR "DEPTNO"=70 OR
"DEPTNO"=71 OR "DEPTNO"=72 OR "DEPTNO"=73 OR "DEPTNO"=74 OR "DEPTNO"=75 OR "DEPTNO"=76 OR "DEPTNO"=77 OR
"DEPTNO"=78 OR "DEPTNO"=79 OR "DEPTNO"=80 OR "DEPTNO"=81 OR "DEPTNO"=82 OR "DEPTNO"=83 OR "DEPTNO"=84 OR
"DEPTNO"=85 OR "DEPTNO"=86 OR "DEPTNO"=87 OR "DEPTNO"=88 OR "DEPTNO"=89 OR "DEPTNO"=90 OR "DEPTNO"=91 OR
"DEPTNO"=92 OR "DEPTNO"=93 OR "DEPTNO"=94 OR "DEPTNO"=95 OR "DEPTNO"=96 OR "DEPTNO"=97 OR "DEPTNO"=98 OR
"DEPTNO"=99 OR "DEPTNO"=100 OR "DEPTNO"=101 OR "DEPTNO"=102 OR "DEPTNO"=103 OR "DEPTNO"=104 OR "DEPTNO"=105
OR "DEPTNO"=106 OR "DEPTNO"=107 OR "DEPTNO"=108 OR "DEPTNO"=109 OR "DEPTNO"=110 OR "DEPTNO"=111 OR
"DEPTNO"=112 OR "DEPTNO"=113 OR "DEPTNO"=114 OR "DEPTNO"=115 OR "DEPTNO"=116 OR "DEPTNO"=117 OR
"DEPTNO"=118 OR "DEPTNO"=119 OR "DEPTNO"=120 OR "DEPTNO"=121 OR "DEPTNO"=122 OR "DEPTNO"=123 OR
"DEPTNO"=124 OR "DEPTNO"=126 OR "DEPTNO"=127 OR "DEPTNO"=130 OR "DEPTNO"=131 OR "DEPTNO"=132 OR
"DEPTNO"=133 OR "DEPTNO"=134 OR "DEPTNO"=135 OR "DEPTNO"=138 OR "DEPTNO"=139 OR "DEPTNO"=140 OR
"DEPTNO"=141 OR "DEPTNO"=142 OR "DEPTNO"=143 OR "DEPTNO"=144 OR "DEPTNO"=145 OR "DEPTNO"=146 OR
"DEPTNO"=147 OR "DEPTNO"=148 OR "DEPTNO"=149 OR "DEPTNO"=150 OR "DEPTNO"=151 OR "DEPTNO"=154 OR
"DEPTNO"=155 OR "DEPTNO"=156 OR "DEPTNO"=157 OR "DEPTNO"=158 OR "DEPTNO"=159 OR "DEPTNO"=160 OR
"DEPTNO"=161 OR "DEPTNO"=162 OR "DEPTNO"=163 OR "DEPTNO"=164 OR "DEPTNO"=165 OR "DEPTNO"=166 OR
"DEPTNO"=167 OR "DEPTNO"=168 OR "DEPTNO"=169 OR "DEPTNO"=170 OR "DEPTNO"=171 OR "DEPTNO"=172 OR
"DEPTNO"=173 OR "DEPTNO"=174 OR "DEPTNO"=175 OR "DEPTNO"=176 OR "DEPTNO"=178 OR "DEPTNO"=179 OR
"DEPTNO"=180 OR "DEPTNO"=181 OR "DEPTNO"=182 OR "DEPTNO"=183 OR "DEPTNO"=184 OR "DEPTNO"=185 OR
"DEPTNO"=186 OR "DEPTNO"=187 OR "DEPTNO"=190 OR "DEPTNO"=191 OR "DEPTNO"=192 OR "DEPTNO"=193 OR
"DEPTNO"=196 OR "DEPTNO"=197 OR "DEPTNO"=198 OR "DEPTNO"=199 OR "DEPTNO"=200 OR "DEPTNO"=201 OR
"DEPTNO"=202 OR "DEPTNO"=203 OR "DEPTNO"=204 OR "DEPTNO"=205 OR "DEPTNO"=206 OR "DEPTNO"=207 OR
"DEPTNO"=208 OR "DEPTNO"=209 OR "DEPTNO"=210 OR "DEPTNO"=211 OR "DEPTNO"=212 OR "DEPTNO"=213 OR
"DEPTNO"=214 OR "DEPTNO"=215 OR "DEPTNO"=220 OR "DEPTNO"=221 OR "DEPTNO"=223 OR "DEPTNO"=224 OR
"DEPTNO"=225 OR "DEPTNO"=226 OR "DEPTNO"=227 OR "DEPTNO"=228 OR "DEPTNO"=235 OR "DEPTNO"=236 OR
"DEPTNO"=238 OR "DEPTNO"=240 OR "DEPTNO"=241 OR "DEPTNO"=242 OR "DEPTNO"=243 OR "DEPTNO"=244 OR
"DEPTNO"=245 OR "DEPTNO"=246 OR "DEPTNO"=247 OR "DEPTNO"=248 OR "DEPTNO"=249 OR "DEPTNO"=250 OR
"DEPTNO"=251 OR "DEPTNO"=252 OR "DEPTNO"=253 OR "DEPTNO"=254 OR "DEPTNO"=255 OR "DEPTNO"=256 OR
"DEPTNO"=257 OR "DEPTNO"=258 OR "DEPTNO"=259 OR "DEPTNO"=260 OR "DEPTNO"=263 OR "DEPTNO"=264 OR
"DEPTNO"=265 OR "DEPTNO"=266 OR "DEPTNO"=267 OR "DEPTNO"=268 OR "DEPTNO"=269 OR "DEPTNO"=270 OR
"DEPTNO"=271 OR "DEPTNO"=272 OR "DEPTNO"=273 OR "DEPTNO"=274 OR "DEPTNO"=275 OR "DEPTNO"=276 OR
"DEPTNO"=277 OR "DEPTNO"=278 OR "DEPTNO"=279 OR "DEPTNO"=280 OR "DEPTNO"=281 OR "DEPTNO"=282 OR
"DEPTNO"=283 OR "DEPTNO"=284 OR "DEPTNO")
64 rows selected.
SCOTT@test01p> select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10001);
DEPTNO DNAME LOC
---------- -------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3rwsvv3qbtgkm, child number 0
-------------------------------------
select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */
deptno from tx where rownum<=10001)
Plan hash value: 176097179
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 4 |00:00:00.01 | 44 |
|* 1 | FILTER | | 1 | | | | | 4 |00:00:00.01 | 44 |
| 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 8 |
|* 3 | FILTER | | 4 | | | | | 4 |00:00:00.01 | 36 |
|* 4 | COUNT STOPKEY | | 4 | | | | | 134 |00:00:00.01 | 36 |
| 5 | TABLE ACCESS FULL| TX | 4 | 1 | 4 | 2 (0)| 00:00:01 | 134 |00:00:00.01 | 36 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$2
5 - SEL$2 / TX@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("DEPTNO"=:B1)
4 - filter(ROWNUM<=10001)
33 rows selected.
--//对比两者执行计划发现不同.
4.再看看没有PRECOMPUTE_SUBQUERY提示的情况:
SCOTT@test01p> select * from dept where deptno in (select deptno from tx where rownum<=10001);
DEPTNO DNAME LOC
---------- -------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
4010294406 0dz0bhbrhhg46 0 15494 176097179 ef083c86 2023-01-22 10:25:23 16777218
SYS@test> @ sharepool/shp4 0dz0bhbrhhg46 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000007FF14E03390 000007FF15A335C0 select * from dept where deptno in (sel 1 0 0 000007FF11963550 000007FF15BE9C50 8144 20256 3217 31617 31617 4010294406 0dz0bhbrhhg46 0
parent handle address 000007FF15A335C0 000007FF15A335C0 select * from dept where deptno in (sel 1 0 0 000007FF123ADC58 00 4072 0 0 4072 4072 4010294406 0dz0bhbrhhg46 65535
--//可以发现堆6占用20256,占用很少)
5.总结:
--//以前也测试过PRECOMPUTE_SUBQUERY不实用,我估计仅仅适合OLAP系统.而且如果提示起效,多次执行每次都会生成新的子光标.
--//子光标堆6占用很大的内存空间.
6.附件:
$ 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;
--//另外curheaps.sql来之tpt包.不再贴出.
--//补充提示precompute_subquery的测试.
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SCOTT@test01p> create table tx as select object_id deptno from all_objects;
Table created.
SCOTT@test01p> @ tpt/gts tx
Gather Table Statistics for table tx...
exec dbms_stats.gather_table_stats(null, upper('tx'), null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
2.测试1:
SCOTT@test01p> 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
SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
125640918 1xzm1bn3ru86q 0 73942 3383998547 77d20d6 2023-01-22 10:01:20 16777216
--//sql_id=1xzm1bn3ru86q.传入10000个参数值,看看堆6占用空间.
SYS@test> @ 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 000007FF15AE9610 000007FF15AC0708 select * from dept where deptno in (sel 0 0 0 000007FF1588A2A8 000007FF155C9E80 4032 5077120 3243 5084395 5084395 125640918 1xzm1bn3ru86q 0
parent handle address 000007FF15AC0708 000007FF15AC0708 select * from dept where deptno in (sel 0 0 0 000007FF000CA430 00 4072 0 0 4072 4072 125640918 1xzm1bn3ru86q 65535
--//可以发现堆6占用5077120 , 5077120/1024/1024 = 4.84 接近5M. )
SYS@test> @ tpt/curheaps.sql 125640918 0
KGLNAHSH KGLHDPAR SQL_ID CHILD# KGLHDADR KGLOBHD0 SIZE0 SIZE1 SIZE2 SIZE3 KGLOBHD4 SIZE4 SIZE5 KGLOBHD6 SIZE6 SIZE7 STATUS
---------- ---------------- ------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ----------
125640918 000007FF15AC0708 1xzm1bn3ru86q 0 000007FF15AE9610 000007FF1588A2A8 4032 0 0 0 00 0 0 000007FF155C9E80 5077120 0 ##########
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP0 perm permanent memor 2968 2
HEAP0 freeabl kgltbtab 608 4
HEAP0 free free memory 456 1
no rows selected
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP6 freeabl qeeOpt: qeesCre 1760944 10002
HEAP6 freeabl optdef: qcopCre 1360000 10000
HEAP6 freeabl opn: qkexrInitO 1126416 10005
HEAP6 freeabl ub1[]: qkexrXfo 407456 10000
HEAP6 freeabl strdef_buf : kk 403536 10001
HEAP6 freeabl kksol : kksnsg 5120 80
HEAP6 freeabl kctdef : qcdlgo 1296 3
HEAP6 freeabl 16322.kgght 1152 2
HEAP6 freeabl ctxdef:kksLoadC 1024 1
HEAP6 freeabl 181.kggfa 824 3
HEAP6 freeabl kccdef: qkxrMem 792 3
HEAP6 freeabl audRegFro:audta 672 4
HEAP6 recr 181.kggfa 576 1
HEAP6 freeabl idndef : qcuAll 560 14
HEAP6 freeabl qosdInitExprCtx 552 10
HEAP6 free free memory 512 1
HEAP6 freeabl opixpop:kctdef 432 1
HEAP6 freeabl kctdef : qcsfps 432 1
HEAP6 freeabl qertbs:qertbIAl 432 1
HEAP6 freeabl qosdUpdateExprM 384 4
HEAP6 freeabl pqctx:kkfdParal 296 1
HEAP6 freeabl unmdef in opipr 296 1
HEAP6 freeabl chedef : qcuatc 280 6
HEAP6 freeabl idndef*[]: qkex 224 4
HEAP6 freeabl kggsmInitCompac 208 5
HEAP6 freeabl ctxqrol : kkqsr 192 2
HEAP6 freeabl qcctx : kkmqccr 152 1
HEAP6 freeabl qeSel: qkxrXfor 144 3
HEAP6 freeabl kggsmCommonInit 144 1
HEAP6 freeabl kafco : qkacol 144 1
HEAP6 freeabl kksol : kkscuf 128 2
HEAP6 perm permanent memor 112 1
HEAP6 freeabl opiprwd : opitc 104 1
HEAP6 freeabl qkaapd : qkaqkn 96 1
HEAP6 freeabl ktamd : ktagmd 96 2
HEAP6 freeabl ctxPlanSig:qksc 88 1
HEAP6 freeabl qcsctx: kkmqccr 88 1
HEAP6 freeabl qertbAllocatePa 88 1
HEAP6 freeabl KGHSC_ALLOC_BUF 88 1
HEAP6 freeabl kobjn : kkdcchs 80 2
HEAP6 freeabl kggsmInit:sm 80 1
HEAP6 freeabl qcpctx: kkmqccr 80 1
HEAP6 freeabl qksmm: qksmmCs 72 1
HEAP6 freeabl qeeRwo: qeeCrea 64 1
HEAP6 freeabl cxach : opiSem 64 1
HEAP6 freeabl kggac: kggacCre 64 1
HEAP6 freeabl qesmaInitTblCtx 64 1
HEAP6 freeabl kggslHd:Init 64 1
HEAP6 freeabl qctctx: kkmqccr 56 1
HEAP6 freeabl qcmemctx : kkmq 56 1
HEAP6 freeabl kksoff : opitca 48 1
HEAP6 freeabl qksrcMarkQB:qks 48 1
HEAP6 freeabl opixfalo:froaty 40 1
HEAP6 freeabl xplGenXpl:planL 40 1
HEAP6 freeabl qkaEnableWide:c 40 1
HEAP6 freeabl opixfalo:ctxkct 40 1
HEAP6 freeabl qcptgc: kkmqccr 40 1
57 rows selected.
--//可以发现前面ALLOC_COMMENT的chunk的数量接近10000个.
3.测试2:
SCOTT@test01p> select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10001);
DEPTNO DNAME LOC
---------- -------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
3971792467 3rwsvv3qbtgkm 0 48723 176097179 ecbcbe53 2023-01-22 10:09:43 16777217
--//sql_id=3rwsvv3qbtgkm.传入10001个参数值,看看堆6占用空间.
SYS@test> @ sharepool/shp4 3rwsvv3qbtgkm 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000007FF003B0200 000007FF157317B8 select * from dept where deptno in (sel 0 0 0 000007FF156E4190 000007FF00BAE1B8 4072 1784704 3243 1792019 1792019 3971792467 3rwsvv3qbtgkm 0
parent handle address 000007FF157317B8 000007FF157317B8 select * from dept where deptno in (sel 0 0 0 000007FF122BB3A8 00 4072 0 0 4072 4072 3971792467 3rwsvv3qbtgkm 65535
--//可以发现堆6占用1784704,1784704/1024/1024 = 1.70,接近1.7M,明显比前面少 )
SYS@test> @ tpt/curheaps.sql 3971792467 0
KGLNAHSH KGLHDPAR SQL_ID CHILD# KGLHDADR KGLOBHD0 SIZE0 SIZE1 SIZE2 SIZE3 KGLOBHD4 SIZE4 SIZE5 KGLOBHD6 SIZE6 SIZE7 STATUS
---------- ---------------- ------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ----------
3971792467 000007FF157317B8 3rwsvv3qbtgkm 0 000007FF003B0200 000007FF156E4190 4072 0 0 0 00 0 0 000007FF00BAE1B8 1784704 0 ##########
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP0 perm permanent memor 3272 2
HEAP0 freeabl kgltbtab 760 5
no rows selected
HEAP CLASS ALLOC_COMMENT BYTES CHUNKS
----- -------- ---------------- ---------- ----------
HEAP6 freeabl optdef: qcopCre 1360000 10000
HEAP6 freeabl strdef_buf : kk 403624 10001
HEAP6 free free memory 1840 1
HEAP6 freeabl idndef : qcuAll 1416 35
HEAP6 freeabl kctdef : qcdlgo 1296 3
HEAP6 freeabl opn: qkexrInitO 1208 9
HEAP6 recr 181.kggfa 1152 2
HEAP6 freeabl 16322.kgght 1152 2
HEAP6 freeabl kccdef: qkxrMem 1056 4
HEAP6 freeabl ctxdef:kksLoadC 1024 1
HEAP6 freeabl 181.kggfa 1008 4
HEAP6 freeabl qertbs:qertbIAl 864 2
HEAP6 freeabl opixpop:kctdef 864 2
HEAP6 freeabl audRegFro:audta 672 4
HEAP6 freeabl qeeOpt: qeesCre 656 4
HEAP6 freeabl qosdInitExprCtx 592 11
HEAP6 freeabl kctdef : qcsfps 432 1
HEAP6 freeabl qosdUpdateExprM 384 4
HEAP6 freeabl qeeRwo: qeeCrea 320 6
HEAP6 freeabl chedef : qcuatc 320 7
HEAP6 freeabl unmdef in opipr 296 1
HEAP6 freeabl pqctx:kkfdParal 296 1
HEAP6 freeabl idndef*[]: qkex 280 5
HEAP6 freeabl kksol : kksnsg 256 4
HEAP6 freeabl rfldef : qerflA 224 2
HEAP6 freeabl kggsmInitCompac 216 5
HEAP6 freeabl kafco : qkacol 208 2
HEAP6 freeabl ctxqrol : kkqsr 192 2
HEAP6 freeabl qertbAllocatePa 176 2
HEAP6 freeabl KGHSC_ALLOC_BUF 152 1
HEAP6 freeabl qcctx : kkmqccr 152 1
HEAP6 freeabl qeSel: qkxrXfor 144 3
HEAP6 freeabl kggsmCommonInit 144 1
HEAP6 freeabl kggslHd:Init 128 2
HEAP6 freeabl qesmaInitTblCtx 128 2
HEAP6 freeabl kksol : kkscuf 128 2
HEAP6 perm permanent memor 112 1
HEAP6 freeabl kggac: kggacCre 112 2
HEAP6 freeabl qercos : qercoA 112 1
HEAP6 freeabl qecsub : qkxrPX 104 1
HEAP6 freeabl opiprwd : opitc 104 1
HEAP6 freeabl ktamd : ktagmd 96 2
HEAP6 freeabl ctxPlanSig:qksc 88 1
HEAP6 freeabl qcsctx: kkmqccr 88 1
HEAP6 freeabl kggsmInit:sm 80 1
HEAP6 freeabl kobjn : kkdcchs 80 2
HEAP6 freeabl qcpctx: kkmqccr 80 1
HEAP6 freeabl qksmm: qksmmCs 72 1
HEAP6 freeabl cxach : opiSem 64 1
HEAP6 freeabl xplGenXpl:planL 64 1
HEAP6 freeabl qcmemctx : kkmq 56 1
HEAP6 freeabl qctctx: kkmqccr 56 1
HEAP6 freeabl qksrcMarkQB:qks 48 1
HEAP6 freeabl kksoff : opitca 48 1
HEAP6 freeabl ub1[]: qkexrXfo 40 1
HEAP6 freeabl opixfalo:froaty 40 1
HEAP6 freeabl qcptgc: kkmqccr 40 1
HEAP6 freeabl qeeOpn*[]: qkex 40 1
HEAP6 freeabl opixfalo:ctxkct 40 1
HEAP6 freeabl qeePrm: qkxrXfo 40 1
60 rows selected.
--//可以发现仅仅2个ALLOC_COMMENT= optdef: qcopCre,strdef_buf : kk的chunk数量大于10000.
--//也就是当出现带入参数数量超过10000时,执行计划停止转换,采用另外的执行方式.
--//这是因为PRECOMPUTE_SUBQUERY提示能接受in的变量数量是10000.超过10000,执行计划提示无效.
--//对比两者的执行计划:
SCOTT@test01p> 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
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1xzm1bn3ru86q, child number 3
-------------------------------------
select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */
deptno from tx where rownum<=10000)
Plan hash value: 3383998547
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 4 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 8 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("DEPTNO"=2 OR "DEPTNO"=3 OR "DEPTNO"=4 OR "DEPTNO"=5 OR "DEPTNO"=6 OR "DEPTNO"=7 OR
"DEPTNO"=8 OR "DEPTNO"=9 OR "DEPTNO"=10 OR "DEPTNO"=11 OR "DEPTNO"=12 OR "DEPTNO"=13 OR "DEPTNO"=14 OR
"DEPTNO"=15 OR "DEPTNO"=16 OR "DEPTNO"=17 OR "DEPTNO"=18 OR "DEPTNO"=19 OR "DEPTNO"=20 OR "DEPTNO"=21 OR
"DEPTNO"=22 OR "DEPTNO"=23 OR "DEPTNO"=24 OR "DEPTNO"=25 OR "DEPTNO"=26 OR "DEPTNO"=27 OR "DEPTNO"=28 OR
"DEPTNO"=29 OR "DEPTNO"=30 OR "DEPTNO"=31 OR "DEPTNO"=32 OR "DEPTNO"=33 OR "DEPTNO"=34 OR "DEPTNO"=35 OR
"DEPTNO"=36 OR "DEPTNO"=37 OR "DEPTNO"=38 OR "DEPTNO"=39 OR "DEPTNO"=40 OR "DEPTNO"=41 OR "DEPTNO"=42 OR
"DEPTNO"=43 OR "DEPTNO"=44 OR "DEPTNO"=45 OR "DEPTNO"=46 OR "DEPTNO"=47 OR "DEPTNO"=48 OR "DEPTNO"=49 OR
"DEPTNO"=50 OR "DEPTNO"=51 OR "DEPTNO"=52 OR "DEPTNO"=53 OR "DEPTNO"=54 OR "DEPTNO"=55 OR "DEPTNO"=56 OR
"DEPTNO"=57 OR "DEPTNO"=58 OR "DEPTNO"=59 OR "DEPTNO"=60 OR "DEPTNO"=61 OR "DEPTNO"=62 OR "DEPTNO"=63 OR
"DEPTNO"=64 OR "DEPTNO"=65 OR "DEPTNO"=66 OR "DEPTNO"=67 OR "DEPTNO"=68 OR "DEPTNO"=69 OR "DEPTNO"=70 OR
"DEPTNO"=71 OR "DEPTNO"=72 OR "DEPTNO"=73 OR "DEPTNO"=74 OR "DEPTNO"=75 OR "DEPTNO"=76 OR "DEPTNO"=77 OR
"DEPTNO"=78 OR "DEPTNO"=79 OR "DEPTNO"=80 OR "DEPTNO"=81 OR "DEPTNO"=82 OR "DEPTNO"=83 OR "DEPTNO"=84 OR
"DEPTNO"=85 OR "DEPTNO"=86 OR "DEPTNO"=87 OR "DEPTNO"=88 OR "DEPTNO"=89 OR "DEPTNO"=90 OR "DEPTNO"=91 OR
"DEPTNO"=92 OR "DEPTNO"=93 OR "DEPTNO"=94 OR "DEPTNO"=95 OR "DEPTNO"=96 OR "DEPTNO"=97 OR "DEPTNO"=98 OR
"DEPTNO"=99 OR "DEPTNO"=100 OR "DEPTNO"=101 OR "DEPTNO"=102 OR "DEPTNO"=103 OR "DEPTNO"=104 OR "DEPTNO"=105
OR "DEPTNO"=106 OR "DEPTNO"=107 OR "DEPTNO"=108 OR "DEPTNO"=109 OR "DEPTNO"=110 OR "DEPTNO"=111 OR
"DEPTNO"=112 OR "DEPTNO"=113 OR "DEPTNO"=114 OR "DEPTNO"=115 OR "DEPTNO"=116 OR "DEPTNO"=117 OR
"DEPTNO"=118 OR "DEPTNO"=119 OR "DEPTNO"=120 OR "DEPTNO"=121 OR "DEPTNO"=122 OR "DEPTNO"=123 OR
"DEPTNO"=124 OR "DEPTNO"=126 OR "DEPTNO"=127 OR "DEPTNO"=130 OR "DEPTNO"=131 OR "DEPTNO"=132 OR
"DEPTNO"=133 OR "DEPTNO"=134 OR "DEPTNO"=135 OR "DEPTNO"=138 OR "DEPTNO"=139 OR "DEPTNO"=140 OR
"DEPTNO"=141 OR "DEPTNO"=142 OR "DEPTNO"=143 OR "DEPTNO"=144 OR "DEPTNO"=145 OR "DEPTNO"=146 OR
"DEPTNO"=147 OR "DEPTNO"=148 OR "DEPTNO"=149 OR "DEPTNO"=150 OR "DEPTNO"=151 OR "DEPTNO"=154 OR
"DEPTNO"=155 OR "DEPTNO"=156 OR "DEPTNO"=157 OR "DEPTNO"=158 OR "DEPTNO"=159 OR "DEPTNO"=160 OR
"DEPTNO"=161 OR "DEPTNO"=162 OR "DEPTNO"=163 OR "DEPTNO"=164 OR "DEPTNO"=165 OR "DEPTNO"=166 OR
"DEPTNO"=167 OR "DEPTNO"=168 OR "DEPTNO"=169 OR "DEPTNO"=170 OR "DEPTNO"=171 OR "DEPTNO"=172 OR
"DEPTNO"=173 OR "DEPTNO"=174 OR "DEPTNO"=175 OR "DEPTNO"=176 OR "DEPTNO"=178 OR "DEPTNO"=179 OR
"DEPTNO"=180 OR "DEPTNO"=181 OR "DEPTNO"=182 OR "DEPTNO"=183 OR "DEPTNO"=184 OR "DEPTNO"=185 OR
"DEPTNO"=186 OR "DEPTNO"=187 OR "DEPTNO"=190 OR "DEPTNO"=191 OR "DEPTNO"=192 OR "DEPTNO"=193 OR
"DEPTNO"=196 OR "DEPTNO"=197 OR "DEPTNO"=198 OR "DEPTNO"=199 OR "DEPTNO"=200 OR "DEPTNO"=201 OR
"DEPTNO"=202 OR "DEPTNO"=203 OR "DEPTNO"=204 OR "DEPTNO"=205 OR "DEPTNO"=206 OR "DEPTNO"=207 OR
"DEPTNO"=208 OR "DEPTNO"=209 OR "DEPTNO"=210 OR "DEPTNO"=211 OR "DEPTNO"=212 OR "DEPTNO"=213 OR
"DEPTNO"=214 OR "DEPTNO"=215 OR "DEPTNO"=220 OR "DEPTNO"=221 OR "DEPTNO"=223 OR "DEPTNO"=224 OR
"DEPTNO"=225 OR "DEPTNO"=226 OR "DEPTNO"=227 OR "DEPTNO"=228 OR "DEPTNO"=235 OR "DEPTNO"=236 OR
"DEPTNO"=238 OR "DEPTNO"=240 OR "DEPTNO"=241 OR "DEPTNO"=242 OR "DEPTNO"=243 OR "DEPTNO"=244 OR
"DEPTNO"=245 OR "DEPTNO"=246 OR "DEPTNO"=247 OR "DEPTNO"=248 OR "DEPTNO"=249 OR "DEPTNO"=250 OR
"DEPTNO"=251 OR "DEPTNO"=252 OR "DEPTNO"=253 OR "DEPTNO"=254 OR "DEPTNO"=255 OR "DEPTNO"=256 OR
"DEPTNO"=257 OR "DEPTNO"=258 OR "DEPTNO"=259 OR "DEPTNO"=260 OR "DEPTNO"=263 OR "DEPTNO"=264 OR
"DEPTNO"=265 OR "DEPTNO"=266 OR "DEPTNO"=267 OR "DEPTNO"=268 OR "DEPTNO"=269 OR "DEPTNO"=270 OR
"DEPTNO"=271 OR "DEPTNO"=272 OR "DEPTNO"=273 OR "DEPTNO"=274 OR "DEPTNO"=275 OR "DEPTNO"=276 OR
"DEPTNO"=277 OR "DEPTNO"=278 OR "DEPTNO"=279 OR "DEPTNO"=280 OR "DEPTNO"=281 OR "DEPTNO"=282 OR
"DEPTNO"=283 OR "DEPTNO"=284 OR "DEPTNO")
64 rows selected.
SCOTT@test01p> select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10001);
DEPTNO DNAME LOC
---------- -------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3rwsvv3qbtgkm, child number 0
-------------------------------------
select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */
deptno from tx where rownum<=10001)
Plan hash value: 176097179
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 4 |00:00:00.01 | 44 |
|* 1 | FILTER | | 1 | | | | | 4 |00:00:00.01 | 44 |
| 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 8 |
|* 3 | FILTER | | 4 | | | | | 4 |00:00:00.01 | 36 |
|* 4 | COUNT STOPKEY | | 4 | | | | | 134 |00:00:00.01 | 36 |
| 5 | TABLE ACCESS FULL| TX | 4 | 1 | 4 | 2 (0)| 00:00:01 | 134 |00:00:00.01 | 36 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$2
5 - SEL$2 / TX@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("DEPTNO"=:B1)
4 - filter(ROWNUM<=10001)
33 rows selected.
--//对比两者执行计划发现不同.
4.再看看没有PRECOMPUTE_SUBQUERY提示的情况:
SCOTT@test01p> select * from dept where deptno in (select deptno from tx where rownum<=10001);
DEPTNO DNAME LOC
---------- -------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
4010294406 0dz0bhbrhhg46 0 15494 176097179 ef083c86 2023-01-22 10:25:23 16777218
SYS@test> @ sharepool/shp4 0dz0bhbrhhg46 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000007FF14E03390 000007FF15A335C0 select * from dept where deptno in (sel 1 0 0 000007FF11963550 000007FF15BE9C50 8144 20256 3217 31617 31617 4010294406 0dz0bhbrhhg46 0
parent handle address 000007FF15A335C0 000007FF15A335C0 select * from dept where deptno in (sel 1 0 0 000007FF123ADC58 00 4072 0 0 4072 4072 4010294406 0dz0bhbrhhg46 65535
--//可以发现堆6占用20256,占用很少)
5.总结:
--//以前也测试过PRECOMPUTE_SUBQUERY不实用,我估计仅仅适合OLAP系统.而且如果提示起效,多次执行每次都会生成新的子光标.
--//子光标堆6占用很大的内存空间.
6.附件:
$ 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;
--//另外curheaps.sql来之tpt包.不再贴出.
