[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.環境:
[email protected]> @ 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

[email protected]> create table tx as select object_id deptno from all_objects;
Table created.

[email protected]> @ 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:
[email protected]> 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


[email protected]> @ 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占用空間.

[email protected]> @ 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. )

[email protected]> @ 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:
[email protected]> 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

[email protected]> @ 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占用空間.

[email protected]> @ 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,明顯比前面少 )

[email protected]> @ 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,執行計劃提示無效.

--//對比兩者的執行計劃:
[email protected]> 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

[email protected]> @ 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 / [email protected]$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.

[email protected]> 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

[email protected]> @ 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 / [email protected]$1
   3 - SEL$2
   5 - SEL$2 / [email protected]$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提示的情況:
[email protected]> 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

[email protected]> @ 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

[email protected]> @ 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
  • 一:背景 1. 講故事 年前遇到了好幾例托管堆被損壞的案例,有些運氣好一些,從被破壞的托管堆記憶體現場能觀測出大概是什麼問題,但更多的情況下是無法做出準確判斷的,原因就在於生成的dump是第二現場,借用之前文章的一張圖,大家可以理解一下。 為了幫助更多受此問題困擾的朋友,這篇來整理一下如何 快狠準 的 ...
  • 前言 .NET6 開始,.NET Croe API 項目取消了 Startup.cs 文件,在 Program.cs 文件的 Main 函數中完成服務的註冊和中間件管道的管理。但當我們項目引入更多包的時候,Program.cs 文件也會看起來很臃腫。 而且,我們不只會有一個後端項目,為了方便快速創建 ...
  • 目錄 背景 get 與 post 的區別 所有介面都用 post 請求? 背景 最近在逛知乎的時候發現一個有趣的問題:公司規定所有介面都用 post 請求,這是為什麼? 看到這個問題的時候其實我也挺有感觸的,因為我也曾經這樣問過我自己。在上上一家公司的時候接到一個項目是從零開始搭建一個微服務,當時就 ...
  • *以下內容為本人的學習筆記,如需要轉載,請聲明原文鏈接 微信公眾號「englyf」https://mp.weixin.qq.com/s/2GFLTstDC7w6u3fTJxflNA 本文大概 1685 個字,閱讀需花 6 分鐘內容不多, 但也花了一些精力如要交流, 歡迎關註我然後評論區留言 謝謝你的 ...
  • 在新版本的pandas中,上述代碼會引起警告,建議改成SQLAlchemy connectable(engine/connection),後續代碼將引入這種升級的連接方式。 ...
  • 幾乎所有的高級編程語言都有自己的垃圾回收機制,開發者不需要關註記憶體的申請與釋放,Python 也不例外。Python 官方團隊的文章 https://devguide.python.org/internals/garbage-collector 詳細介紹了 Python 中的垃圾回收演算法,本文是這篇 ...
  • 如果您想查找高於或低於平均值的數字,可以不必計算該平均值,就能查看更高或更低的值。通過Java應用程式,可以自動突出顯示這些數字。除了快速突出顯示高於或低於平均值的值外,您還可以查看高於或低於的值的個數。現在讓我們看看如何在 Java應用程式中實現此操作。 引入jar包 導入方法1: 手動引入。將  ...
  • 第一種方式:使用{} firstDict = {"name": "wang yuan wai ", "age" : 25} 說明:{}為創建一個空的字典對象 第二種方式:使用fromkeys()方法 second_dict = dict.fromkeys(("name", "age")) #valu ...
  • 在golang中可以使用a := b這種方式將b賦值給a,只有當b能進行深拷貝時a與b才不會互相影響,否則就需要進行更為複雜的深拷貝。 下麵就是Go賦值操作的一個說明: Go語言中所有賦值操作都是值傳遞,如果結構中不含指針,則直接賦值就是深度拷貝;如果結構中含有指針(包括自定義指針,以及切片,map ...
  • 本文結合京東監控埋點場景,對解決樣板代碼的技術選型方案進行分析,給出最終解決方案後,結合理論和實踐進一步展開。通過關註文中的技術分析過程和技術場景,讀者可收穫一種樣板代碼思想過程和解決思路,並對Java編譯器底層有初步瞭解。 ...