[20230220]探究v$session.SQL_EXEC_ID在共享池.txt
[20230220]探究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重新计数,在这样的情况下使用它判断执行次数可能存在错误.
--//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重新计数,在这样的情况下使用它判断执行次数可能存在错误.
