[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
--//验证我的判断.
--//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
--//验证我的判断.
