[20230227]探究v$session.SQL_EXEC_ID在共享池(補充).txt
[20230227]探究v$session.SQL_EXEC_ID在共享池(補充).txt
--//http://blog.tanelpoder.com/2011/10/24/what-the-heck-is-the-sql-execution-id-sql_exec_id/
--//上個星期測試了SQL_EXEC_ID儲存在父游標地址的偏移108處,今天補充一些測試.
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
2.測試:
--//session 1:
SCOTT@book> SELECT sql_exec_id,TO_CHAR (sql_exec_id, 'FM0xxxxxxxxxxxxxxx') sql_exec_id16,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SQL_EXEC_ID16 SYSDATE
----------- ----------------- -------------------
16777216 0000000001000000 2023-02-27 09:05:47
...
SCOTT@book> SELECT sql_exec_id,TO_CHAR (sql_exec_id, 'FM0xxxxxxxxxxxxxxx') sql_exec_id16,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SQL_EXEC_ID16 SYSDATE
----------- ----------------- -------------------
16777219 0000000001000003 2023-02-27 09:08:17
SCOTT@book> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
634459932 635v7a0kx25sw 0 71452 2422122865 25d1171c 2023-02-27 09:05:50 16777218
--//session 2:
SYS@book> @ sharepool/shp4x 635v7a0kx25sw 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007F30DCA0 000000007CD59D58 SELECT sql_exec_id,TO_CHAR (sql_exec_id, 1 0 0 000000007D2F4908 000000007D1EE7C8 4528 24312 3160 32000 32000 634459932 635v7a0kx25sw 0
parent handle address 000000007CD59D58 000000007CD59D58 SELECT sql_exec_id,TO_CHAR (sql_exec_id, 1 0 0 000000007CFF61B8 00 4816 0 0 4816 4816 634459932 635v7a0kx25sw 65535
SYS@book> @ fcha 000000007CD59D58
Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007CD59D58 resides...
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000000007CD59D28 2 1 KGLHD 656 recr 80 00
SYS@book> @ calc 0x000000007CD59D28 + 108
DEC HEX
----------------------------------- --------------------
2094374292.000000 7CD59D94
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x7CD59D94 4
[07CD59D94, 07CD59D98) = 00000004
--//修改為00FFFFFE.
SYS@book> oradebug poke 0x7CD59D94 4 0xfffffe
BEFORE: [07CD59D94, 07CD59D98) = 00000004
AFTER: [07CD59D94, 07CD59D98) = 00FFFFFE
--//session 1:
SCOTT@book> SELECT sql_exec_id,TO_CHAR (sql_exec_id, 'FM0xxxxxxxxxxxxxxx') sql_exec_id16,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SQL_EXEC_ID16 SYSDATE
----------- ----------------- -------------------
33554430 0000000001fffffe 2023-02-27 09:10:11
--//session 2:
SYS@book> oradebug peek 0x7CD59D94 4
[07CD59D94, 07CD59D98) = 00FFFFFF
--//再次執行呢?
--//session 1:
SCOTT@book> SELECT sql_exec_id,TO_CHAR (sql_exec_id, 'FM0xxxxxxxxxxxxxxx') sql_exec_id16,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SQL_EXEC_ID16 SYSDATE
----------- ----------------- -------------------
33554431 0000000001ffffff 2023-02-27 09:11:13
--//session 2:
SYS@book> oradebug peek 0x7CD59D94 4
[07CD59D94, 07CD59D98) = 01000000
--//session 1:
SCOTT@book> SELECT sql_exec_id,TO_CHAR (sql_exec_id, 'FM0xxxxxxxxxxxxxxx') sql_exec_id16,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SQL_EXEC_ID16 SYSDATE
----------- ----------------- -------------------
16777216 0000000001000000 2023-02-27 09:11:57
SYS@book> oradebug peek 0x7CD59D94 4
[07CD59D94, 07CD59D98) = 01000001
--//可以猜測sql_exec_id大致計算父游標該處值 and 0xffffff,然後 或 01000000獲得的計算結果.
SYS@book> select sql_id,executions from v$sqlarea where sql_id='635v7a0kx25sw';
SQL_ID EXECUTIONS
------------- ----------
635v7a0kx25sw 7
SYS@book> select sql_id,executions from v$sql where sql_id='635v7a0kx25sw';
SQL_ID EXECUTIONS
------------- ----------
635v7a0kx25sw 7
--//實際僅僅執行7次.
3.繼續測試:
--//可以推測子游標偏移108處也應該儲存對於子游標的執行次數.
--//session 2:
SYS@book> @ fcha 000000007F30DCA0
Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007F30DCA0 resides...
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000000007F30DC70 2 1 KGLHD 368 recr 80 00
SYS@book> @ calc 0x000000007F30DC70 + 108
DEC HEX
----------------------------------- --------------------
2133908700.000000 7F30DCDC
SYS@book> oradebug peek 0x7F30DCDC 4
[07F30DCDC, 07F30DCE0) = 00000007
--//session 1:
SCOTT@book> SELECT sql_exec_id,TO_CHAR (sql_exec_id, 'FM0xxxxxxxxxxxxxxx') sql_exec_id16,sysdate FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SQL_EXEC_ID16 SYSDATE
----------- ----------------- -------------------
16777217 0000000001000001 2023-02-27 09:18:21
--//session 2:
SYS@book> oradebug peek 0x7F30DCDC 4
[07F30DCDC, 07F30DCE0) = 00000008
--//驗證我的判斷.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2937262/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20220120]探究v$session.SQL_EXEC_ID在共享池.txtSessionSQL
- [20230226]探究v$session.SQL_EXEC_ID在共享池(windows).txtSessionSQLWindows
- [20240930]關於共享池-表物件在庫快取探究2.txt物件快取
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- 1224關於共享池4SQL記憶體結構父子游標補充SQL記憶體
- 1229關於共享池4-SQL記憶體結構父子游標補充3SQL記憶體
- [20181030]避免表示式在sql語句中(10g)(補充).txtSQL
- [20230227]firefox被2345篡改.txtFirefox
- [20200424]跟蹤特定sql語句以及v$open_cursor檢視(再補充).txtSQL
- [20200422]跟蹤特定sql語句以及v$open_cursor檢視(補充).txtSQL
- [20161012]linux free的補充.txtLinux
- [20160407]bbed修改檔案頭2(補充).txt
- [20180105]oracle臨時表補充.txtOracle
- [20120601]ITL的問題補充.txt
- [20241016]Oracle C functions annotations補充.txtOracleFunction
- Oracle 共享池操作Oracle
- [20170916]sqlplus set array最小2補充.txtSQL
- [20120809]學習物化檢視(補充).txt
- 執行緒池原始碼探究執行緒原始碼
- [20191213]共享池繫結變數的值在哪裡.txt變數
- [20220603]測試quiz night(補充).txtUI
- [20211013]測試遠端監聽補充.txt
- JVM補充篇JVM
- 聯通性補充
- 如何定位SQL語句在共享池裡用到了哪些chunksSQL
- 共享池最佳化思路
- css雜項補充CSS
- redis筆記補充Redis筆記
- Servlet學習補充Servlet
- Oracle 補充日誌Oracle
- UDP聊天程式補充UDP
- lambda(持續補充)
- while迴圈補充While
- step1 補充
- linux命令補充Linux
- [20190211]簡單測試埠是否開啟(補充).txt
- [20211116]plsql_code_type=native補充.txtSQL
- [20211021]windows新建文字檔案帶日期(補充).txtWindows