[20220120]探究v$session.SQL_EXEC_ID在共享池.txt

[20220120]探究v$session.SQL_EXEC_ID在共享池.txt

--//http://blog.tanelpoder.com/2011/10/24/what-the-heck-is-the-sql-execution-id-sql_exec_id/

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,sysdate  FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
   16777216 2023-02-20 11:10:31

SCOTT@book> @ 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-20 11:10:31    16777216

SCOTT@book> SELECT sql_exec_id,sysdate  FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
   16777217 2023-02-20 11:10:56

--//退出會話再次執行:
--//session 1:
SCOTT@book> SELECT sql_exec_id,sysdate  FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
   16777218 2023-02-20 11:11:14

--//16777216 = 0x1000000,共6個0佔24位,從0開始記錄執行次數.前面8位表示那個例項執行該語句.最大256個例項.
--//按照連結的介紹,就是表示例項執行該語句的次數,佔32位,前面佔8位表示例項(最多256個),後面佔24位表示執行次數從0開始記錄,
--//一旦操作超過2^24 = 16777216就溢位了.
--//最大執行計數是 0xffffff = 16777215, 也就是記錄的執行次數最大2^24 = 16777216.

3.我的好奇是這個計數的東西儲存在那裡,如果每條sql語句都有記錄,可以推測應該單獨儲存在父遊標裡面嗎?驗證看看.

--//session 2,清除共享池,注意session 1當前的游標沒有關閉:
SYS@book> @ flush_sql.sql 9jwq10th82zd3 1
PL/SQL procedure successfully completed.

--//session 1:
SCOTT@book> SELECT sql_exec_id,sysdate  FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
   16777219 2023-02-20 11:12:18
--//SQL_EXEC_ID=16777219,計數還是在增加!!註上面執行的會話沒有退出,游標還是開啟的.儲存在父游標某個位置的可能性最大.
--//session 1:
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2023-02-20 11:13:02

--//session 2,再次清除共享池,注意session 1當前的游標已經關閉:
SYS@book> alter system flush shared_pool;
System altered.

SYS@book> @ sharepool/shp4 9jwq10th82zd3
no rows selected

--//session 1:
SCOTT@book> SELECT sql_exec_id,sysdate  FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
   16777216 2023-02-20 11:14:32

--//可以發現一旦重新整理共享池父游標不再了,再次執行計數重新開始,為什麼前面執行flush_sql.sql而計數還是在增加呢?因為前面測試
--//游標出於開啟狀態.
--//session 1,執行多次略.

SCOTT@book> SELECT sql_exec_id,sysdate  FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
   16777224 2023-02-20 11:16:01
--//16777224= 0x1000008

4.繼續測試:
--//session 2:
SYS@book> @ sharepool/shp4x 9jwq10th82zd3
TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address  000000007C23A960 000000007C2F4F60 SELECT sql_exec_id,sysdate  FROM v$sessi          1          0          0 000000007D6F97C8 000000007CEF6308       4544      24312       3102     31958      31958 1619099043 9jwq10th82zd3          0
parent handle address 000000007C2F4F60 000000007C2F4F60 SELECT sql_exec_id,sysdate  FROM v$sessi          1          0          0 000000007D657258 00                     4752          0          0      4752       4752 1619099043 9jwq10th82zd3      65535

SYS@book> @ fcha 000000007C2F4F60
Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007C2F4F60 resides...
Press ENTER to continue, CTRL+C to cancel...

LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 000000007C2F4F30          2          1 KGLHD                   592 recr             80 00
--//開始位置0x000000007C2F4F30.

SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x000000007C2F4F30 592 1
[07C2F4F30, 07C2F5180) = 00000251 80B38F00 7C2F4D10 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00080050 813F3870 00000000 ...

--//session 1:
SCOTT@book> SELECT sql_exec_id,sysdate  FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
   16777225 2023-02-20 11:19:32
