[20220216]為什麼出現這樣的情況.txt
[20220216]為什麼出現這樣的情況.txt
--//春節放假期間,看來以前一個帖子,很奇怪的發現以前寫的連結:
--//[20191213]共享池繫結變數的值在哪裡.txt=>http://blog.itpub.net/267265/viewspace-2668705/
--//裡面查詢子游標堆0時,發現多了一個chunk,按照以前測試這裡僅僅顯示一個chunk,為什麼呢?
--//我重複測試看看.
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t as select rownum id ,lpad('x',100,'x') name ,lpad('1',1) flag from dual connect by level<=1e5;
--//注意不要分析表.
2.測試:
--//session 1:
SCOTT@book> variable v_name varchar2(100)
SCOTT@book> exec :v_name :='987654321'
PL/SQL procedure successfully completed.
select * from t where name = :v_name;
select * from t where name = :v_name;
select * from t where name = :v_name;
select * from t where name = :v_name;
select * from t where name = :v_name;
select * from t where name = :v_name;
SCOTT@book> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ------------------- -----------
1906624619 2tftcf9su9k3b 0 51307 1601196873 2022-02-16 09:08:06 16777220
--//執行多次,獲取sql_id=2tftcf9su9k3b.
SYS@book> @sharepool/shp4 2tftcf9su9k3b 0
KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000000084607898 select * from t where name = :v_name 1 0 0 00000000828F51A8 0000000081EADE40 4528 12144 3093 19765 19765 1906624619 2tftcf9su9k3b 0
parent handle address 0000000084607898 select * from t where name = :v_name 1 0 0 00000000828B2658 00 4736 0 0 4736 4736 1906624619 2tftcf9su9k3b 65535
--//注意看KGLOBHS0=4528.
--//子游標堆0:
SYS@book> @sharepool/shp3 00000000828F51A8
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('00000000828F51A8')
no rows selected
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('00000000828F51A8')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFD25347D40 6085 1 1 1 KGLH0^71a4c86b 0000000082732E00 4096 recr 4095 00000000828F51A8
--//僅僅1個chunk.
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('00000000828F51A8', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFD25340E50 5772 1 1 1 KGLDA 00000000828F5140 240 freeabl 0 00
--//子游標堆6:
SYS@book> @sharepool/shp3 0000000081EADE40
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('0000000081EADE40')
no rows selected
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('0000000081EADE40')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFD25340560 5085 1 1 1 SQLA^71a4c86b 0000000082D82968 4096 freeabl 0 0000000081EADE40
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
00007FFD25340508 5086 1 1 1 SQLA^71a4c86b 0000000082D81968 4096 freeabl 0 0000000081EADE40
00007FFD253370E0 6112 1 1 1 SQLA^71a4c86b 0000000082730E00 4096 recr 4095 0000000081EADE40
--//3個chunk.
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000081EADE40', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFD25315CB8 7379 1 1 1 KGLH0^71a4c86b 0000000081EAD6D0 4096 recr 4095 00000000828B2658
--//子游標堆0下僅僅1個chunk,為什麼我前面的測試出現兩個chunk.我看了執行計劃,發現note部分出現如下:
Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//難道是我沒有分析表的原因嗎?
--//分析表後.
SCOTT@book> @ gts t
Gather Table Statistics for table t...
PL/SQL procedure successfully completed.
--//注意:no_invalidate = false,理論講當收集完統計資訊後，收集物件的cursor會立即失效(新的執行計劃，新的子游標)
SCOTT@book> select * from t where name = :v_name;
no rows selected
SYS@book> @sharepool/shp4 2tftcf9su9k3b 0
KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000000084607898 select * from t where name = :v_name 1 0 1 00000000828F51A8 0000000081EADE40 8600 8088 3093 19781 19781 1906624619 2tftcf9su9k3b 0
parent handle address 0000000084607898 select * from t where name = :v_name 1 0 1 00000000828B2658 00 4736 0 0 4736 4736 1906624619 2tftcf9su9k3b 65535
--//你可以發現分析表後並沒有出現新的子游標,但是KGLOBHS0=8600.注意看子游標堆0,堆6的描述符地址還是00000000828F51A8,0000000081EADE40
--//子游標堆0:
SYS@book> @sharepool/shp3 00000000828F51A8
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('00000000828F51A8')
no rows selected
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('00000000828F51A8')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFD253402F8 6578 1 1 1 KGLH0^71a4c86b 0000000082D82968 4096 freeabl 0 00000000828F51A8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
00007FFD253274A8 8277 1 1 1 KGLH0^71a4c86b 0000000082732E00 4096 recr 4095 00000000828F51A8
--//變成了2個chunk.
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('00000000828F51A8', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFD2531C978 7754 1 1 1 KGLDA 00000000828F5140 240 freeabl 0 00
--//子游標堆6:
SYS@book> @sharepool/shp3 0000000081EADE40
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('0000000081EADE40')
no rows selected
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('0000000081EADE40')
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFD2533CA80 6681 1 1 1 SQLA^71a4c86b 0000000082D81968 4096 freeabl 0 0000000081EADE40
00007FFD25324880 8437 1 1 1 SQLA^71a4c86b 0000000082730E00 4096 recr 4095 0000000081EADE40
--//2個chunk.比原來少了1個chunk.
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000081EADE40', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FFD253C9050 10392 1 1 1 KGLH0^71a4c86b 0000000081EAD6D0 4096 recr 4095 00000000828B2658
--//仔細看下劃線的地址KSMCHPTR=0000000082D82968,對比前面可以發現其中1個chunk是從堆6下移動到堆0.(注使用+++++標識)
--//至於為什麼出現這樣的情況我不知道.
--//附上相關測試指令碼:
$ 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;
$ cat sharepool/shp3.sql
prompt
prompt select a.* from x$ksmsp a where a.ksmchptr=hextoraw('&1');
prompt
--select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchptr=hextoraw('&1');
select a.* from x$ksmsp a where a.ksmchptr=hextoraw('&1');
prompt
prompt select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1');
prompt
--select a.*,dump(a.KSMCHCOM,16) c60 from x$ksmsp a where a.ksmchpar=hextoraw('&&1');
select a.* from x$ksmsp a where a.ksmchpar=hextoraw('&&1');
--select * from x$ksmsp where ksmchptr in (
--SELECT x
-- FROM (SELECT a.ksmchptr, lag (a.ksmchptr, 1) OVER (ORDER BY a.ksmchptr) x
-- FROM x$ksmsp a )
-- WHERE '&1' between x and ksmchptr);
-- oracle do not support raw compare
--SELECT * FROM x$ksmsp WHERE '&1' between KSMCHPTR and TO_CHAR(TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ,'0xxxxxxxxxxxxxxx');
prompt
prompt SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1;
prompt
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1;
$ cat tpt/gts.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
prompt Gather Table Statistics for table &1....
exec dbms_stats.gather_table_stats(null, upper('&1'), null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false);
--//注意:no_invalidate = false.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2856378/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 為什麼分散式限流會出現不均衡的情況？分散式
- 爬蟲代理為什麼會出現超時的情況？爬蟲
- 什麼情況下會出現css阻塞？CSS
- 什麼情況下會出現js阻塞？JS
- fiddler 抓手機包出現這種情況為什麼檢視不了請求資料
- [20211130]為什麼出現負數.txt
- 伺服器過載會出現什麼情況伺服器
- JavaScript 加減危機 —— 為什麼會出現這樣的結果？JavaScript
- 什麼情況！華為開源JDK！JDK
- 為什麼mysql會經常出現主從同步不一致的情況MySql主從同步
- [20211203]為什麼出現負數3.txt
- 我在下載模組的時候下不下來出現這種情況是什麼意思？
- 為什麼做軟體類專案，會出現人多，事少，工作量大的情況？
- 為什麼靜態程式碼安全檢測工具會有誤報、漏報的情況出現?
- 在配置DDOS高防服務後為什麼會出現有些源站IP被封的情況？
- [20210301]為什麼邏輯讀這麼多.txt
- [20200326]為什麼選擇這個索引.txt索引
- 邦芒面試：為什麼你的面試沒回音？逃不過這8種情況面試
- [20240827]分析為什麼出現library cache lock等待事件2.txt事件
- [20240828]分析為什麼出現library cache lock等待事件5.txt事件
- [20200219]strace跟蹤設定ENABLE=BROKEN的情況(網路的情況).txt
- [20231024]NULL值在索引的情況.txtNull索引
- [20230501]為什麼沒有顯示輸出.txt
- 20200909]為什麼執行計劃不是出現hash join semi.txt
- 如果列表元素li的兄弟元素為div，會產生什麼情況？
- Nginx代理websocket為什麼要這樣做？NginxWeb
- 美國末日的劇情為什麼有這麼強的代入感？
- [20180410]為什麼2個邏輯讀不一樣.txt
- 什麼情況下你能接受 996996
- GreatSQL 中 Insert 慢是什麼情況？SQL
- ******這樣的情況，可以找到一份Go開發的工作嗎？Go
- 成都現在的情況
- 寫出幾個初始化CSS的樣式，並解釋說明為什麼要這樣寫CSS
- 什麼情況下進行效能測試
- oracle組合索引什麼情況下生效?Oracle索引
- 什麼情況下不能使用最壞情況評估演算法的複雜度？演算法複雜度
- Python學完之後從業情況怎麼樣？Python
- Latex請問這種情況怎麼解決