[20201117]使用DBMS_SHARED_POOL.MARKHOT与sql语句5.txt

来源：这里教程网时间：2026-03-03 16:16:39 作者：

[20201117]使用DBMS_SHARED_POOL.MARKHOT与sql语句5.txt --//前几天我看了链接：https://blog.pythian.com/reducing-contention-on-hot-cursor-objects-cursor-pin-s/ --//里面提到使用DBMS_SHARED_POOL.MARKHOT标识sql语句，减少Cursor: Pin S的情况,不过对方不同的地方是在函数中使用。 --//我以前的测试不在函数里面反而更慢，不建议使用，出现大量的"library cache: mutex X". --//正好里面有例子，我直接拿来测试看看,首先看看标识热sql语句后，看看v$open_cursor的情况。 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.建立测试环境: create table job_times (sid number, sessionid number,time_ela number,method varchar2(20)); create table code_table (code_name char(1), low_value number, high_value number); declare letters char(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; v_num number := 1; begin for i in 1..26 LOOP insert into code_table values (substr(letters,i,1), i*v_num, i*(v_num+1000)); v_num := v_num + 1001; end loop; commit; end; / create or replace function fx_num (v_name varchar) return number is v_low number; v_high number; begin select low_value, high_value into v_low, v_high from code_table where code_name=v_name; return(DBMS_RANDOM.value(low => v_low, high => v_high)); end; / --//建立测试脚本m3.txt: $ cat m3.txt set verify off insert into job_times values ( sys_context ('userenv', 'sid') ,sys_context ('userenv', 'sessionid'),dbms_utility.get_time ,'&&2') ; commit ; declare v_id number; v_d date; m_rowid varchar2(20); m_data varchar2(32); begin --// m_rowid := '&3'; for i in 1 .. &&1 loop select /*+ &3 */ fx_num(substr(to_char(sysdate,'MON'),1,1)) into v_id from dual; end loop; end ; / update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and sessionid=sys_context ('userenv', 'sessionid') and method='&&2'; commit; $ cat cc.txt SELECT owner ,name ,hash_value ,full_hash_value ,namespace ,child_latch ,property hot_flag ,executions ,invalidations FROM v$db_object_cache WHERE name = 'SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 ' order by executions desc ; --//注意/:B1后面有1个空格。 3.测试: SCOTT@book> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 295 695 56167 DEDICATED 56168 21 250 alter system kill session '295,695' immediate; SCOTT@book> @m3.txt 10 xx yy 1 row created. Commit complete. PL/SQL procedure successfully completed. 4 rows updated. Commit complete. --//确定sql_id='7tr4jwnamtmsr'. SCOTT@book> select * from v$open_cursor where sid=295 and SQL_ID='7tr4jwnamtmsr' 2 @ prxx ============================== SADDR : 0000000085EC7D20 SID : 295 USER_NAME : SCOTT ADDRESS : 000000007BE3D218 HASH_VALUE : 356306711 SQL_ID : 7tr4jwnamtmsr SQL_TEXT : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : PL/SQL CURSOR CACHED PL/SQL procedure successfully completed. SCOTT@book> @ cc.txt OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ ------------------------------------------------------------------ ---------- -------------------------------- -------------------- ----------- -------------------- ---------- ------------- SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA 53015 31 0 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA 0 31 0 SCOTT@book> @prxx ============================== OWNER : NAME : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 HASH_VALUE : 356306711 FULL_HASH_VALUE : eb4cdceda1c495cd7cdc91e5153ccf17 NAMESPACE : SQL AREA CHILD_LATCH : 53015 HOT_FLAG : EXECUTIONS : 31 INVALIDATIONS : 0 ============================== OWNER : NAME : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 HASH_VALUE : 356306711 FULL_HASH_VALUE : eb4cdceda1c495cd7cdc91e5153ccf17 NAMESPACE : SQL AREA CHILD_LATCH : 0 HOT_FLAG : EXECUTIONS : 31 INVALIDATIONS : 0 PL/SQL procedure successfully completed. --//确定FULL_HASH_VALUE=eb4cdceda1c495cd7cdc91e5153ccf17. 4.标识热对象： --//以sys用户执行： SYS@book> exec dbms_shared_pool.markhot(hash => 'eb4cdceda1c495cd7cdc91e5153ccf17', namespace => 0, global => true); PL/SQL procedure successfully completed. --//退出重新登录： SCOTT@book> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 295 697 56266 DEDICATED 56267 21 251 alter system kill session '295,697' immediate; SCOTT@book> @m3.txt 10 xx yy 1 row created. Commit complete. PL/SQL procedure successfully completed. 1 row updated. Commit complete. SCOTT@book> @ cc.txt OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ ------------------------------------------------------------------ ---------- -------------------------------- -------------------- ----------- -------------------- ---------- ------------- SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA 0 HOT 31 0 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA 53015 HOT 31 0 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 939937426 432f8fa611a9e7d4d7a686f838064e92 SQL AREA 0 HOTCOPY8 10 0 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 939937426 432f8fa611a9e7d4d7a686f838064e92 SQL AREA 20114 HOTCOPY8 10 0 --//HOT_FLAG=HOTCOPY8.参考http://blog.itpub.net/267265/viewspace-2675377/的总结： --//mod(sid,cpu_count/2)+1 ,我的测试环境cpu_count=24. $ echo 295%12+1 | bc 8 SCOTT@book> select * from v$open_cursor where sid=295 and sql_text='SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 '; no rows selected --//奇怪没有找到对应记录。 SCOTT@book> select * from v$open_cursor where sid=295 and sql_text like 'SELECT LOW_VALUE, HIGH_VALUE%' 2 @ prxx ============================== SADDR : 0000000085EC7D20 SID : 295 USER_NAME : SCOTT ADDRESS : 000000007CB1C9D0 HASH_VALUE : 939937426 SQL_ID : dg9n6z0w0cmnk SQL_TEXT : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME LAST_SQL_ACTIVE_TIME : SQL_EXEC_ID : CURSOR_TYPE : PL/SQL CURSOR CACHED PL/SQL procedure successfully completed. --//噢，原来sql_text仅仅保存开头60个字符长度。 --//可以发现很明显，没有出现在链接看到的情况 http://blog.itpub.net/267265/viewspace-2675362/ --//也就是如果在函数或者PL/SQL包里面的sql语句通过DBMS_SHARED_POOL.MARKHOT是可以提高性能的。 --//限于偏于另外写一篇blog测试。