ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20230226]探究v$session.SQL_EXEC_ID在共享池(windows).txt

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

原创 Oracle 作者：lfree 时间：2023-02-27 20:58:44 0 删除 编辑

[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重新计数,在这样的情况下使用它判断执行次数可能存在错误.

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2937257/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20230225]12c Real-time materialized view 实时物化视图的应用.txt
下一篇： [20230227]firefox被2345篡改.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7969436

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593