[20200212]使用DBMS_SHARED_POOL.MARKHOT与视图v$open_cursor.txt

来源：这里教程网时间：2026-03-03 15:00:09 作者：

[20200212]使用DBMS_SHARED_POOL.MARKHOT与视图v$open_cursor.txt --//中午午休时，有点不理解使用DBMS_SHARED_POOL.MARKHOT标识热sql语句时，测试反而更慢。 --//我再想一般参数open_cursors是设置，执行3次以上，以后sql语句的执行都是软软解析，会不会v$open_cursor里面记录还是原来的 --//sql_id呢？这样软软解析就失效了,这样必须测试确定在使用DBMS_SHARED_POOL.MARKHOT标识热sql语句时，生成的sql_id会与原来不 --//同，测试视图v$open_cursor记录那个sql_id. 1.环境: SYS@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 $ cat bb1.txt SELECT name ,hash_value ,full_hash_value ,namespace ,child_latch ,property hot_flag ,executions ,invalidations FROM v$db_object_cache WHERE name = 'select * from dept where deptno=10'; 2.测试： --//session 1: SCOTT@book> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 44 279 2359 DEDICATED 2360 27 102 alter system kill session '44,279' immediate; SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//sql_id='4xamnunv51w9j' --//session 2: SYS@book> select * from v$open_cursor where sid=44 and SQL_ID='4xamnunv51w9j' 2 @ prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DDC5CD8 HASH_VALUE : 911274289 SQL_ID : 4xamnunv51w9j SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : OPEN PL/SQL procedure successfully completed. --//首先使用DBMS_SHARED_POOL.MARKHOT标记。 --//session 2: SYS@book> @ bb1.txt NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ---------------------------------------- ---------- -------------------------------- ---------------- ----------- ---------- ---------- ------------- select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 0 1 0 select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 61745 1 0 SYS@book> exec dbms_shared_pool.markhot( hash=>'1431c45dbddbb9e74eaa74d53650f131', namespace=>0, global=>true); PL/SQL procedure successfully completed. SYS@book> @ bb1.txt NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ---------------------------------------- ---------- -------------------------------- ---------------- ----------- ---------- ---------- ------------- select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 0 HOT 1 0 select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 61745 HOT 1 0 --//session 1: SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> select * from v$open_cursor where sid=44 and SQL_ID='4xamnunv51w9j'; no rows selected --//原来记录的sql_id已经找不到相应记录在v$open_cursor中，这样看来记录新的sql_id. SYS@book> @ bb1.txt NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ---------------------------------------- ---------- -------------------------------- ---------------- ----------- ---------- ---------- ------------- select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 0 HOT 1 0 select * from dept where deptno=10 911274289 1431c45dbddbb9e74eaa74d53650f131 SQL AREA 61745 HOT 1 0 select * from dept where deptno=10 3263113834 f41d12549d8b4d0ebb9d55c9c27f2a6a SQL AREA 0 HOTCOPY45 1 0 select * from dept where deptno=10 3263113834 f41d12549d8b4d0ebb9d55c9c27f2a6a SQL AREA 76394 HOTCOPY45 1 0 --//注:当前_kgl_hot_object_copies=101.所以出现HOTCOPY45. mod(sid,101)+1=mod(44,101)+1=45. --//session 1: SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> select * from v$open_cursor where sid=44 and SQL_ID='4xamnunv51w9j'; no rows selected --//噢不能在这样查询，改写如下. SYS@book> select * from v$open_cursor where sid=44 and sql_text like 'select * from dept where deptno=10' 2 @ prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : OPEN PL/SQL procedure successfully completed. --//记录的sql_id=br7apt717yama.不再是原来的sql_id=4xamnunv51w9j. --//session 1: SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//sesson 2: SYS@book> select * from v$open_cursor where sid=44 and sql_text like 'select * from dept where deptno=10' 2 @ prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : OPEN ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED PL/SQL procedure successfully completed. --//噢，出现奇特的现象，在视图v$open_cursor 当前sid=44记录了3条。 SYS@book> select distinct * from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10' 2 @prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : OPEN ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED PL/SQL procedure successfully completed. --//差异在CURSOR_TYPE，查询CURSOR_TYPE有2个值DICTIONARY LOOKUP CURSOR CACHED，OPEN。 --//session 1: SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> select count(*) from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10'; COUNT(*) ---------- 5 --//记录了5条。 SYS@book> select distinct * from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10' 2 @ prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : OPEN ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED PL/SQL procedure successfully completed. --//ADDRESS记录父游标的地址。可以验证卡看看。 SYS@book> @ sharepool/shp4 br7apt717yama 0 TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 子游标句柄地址 000000007C0D1150 000000007DCA5958 select * from dept where deptno=10 1 0 0 000000007E258468 000000007BE93F00 4528 12144 3069 19741 19741 3263113834 br7apt717yama 0 父游标句柄地址 000000007DCA5958 000000007DCA5958 select * from dept where deptno=10 1 0 0 000000007C688068 00 4720 0 0 4720 4720 3263113834 br7apt717yama 65535 --//父游标句柄地址=000000007DCA5958. SYS@book> select CURSOR_TYPE from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10'; CURSOR_TYPE ---------------------------------------------------------------- OPEN DICTIONARY LOOKUP CURSOR CACHED DICTIONARY LOOKUP CURSOR CACHED DICTIONARY LOOKUP CURSOR CACHED DICTIONARY LOOKUP CURSOR CACHED --//仅仅CURSOR_TYPE不同。 --//session 1: SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> select count(*) from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10'; COUNT(*) ---------- 7 --//增加了2条。 --//session 1: SCOTT@book> select sysdate from dual ; SYSDATE ------------------- 2020-02-12 16:26:44 SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> select count(*) from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10'; COUNT(*) ---------- 8 --//很明显在使用BMS_SHARED_POOL.MARKHOT标识热sql语句时存在bug或者问题.也许正是这样的情况导致出现library cache: mutex X。 --//我的测试很特别，没有退出会话执行dbms_shared_pool.markhot标识热sql语句，建立新会话看看。 3.继续测试： --//session 1:退出再登录。 SCOTT@book> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 44 283 2500 DEDICATED 2501 27 104 alter system kill session '44,283' immediate; --//sid=44,但是serial#发生变化。 --//session 2: SYS@book> select * from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10' 2 @ prxx PL/SQL procedure successfully completed. --//session 1: SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> select * from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10' 2 @ prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : OPEN PL/SQL procedure successfully completed. --//session 1: SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SYS@book> select * from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10' 2 @prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : OPEN ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED PL/SQL procedure successfully completed. --//session 1: SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : OPEN ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED PL/SQL procedure successfully completed. --//依旧出现问题。感觉这个是使用DBMS_SHARED_POOL.MARKHOT标识热sql语句的bug。 4.取消DBMS_SHARED_POOL.MARKHOT标识热sql语句看看。 --//session 2: SYS@book> exec dbms_shared_pool.unmarkhot( hash=>'1431c45dbddbb9e74eaa74d53650f131', namespace=>0, global=>true); PL/SQL procedure successfully completed. --//session 1: SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> select * from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10' 2 @ prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DDC5CD8 HASH_VALUE : 911274289 SQL_ID : 4xamnunv51w9j ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : OPEN ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DCA5958 HASH_VALUE : 3263113834 SQL_ID : br7apt717yama SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED PL/SQL procedure successfully completed. --//其它记录依旧存在。但是增加1条sql_id=4xamnunv51w9j,注意看下划线。 SYS@book> select * from v$open_cursor where sid=44 and sql_text = 'select * from dept where deptno=10' and sql_id='4xamnunv51w9j' 2 @ prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DDC5CD8 HASH_VALUE : 911274289 SQL_ID : 4xamnunv51w9j SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : OPEN PL/SQL procedure successfully completed. --//session 1: SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK --//session 2: SYS@book> @ prxx ============================== SADDR : 000000008638EC10 SID : 44 USER_NAME : SCOTT ADDRESS : 000000007DDC5CD8 HASH_VALUE : 911274289 SQL_ID : 4xamnunv51w9j SQL_TEXT : select * from dept where deptno=10 LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : SESSION CURSOR CACHED PL/SQL procedure successfully completed. --//注意看CURSOR_TYPE='SESSION CURSOR CACHED'.而且仅仅1条记录。 --//而前面的都是停留在CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED',并且多次出现.感觉是bug的可能性更大. --//也许正是这个问题导致设置后反而更慢。总结： 1.使用DBMS_SHARED_POOL.MARKHOT标识热sql语句,v$open_cursor记录的是变化的sql_id. 2.我个人认为这个是bug,不建议使用DBMS_SHARED_POOL.MARKHOT标识热sql语句. --//附上shp4.sql脚本: column N0_6_16 format 99999999 SELECT DECODE (kglhdadr, kglhdpar, '父游标句柄地址', '子游标句柄地址') text, kglhdadr, kglhdpar, substr(kglnaobj,1,40) c40, KGLHDLMD, KGLHDPMD, kglhdivc, kglobhd0, kglobhd6, kglobhs0,kglobhs6,kglobt16, kglobhs0+kglobhs6+kglobt16 N0_6_16, kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20, kglnahsh, kglobt03 , kglobt09 FROM x$kglob WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;