[20200212]使用DBMS_SHARED_POOL.MARKHOT与视图v$open_cursor.txt
[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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2675362/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261105]测试21c SQL Translation Framework.txt
- [20261106]ANSI DATE and TIMESTAMP SQL syntax.txt
- [20261106]光标跟踪事件11.txt
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
