[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包.不再贴出.
[20221230]提示precompute_subquery补充3.txt
来源：这里教程网
时间：2026-03-03 18:20:42
作者：
编辑推荐：
- [20221230]提示precompute_subquery补充3.txt03-03
- [20230108]ORA-00600 and Session Disconnected.txt03-03
- [20230108]ORA-00600 and Session Disconnected 2.txt03-03
- [20230119]12c grant read 特性.txt03-03
- [20230111]记录dml语句中的绑定变量.txt03-03
- [20230124]测试是否能使用物化视图优化.txt03-03
- [20230125]21c Force matching signature的计算.txt03-03
- 5款用过就舍不得删除的电脑软件03-03
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 5款用过就舍不得删除的电脑软件
5款用过就舍不得删除的电脑软件
26-03-03 - pdb库单库升级文档
pdb库单库升级文档
26-03-03 - 5款非凡的电脑软件，用过才知道好
5款非凡的电脑软件，用过才知道好
26-03-03 - 记一次DG修复后无法打开小乌龙
记一次DG修复后无法打开小乌龙
26-03-03 - 飞书二度出海“谋生”
飞书二度出海“谋生”
26-03-03 - 消毒柜行业的2023：变局、商机和反思
消毒柜行业的2023：变局、商机和反思
26-03-03 - database 空值问题
database 空值问题
26-03-03 - 19C PGA占用过载优化
19C PGA占用过载优化
26-03-03 - LINUX 环境 mysql to mysql OGG安装配置（一）
LINUX 环境 mysql to mysql OGG安装配置（一）
26-03-03 - LINUX 环境 mysql to oracle OGG安装配置
LINUX 环境 mysql to oracle OGG安装配置
26-03-03