--//16777225= 0x1000009

--//session 2:
SYS@book> @ ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_17141_0001.trc

SYS@book> oradebug peek 0x000000007C2F4F30 592 1
[07C2F4F30, 07C2F5180) = 00000251 80B38F00 7C2F4D10 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00080050 813F3870 00000000 ...

$ diff book_ora_17141_0001.trc book_ora_17141.trc
...
< *** 2023-02-20 11:19:40.645
---
> *** 2023-02-20 11:18:45.674
36c37
< 07C2F4F90 00010001 00000002 00000000 0000000A  [................]
                                       ~~~~~~~~~
---
> 07C2F4F90 00010001 00000002 00000000 00000009  [................]
                                       ~~~~~~~~
67c68
...

--//注意看下劃線09->0a,估計在這個位置,驗證看看,不要在生產系統做如下這個測試.
--//0xF9C-0xF30 = 108 = 0x6c.

SYS@book> oradebug peek 0x07C2F4F9C 4
[07C2F4F9C, 07C2F4FA0) = 0000000A

SYS@book> oradebug poke 0x07C2F4F9C 4 0x000000AA
BEFORE: [07C2F4F9C, 07C2F4FA0) = 0000000A
AFTER:  [07C2F4F9C, 07C2F4FA0) = 000000AA

--//session 1:
SCOTT@book> SELECT sql_exec_id,sysdate  FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
   16777386 2023-02-20 11:25:04

--//16777386= 0x10000aa,驗證自己的判斷,看看現在該位置記錄怎麼內容.

SYS@book> oradebug peek 0x07C2F4F9C 4
[07C2F4F9C, 07C2F4FA0) = 000000AB
--//現在記錄的是0x000000AB,基本可以驗證SQL_EXEC_ID值儲存在父遊標中.
--//book_ora_17141.trc
Dump of memory from 0x07C2F4F44 to 0x07C2F5180
07C2F4F40          00000000 00000000 00000000      [............]
07C2F4F50 00000000 00000000 00000000 00080050  [............P...]
07C2F4F60 813F3870 00000000 813F3870 00000000  [p8?.....p8?.....]
07C2F4F70 7CEF5C48 00000000 7C2F50B8 00000000  [H\.|.....P/|....]
07C2F4F80 00010000 10012841 00000001 00000001  [....A(..........]
07C2F4F90 00010001 00000002 00000000 0000000A  [................]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

5.在游標開啟的情況下重新整理共享池看看:
SYS@book> alter system flush shared_pool;
System altered.

SYS@book> @ sharepool/shp4x 9jwq10th82zd3
TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address  000000007C23A960 000000007C2F4F60 SELECT sql_exec_id,sysdate  FROM v$sessi          1          0          1 00               00                        0          0       3102      3102       3102 1619099043 9jwq10th82zd3          0
parent handle address 000000007C2F4F60 000000007C2F4F60 SELECT sql_exec_id,sysdate  FROM v$sessi          1          0          1 000000007D657258 00                     4752          0          0      4752       4752 1619099043 9jwq10th82zd3      65535
--//你可以發現子游標的KGLOBHD0,KGLOBHD6已經清除,而父游標還在!!
--//session 1:
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2023-02-20 11:28:53

--//session 2:
SYS@book> alter system flush shared_pool;
System altered.

SYS@book> @ sharepool/shp4x 9jwq10th82zd3
no rows selected

--//session 1:
SCOTT@book> SELECT sql_exec_id,sysdate  FROM v$session WHERE sid = USERENV('SID');
SQL_EXEC_ID SYSDATE
----------- -------------------
   16777216 2023-02-20 11:29:18
--//回到初始值.

6.總結:
--//可以得出結論:sql_exec_id儲存在父遊標中.
--//重新整理共享池可能導致sql_exec_id重新計數,在這樣的情況下使用它判斷執行次數可能存在錯誤.