[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

--//驗證我的判斷.