[20230226]探究v$session.SQL_EXEC_ID在共享池(windows).txt

来源：这里教程网时间：2026-03-03 18:26:32 作者：

[20230226]探究v$session.SQL_EXEC_ID在共享池(windows).txt --//http://blog.tanelpoder.com/2011/10/24/what-the-heck-is-the-sql-execution-id-sql_exec_id/ --//在windows环境验证看看,增强记忆. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID -------------------- ---------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 2.测试: --//session 1: SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID'); SQL_EXEC_ID SYSDATE ----------- ------------------- 16777216 2023-02-26 19:57:47 SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID'); SQL_EXEC_ID SYSDATE ----------- ------------------- 16777217 2023-02-26 19:57:47 SCOTT@test01p> @ 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-26 19:57:47 16777217 --//16777216 = 0x1000000,共6个0占24位,从0开始记录执行次数.前面8位表示那个实例执行该语句.最大256个实例. --//按照链接的介绍,就是表示实例执行该语句的次数,占32位,前面占8位表示实例(最多256个),后面占24位表示执行次数从0开始记录, --//一旦操作超过2^24 = 16777216就溢出了. --//最大执行计数是 0xffffff = 16777215, 也就是记录的执行次数最大2^24 = 16777216. 3.我的好奇是这个计数的东西保存在那里,如果每条sql语句都有记录,可以推测应该单独保存在父游标里面吗?验证看看. --//按照前面的测试,保存在父游标地址的偏移108处. --//执行多次.session 1: SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID'); SQL_EXEC_ID SYSDATE ----------- ------------------- 16777221 2023-02-26 20:00:33 --//16777221 = 0x1000005 --//session 2: SYS@test> @ sharepool/shp4x 9jwq10th82zd3 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 --------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 000007FF15C80D18 000007FF15C7F4C0 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 0 000007FF12D9B8A0 000007FF15FF57E8 20360 36480 3206 60046 60046 1619099043 9jwq10th82zd3 0 parent handle address 000007FF15C7F4C0 000007FF15C7F4C0 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 0 000007FF15CD59F8 00 4072 0 0 4072 4072 1619099043 9jwq10th82zd3 65535 SYS@test> @ fcha 000007FF15C7F4C0 Find in which heap (UGA, PGA or Shared Pool) the memory address 000007FF15C7F4C0 resides... Press ENTER to continue, CTRL+C to cancel... LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- SGA 000007FF15C7F490 1 1 KGLHD 816 recr 80 00 SYS@test> @ calc 0x000007FF15C7F490 + 108 DEC HEX ----------------------------------- -------------------- 8792163480828.000000 7FF15C7F4FC --//看看0x000007FF15C7F4FC处的值. SYS@test> oradebug peek 0x7FF15C7F4FC 4 [7FF15C7F4FC, 7FF15C7F500) = 00000006 --//session 1: SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID'); SQL_EXEC_ID SYSDATE ----------- ------------------- 16777222 2023-02-26 20:04:41 --//16777222= 0x1000006 --//session 2: SYS@test> oradebug peek 0x7FF15C7F4FC 4 [7FF15C7F4FC, 7FF15C7F500) = 00000007 4.继续验证: SYS@test> oradebug poke 0x7FF15C7F4FC 4 0x000000AA ORA-32521: error parsing ORADEBUG command: --//windows版本无法使用poke修改内存信息. --//session 1,再次执行多次. SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID'); SQL_EXEC_ID SYSDATE ----------- ------------------- 16777231 2023-02-26 20:10:23 --//16777231 = 0x100000f --//session 2: SYS@test> oradebug peek 0x7FF15C7F4FC 4 [7FF15C7F4FC, 7FF15C7F500) = 00000010 --//基本可以验证sql_exec_id保存在父游标地址中. SYS@test> select executions from v$sqlarea where sql_id='9jwq10th82zd3'; EXECUTIONS ---------- 16 5.最后测试刷新共享池的情况: --//session 2: SYS@test> alter system flush shared_pool; System altered. SYS@test> @ sharepool/shp4x 9jwq10th82zd3 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 --------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 000007FF15C80D18 000007FF15C7F4C0 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 1 00 00 0 0 3206 3206 3206 1619099043 9jwq10th82zd3 0 parent handle address 000007FF15C7F4C0 000007FF15C7F4C0 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 1 000007FF15CD59F8 00 4072 0 0 4072 4072 1619099043 9jwq10th82zd3 65535 --//子光标的KGLOBHD0,KGLOBHD6已经清除. --//session 1: SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID'); SQL_EXEC_ID SYSDATE ----------- ------------------- 16777232 2023-02-26 20:14:42 --//父游标还在,sql_exec_id还在继续增加. --//session 2: SYS@test> oradebug peek 0x7FF15C7F4FC 4 [7FF15C7F4FC, 7FF15C7F500) = 00000011 --//增加+1 --//session 1: SCOTT@test01p> select sysdate from dual ; SYSDATE ------------------- 2023-02-26 20:19:15 --//session 2: SYS@test> alter system flush shared_pool; System altered. SYS@test> @ sharepool/shp4x 9jwq10th82zd3 0 no rows selected --//session 1: SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID'); SQL_EXEC_ID SYSDATE ----------- ------------------- 16777216 2023-02-26 20:20:32 --//16777216 = 0x1000000,会到初始值. 6.还可以推测子光标偏移108位置保存子光标的执行测试. --//session 1,执行多次: SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID'); SQL_EXEC_ID SYSDATE ----------- ------------------- 16777234 2023-02-26 20:22:11 --//16777234= 0x1000012 --//session 2: SYS@test> @ sharepool/shp4x 9jwq10th82zd3 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 --------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 000007FF1252E1F0 000007FF11CEEE70 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 0 000007FF1252E030 000007FF11AC0F20 20360 36480 3206 60046 60046 1619099043 9jwq10th82zd3 0 parent handle address 000007FF11CEEE70 000007FF11CEEE70 SELECT sql_exec_id,sysdate FROM v$sessi 1 0 0 000007FF1726F9A0 00 4072 0 0 4072 4072 1619099043 9jwq10th82zd3 65535 SYS@test> @ fcha 000007FF1252E1F0 Find in which heap (UGA, PGA or Shared Pool) the memory address 000007FF1252E1F0 resides... Press ENTER to continue, CTRL+C to cancel... LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- SGA 000007FF1252E1C0 1 1 KGLHD 560 recr 80 00 SYS@test> @ calc 0x000007FF1252E1C0 + 108 DEC HEX ----------------------------------- -------------------- 8792105476652.000000 7FF1252E22C SYS@test> oradebug peek 0x7FF1252E22C 4 [7FF1252E22C, 7FF1252E230) = 00000013 --//session 1: SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID'); SQL_EXEC_ID SYSDATE ----------- ------------------- 16777235 2023-02-26 20:24:12 --//16777235 = 0x1000013 --//session 2: SYS@test> oradebug peek 0x7FF1252E22C 4 [7FF1252E22C, 7FF1252E230) = 00000014 SYS@test> @ fcha 000007FF11CEEE70 Find in which heap (UGA, PGA or Shared Pool) the memory address 000007FF11CEEE70 resides... Press ENTER to continue, CTRL+C to cancel... LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- SGA 000007FF11CEEE40 1 1 KGLHD 856 recr 80 00 SYS@test> @ calc 0x000007FF11CEEE40 + 108 DEC HEX ----------------------------------- -------------------- 8792096829100.000000 7FF11CEEEAC SYS@test> oradebug peek 0x7FF11CEEEAC 4 [7FF11CEEEAC, 7FF11CEEEB0) = 00000014 --//session 1: SCOTT@test01p> SELECT sql_exec_id,sysdate FROM v$session WHERE sid = USERENV('SID'); SQL_EXEC_ID SYSDATE ----------- ------------------- 16777236 2023-02-26 20:26:01 --//session 2: SYS@test> oradebug peek 0x7FF1252E22C 4 [7FF1252E22C, 7FF1252E230) = 00000015 SYS@test> oradebug peek 0x7FF11CEEEAC 4 [7FF11CEEEAC, 7FF11CEEEB0) = 00000015 --//增加1 6.总结: --//可以得出结论:sql_exec_id保存在父游标中. --//刷新共享池可能导致sql_exec_id重新计数,在这样的情况下使用它判断执行次数可能存在错误.

编辑推荐：

返回列表

[20230226]探究v$session.SQL_EXEC_ID在共享池(windows).txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推