[20221230]提示precompute_subquery補充3.txt

来源:https://www.cnblogs.com/lfree/archive/2023/01/23/17065315.html
-Advertisement-
Play Games

##視圖 ###什麼是視圖 視圖是一張虛表(建立在真實的table的基礎之上,即視圖的數據來源是對應的table). 首先需要創建一張表,在表的基礎上,指定的列映射成一個視圖. 就是一個SELECT查詢語句(過濾掉安全隱患列的數據),把它查到的數據作為視圖的數據進行映射 ###視圖的語法 ####視 ...


[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包.不再貼出.
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 最近寫了個 python 的 tkinter 框架入門教程,希望給大家介紹一下這個框架的上手過程,這是一個系列文章,現在是第一篇。 ...
  • 2023-01-22 一、SSM整合 1、Spring + SpringMVC (1)容器管理對象,由DispatcherServlet管理 (2)Spring容器對象,由ContextLoaderListener管理 2、解決組件掃描的衝突問題 (1)SpringMVC只掃描Controller層 ...
  • 這篇筆記咱日後應該還會進行補充。 關於sort的比較函數 STL的algorithm庫中的sort函數,可以接受一個cmp函數作為第三個參數,用來指定排序的規則。 自定義sort比較函數 cmp(a,b)函數的返回值是一個bool值,當返回值為true時不改變元素順序。 可以把其中的a看作序列中前一 ...
  • 2023-01-22 一、SpringMVC攔截器的兩種裝配方式 1、全局裝配(放置在springmvc.xml中) <!-- 裝配攔截器--> <!-- 全局裝配--> <mvc:interceptors> <ref bean="myInterceptor"></ref> </mvc:interc ...
  • 題目描述 牛牛從鍵盤上輸入三個整數,並嘗試在屏幕上顯示第二個整數。 輸入描述 一行輸入 3 個整數,用空格隔開。 輸出描述 請輸出第二個整數的值。 示例 1 輸入:1 2 3 輸出:2 解題思路 方案一 使用 3 個整形變數依次存儲輸入的 3 個整數,然後將第二個整形變數的數據輸出。 具體代碼如下: ...
  • 是否有小伙伴在使用tab的時候想進行滑動切換Tab? 並且有滑動左出左進,右出右進的效果 ,本文將講解怎麼在Blazor中去通過滑動切換Tab 本文中的UI組件使用的是MASA Blazor,您也可以是其他的UI框架,這個並不影響實際的運行效果,本文案例是相容PC和Android的,演示效果是and ...
  • eunomia-bpf 0.3.0 發佈:只需編寫內核態代碼,輕鬆構建、打包、發佈完整的 eBPF 應用 eunomia-bpf 簡介 eBPF 源於 BPF,本質上是處於內核中的一個高效與靈活的虛擬機組件,以一種安全的方式在許多內核 hook 點執行位元組碼,開發者可基於 eBPF 開發性能分析工具 ...
  • 寫在前面 在開發的過程中,大多數人都需要對代碼進行測試。目前對於c/c++項目,可以採用google的gtest框架,除此之外在github上搜索之後可以發現很多其他類似功能的項目。但把別人的輪子直接拿來用,終究比不過自己造一個同樣功能的輪子更有成就感。作為“linux環境編程”系列文章的第一篇,本 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...