[20230226]探究v$session.SQL_EXEC_ID在共享池(windows).txt
[20230226]探究v$session.SQL_EXEC_ID在共享池(windows).txt
--//http://blog.tanelpoder.com/2011/10/24/what-the-heck-is-the-sql-execution-id-sql_exec_id/
--//在windows環境驗證看看,增強記憶.
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
2.測試:
--//session 1:
SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
16777216 2023-02-26 19:57:47
SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
16777217 2023-02-26 19:57:47
SCOTT@test01p> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1619099043 9jwq10th82zd3 0 97699 2422122865 60817da3 2023-02-26 19:57:47 16777217
--//16777216 = 0x1000000,共6個0佔24位,從0開始記錄執行次數.前面8位表示那個例項執行該語句.最大256個例項.
--//按照連結的介紹,就是表示例項執行該語句的次數,佔32位,前面佔8位表示例項(最多256個),後面佔24位表示執行次數從0開始記錄,
--//一旦操作超過2^24 = 16777216就溢位了.
--//最大執行計數是 0xffffff = 16777215, 也就是記錄的執行次數最大2^24 = 16777216.
3.我的好奇是這個計數的東西儲存在那裡,如果每條sql語句都有記錄,可以推測應該單獨儲存在父遊標裡面嗎?驗證看看.
--//按照前面的測試,儲存在父遊標地址的偏移108處.
--//執行多次.session 1:
SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
16777221 2023-02-26 20:00:33
--//16777221 = 0x1000005
--//session 2:
SYS@test> @ sharepool/shp4x 9jwq10th82zd3 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000007FF15C80D18 000007FF15C7F4C0 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 0 000007FF12D9B8A0 000007FF15FF57E8 20360 36480 3206 60046 60046 1619099043 9jwq10th82zd3 0
parent handle address 000007FF15C7F4C0 000007FF15C7F4C0 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 0 000007FF15CD59F8 00 4072 0 0 4072 4072 1619099043 9jwq10th82zd3 65535
SYS@test> @ fcha 000007FF15C7F4C0
Find in which heap (UGA, PGA or Shared Pool) the memory address 000007FF15C7F4C0 resides...
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000007FF15C7F490 1 1 KGLHD 816 recr 80 00
SYS@test> @ calc 0x000007FF15C7F490 + 108
DEC HEX
----------------------------------- --------------------
8792163480828.000000 7FF15C7F4FC
--//看看0x000007FF15C7F4FC處的值.
SYS@test> oradebug peek 0x7FF15C7F4FC 4
[7FF15C7F4FC, 7FF15C7F500) = 00000006
--//session 1:
SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
16777222 2023-02-26 20:04:41
--//16777222= 0x1000006
--//session 2:
SYS@test> oradebug peek 0x7FF15C7F4FC 4
[7FF15C7F4FC, 7FF15C7F500) = 00000007
4.繼續驗證:
SYS@test> oradebug poke 0x7FF15C7F4FC 4 0x000000AA
ORA-32521: error parsing ORADEBUG command:
--//windows版本無法使用poke修改記憶體資訊.
--//session 1,再次執行多次.
SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
16777231 2023-02-26 20:10:23
--//16777231 = 0x100000f
--//session 2:
SYS@test> oradebug peek 0x7FF15C7F4FC 4
[7FF15C7F4FC, 7FF15C7F500) = 00000010
--//基本可以驗證sql_exec_id儲存在父遊標地址中.
SYS@test> select executions from v$sqlarea where sql_id='9jwq10th82zd3';
EXECUTIONS
----------
16
5.最後測試重新整理共享池的情況:
--//session 2:
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> @ sharepool/shp4x 9jwq10th82zd3 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000007FF15C80D18 000007FF15C7F4C0 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 1 00 00 0 0 3206 3206 3206 1619099043 9jwq10th82zd3 0
parent handle address 000007FF15C7F4C0 000007FF15C7F4C0 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 1 000007FF15CD59F8 00 4072 0 0 4072 4072 1619099043 9jwq10th82zd3 65535
--//子游標的KGLOBHD0,KGLOBHD6已經清除.
--//session 1:
SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
16777232 2023-02-26 20:14:42
--//父遊標還在,sql_exec_id還在繼續增加.
--//session 2:
SYS@test> oradebug peek 0x7FF15C7F4FC 4
[7FF15C7F4FC, 7FF15C7F500) = 00000011
--//增加+1
--//session 1:
SCOTT@test01p> select sysdate from dual ;
SYSDATE
-------------------
2023-02-26 20:19:15
--//session 2:
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> @ sharepool/shp4x 9jwq10th82zd3 0
no rows selected
--//session 1:
SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
16777216 2023-02-26 20:20:32
--//16777216 = 0x1000000,會到初始值.
6.還可以推測子游標偏移108位置儲存子游標的執行測試.
--//session 1,執行多次:
SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
16777234 2023-02-26 20:22:11
--//16777234= 0x1000012
--//session 2:
SYS@test> @ sharepool/shp4x 9jwq10th82zd3 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000007FF1252E1F0 000007FF11CEEE70 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 0 000007FF1252E030 000007FF11AC0F20 20360 36480 3206 60046 60046 1619099043 9jwq10th82zd3 0
parent handle address 000007FF11CEEE70 000007FF11CEEE70 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 0 000007FF1726F9A0 00 4072 0 0 4072 4072 1619099043 9jwq10th82zd3 65535
SYS@test> @ fcha 000007FF1252E1F0
Find in which heap (UGA, PGA or Shared Pool) the memory address 000007FF1252E1F0 resides...
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000007FF1252E1C0 1 1 KGLHD 560 recr 80 00
SYS@test> @ calc 0x000007FF1252E1C0 + 108
DEC HEX
----------------------------------- --------------------
8792105476652.000000 7FF1252E22C
SYS@test> oradebug peek 0x7FF1252E22C 4
[7FF1252E22C, 7FF1252E230) = 00000013
--//session 1:
SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
16777235 2023-02-26 20:24:12
--//16777235 = 0x1000013
--//session 2:
SYS@test> oradebug peek 0x7FF1252E22C 4
[7FF1252E22C, 7FF1252E230) = 00000014
SYS@test> @ fcha 000007FF11CEEE70
Find in which heap (UGA, PGA or Shared Pool) the memory address 000007FF11CEEE70 resides...
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000007FF11CEEE40 1 1 KGLHD 856 recr 80 00
SYS@test> @ calc 0x000007FF11CEEE40 + 108
DEC HEX
----------------------------------- --------------------
8792096829100.000000 7FF11CEEEAC
SYS@test> oradebug peek 0x7FF11CEEEAC 4
[7FF11CEEEAC, 7FF11CEEEB0) = 00000014
--//session 1:
SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
16777236 2023-02-26 20:26:01
--//session 2:
SYS@test> oradebug peek 0x7FF1252E22C 4
[7FF1252E22C, 7FF1252E230) = 00000015
SYS@test> oradebug peek 0x7FF11CEEEAC 4
[7FF11CEEEAC, 7FF11CEEEB0) = 00000015
--//增加1
6.總結:
--//可以得出結論:sql_exec_id儲存在父遊標中.
--//重新整理共享池可能導致sql_exec_id重新計數,在這樣的情況下使用它判斷執行次數可能存在錯誤.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2937257/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20220120]探究v$session.SQL_EXEC_ID在共享池.txtSessionSQL
- [20230227]探究v$session.SQL_EXEC_ID在共享池(補充).txtSessionSQL
- [20240930]關於共享池-表物件在庫快取探究2.txt物件快取
- [20180813]重新整理共享池與父子游標.txt
- [20191213]共享池繫結變數的值在哪裡.txt變數
- [20191216]共享池繫結變數的值在哪裡2.txt變數
- [20231008]bbed探究lob段.txt
- [20220321]探究oracle sequence.txtOracle
- [20210903]探究mutex的值.txtMutex
- 共享池 shared pool
- 執行緒池原始碼探究執行緒原始碼
- [20191125]探究等待事件的本源.txt事件
- [20191119]探究ipcs命令輸出.txt
- [20220322]探究oracle sequence 2.txtOracle
- [20181005]安裝12C在windows.txtWindows
- [20200620]IMPDP TRANSFORM引數再探究.txtORM
- [20181226]簡單探究cluster table.txt
- [20240529]簡單探究FREE LISTS列表.txt
- [20210915]探究mutex的值 6.txtMutex
- [20210914]探究mutex的值 4.txtMutex
- [20210914]探究mutex的值 5.txtMutex
- [20210916]探究mutex的值 8.txtMutex
- [20210126]探究oracle記憶體分配.txtOracle記憶體
- [20191202]tmux共享回話.txtUX
- commons-pool2 池化技術探究
- [20191126]探究等待事件的本源2.txt事件
- [20191127]探究等待事件的本源4.txt事件
- [20191119]探究ipcs命令輸出2.txt
- [20201203]探究library cache mutex X 3.txtMutex
- oracle固定物件到共享池Oracle物件
- 【SQL】Oracle SQL共享池檢查SQLOracle
- 解決macOS新建txt文件在Windows下不換行問題MacWindows
- [20210126]探究oracle記憶體分配3.txtOracle記憶體
- [20210126]探究oracle記憶體分配4.txtOracle記憶體
- Windows檔案共享LinuxWindowsLinux
- [20190710]windows文字格式.txtWindows
- Windows使用者組（探究失敗Windows
- [20180926]共享池中的NETWORK BUFFER.txt