[20230227]探究v$session.SQL_EXEC_ID在共享池(补充).txt

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

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

编辑推荐：

返回列表

[20230227]探究v$session.SQL_EXEC_ID在共享池(补充).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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推