[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包.不再贴出.