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

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

[20201117]使用DBMS_SHARED_POOL.MARKHOT与sql语句6.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". --//正好里面有例子，我直接拿来测试看看。 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); create unique index pk_code_table on scott.code_table(code_name); alter table code_table add constraint pk_code_table primary key (code_name); --//作者的测试不建立主键索引，我的测试建立它。 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( v_high-v_low); --//return(DBMS_RANDOM.value(low => v_low, high => v_high)); end; / --//注：我发现作者写的例子不是很好，调用DBMS_RANDOM函数。导致函数本身执行时间有点"长"。 --//建立测试脚本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个空格。 $ cat cd.txt column NAMESPACE format a20 column HOT_FLAG format a20 column name format a66 SELECT owner ,name ,hash_value ,full_hash_value ,namespace ,child_latch ,property hot_flag ,executions ,invalidations FROM v$db_object_cache WHERE name='FX_NUM' order by executions desc ; 3.测试: --//执行： SYS@book> @ tpt/snapper ash 65 1 "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1" Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 65 seconds, taking 1 snapshots... -- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) ---------------------------------------------------------------------------------------------------- Active% | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS ---------------------------------------------------------------------------------------------------- 993% | 1 | | | library cache: mutex X | Concurrency 517% | 1 | 7tr4jwnamtmsr | 0 | ON CPU | ON CPU 255% | 1 | | | ON CPU | ON CPU 42% | 1 | ctyugp3su3k91 | 0 | library cache: mutex X | Concurrency 42% | 1 | 1zvmjk9k3sp2m | 0 | library cache: mutex X | Concurrency 42% | 1 | 158y7v912v8fb | 0 | library cache: mutex X | Concurrency 41% | 1 | 3mbun480b7ccy | 0 | library cache: mutex X | Concurrency 40% | 1 | 4r4k676qa6v2d | 0 | library cache: mutex X | Concurrency 39% | 1 | 3k7xkc2t9fwf6 | 0 | library cache: mutex X | Concurrency 39% | 1 | 8t7czzgnugs5x | 0 | library cache: mutex X | Concurrency -- End of ASH snap 1, end=2020-11-17 15:57:47, seconds=65, samples_taken=99 PL/SQL procedure successfully completed. --//马上打开另外会话执行： $ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 2e5 unmarkhot_p=50 {} >/dev/null ;zzdate trunc(sysdate)+15/24+56/1440+40/86400 == 2020/11/17 15:56:40 trunc(sysdate)+15/24+57/1440+42/86400 == 2020/11/17 15:57:42 SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 module='SQL*Plus' trunc(sysdate)+15/24+56/1440+40/86400 trunc(sysdate)+15/24+57/1440+42/86400 -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This SECONDS AAS WAIT_CHAIN ------ ---------- ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 41% 1249 20.1 -> SCOTT:(sqlplus) ON CPU 32% 984 15.9 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 22% 675 10.9 -> SCOTT:(sqlplus) library cache: mutex X 3% 87 1.4 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 3% 80 1.3 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 0% 2 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 7 rows selected. --//从等待事件看出现大量的library cache: mutex X。实际上在做到这里时就怀疑作者的测试，因为sql语句出现等待集中在cursor: pin S。 --//而测试出现library cache: mutex X，说明主要还是集中在包或者函数上。我估计标识sql语句后效果不会太好。 4.标识热对象SQL语句： SYS@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 37338848 0 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA 0 36943000 0 --//CHILD_LATCH是FULL_HASH_VALUE的后17位 0xcf17 = 53015。 SYS@book> exec dbms_shared_pool.markhot(hash => 'eb4cdceda1c495cd7cdc91e5153ccf17', namespace => 0, global => true); PL/SQL procedure successfully completed. 5.继续测试: SYS@book> @ tpt/snapper ash 65 1 "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1" Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 65 seconds, taking 1 snapshots... -- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) ---------------------------------------------------------------------------------------------------- Active% | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS ---------------------------------------------------------------------------------------------------- 1130% | 1 | | | library cache: mutex X | Concurrency 234% | 1 | | | ON CPU | ON CPU 76% | 1 | dc8x5k16dsk82 | 0 | ON CPU | ON CPU 76% | 1 | 9pyrc45h3tgg9 | 0 | ON CPU | ON CPU 65% | 1 | 0u9hxt3azayv5 | 0 | ON CPU | ON CPU 62% | 1 | arg5wx08suqm0 | 0 | ON CPU | ON CPU 55% | 1 | cqtdnxg9apfza | 0 | ON CPU | ON CPU 49% | 1 | 3k7xkc2t9fwf6 | 0 | library cache: mutex X | Concurrency 46% | 1 | 61m38g7ygpfvz | 0 | ON CPU | ON CPU 44% | 1 | 2ht2a25wq37kg | 0 | library cache: mutex X | Concurrency -- End of ASH snap 1, end=2020-11-17 16:03:21, seconds=65, samples_taken=98 PL/SQL procedure successfully completed. $ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 2e5 markhot_p=50 {} >/dev/null ;zzdate trunc(sysdate)+16/24+02/1440+17/86400 == 2020/11/17 16:02:17 trunc(sysdate)+16/24+03/1440+18/86400 == 2020/11/17 16:03:18 --//从时间上看并没有体现使用DBMS_SHARED_POOL.MARKHOT的好处。 SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 module='SQL*Plus' trunc(sysdate)+16/24+02/1440+17/86400 trunc(sysdate)+16/24+03/1440+18/86400 -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This SECONDS AAS WAIT_CHAIN ------ ---------- ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 43% 1289 21.1 -> SCOTT:(sqlplus) ON CPU 33% 994 16.3 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 19% 570 9.3 -> SCOTT:(sqlplus) library cache: mutex X 2% 57 .9 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 2% 54 .9 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 0% 5 .1 -> SCOTT:(sqlplus) cursor: pin S 0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 0% 1 0 -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) ON CPU 0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) cursor: pin S 0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 11 rows selected. SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ; METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) -------------------- ---------- ---------------------- ------------- markhot_p=50 50 5989 299426 unmarkhot_p=50 50 6118 305892 --//根本无法体会dbms_shared_pool.markhot的好处。 6.标识函数为热对象呢？ SCOTT@book> @ cd.txt OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ ------- ---------- -------------------------------- -------------------- ----------- -------------------- ---------- ------------- SCOTT FX_NUM 3934649448 e946e63d9c1e58dd062f52adea85fc68 TABLE/PROCEDURE 130152 50000000 0 SYS@book> exec dbms_shared_pool.markhot(hash => 'e946e63d9c1e58dd062f52adea85fc68', namespace => 1, global => true); PL/SQL procedure successfully completed. SYS@book> @ tpt/snapper ash 65 1 "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1" Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 65 seconds, taking 1 snapshots... -- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) ---------------------------------------------------------------------------------------------------- Active% | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS ---------------------------------------------------------------------------------------------------- 309% | 1 | | | ON CPU | ON CPU 114% | 1 | | | library cache: mutex X | Concurrency 106% | 1 | 0u9hxt3azayv5 | 0 | ON CPU | ON CPU 101% | 1 | dc8x5k16dsk82 | 0 | ON CPU | ON CPU 97% | 1 | arg5wx08suqm0 | 0 | ON CPU | ON CPU 95% | 1 | cqtdnxg9apfza | 0 | ON CPU | ON CPU 88% | 1 | 9pyrc45h3tgg9 | 0 | ON CPU | ON CPU 64% | 1 | 5bacfy8mwthrj | 0 | ON CPU | ON CPU 54% | 1 | 61m38g7ygpfvz | 0 | ON CPU | ON CPU 36% | 1 | 0srq6bz2rvktx | 0 | ON CPU | ON CPU -- End of ASH snap 1, end=2020-11-17 16:10:06, seconds=65, samples_taken=99 PL/SQL procedure successfully completed. $ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 2e5 markhot_pfs=50 {} >/dev/null ;zzdate trunc(sysdate)+16/24+09/1440+02/86400 == 2020/11/17 16:09:02 trunc(sysdate)+16/24+09/1440+45/86400 == 2020/11/17 16:09:45 SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 module='SQL*Plus' trunc(sysdate)+16/24+09/1440+02/86400 trunc(sysdate)+16/24+09/1440+45/86400 -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This SECONDS AAS WAIT_CHAIN ------ ---------- ---------- ------------------------------------------------------------------------------------- 84% 1767 41.1 -> SCOTT:(sqlplus) ON CPU 11% 234 5.4 -> SCOTT:(sqlplus) library cache: mutex X 4% 77 1.8 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 1% 15 .3 -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) ON CPU 0% 4 .1 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 0% 3 .1 -> SCOTT:(sqlplus) cursor: pin S 6 rows selected. --//依旧存在少量library cache: mutex X。 SCOTT@book> @ cd.txt OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ ------ ---------- -------------------------------- -------------------- ----------- -------------------- ---------- ------------- SCOTT FX_NUM 3934649448 e946e63d9c1e58dd062f52adea85fc68 TABLE/PROCEDURE 130152 HOT 50000000 0 SCOTT FX_NUM 2604013040 acab658d7761882204bcbd0b9b3615f0 TABLE/PROCEDURE 5616 HOTCOPY1 1400000 0 SCOTT FX_NUM 1147571161 5bc0190cffaf491d2cc98bda44668bd9 TABLE/PROCEDURE 35801 HOTCOPY6 1400000 0 SCOTT FX_NUM 2514462761 4046d059eca2403b2f1031c395dfa829 TABLE/PROCEDURE 108585 HOTCOPY11 1400000 0 SCOTT FX_NUM 2255211854 685056ffeb2dead33b91453e866bcd4e TABLE/PROCEDURE 118094 HOTCOPY9 1200000 0 SCOTT FX_NUM 1128717707 5e9cc5f89e3a3b6fa14956904346dd8b TABLE/PROCEDURE 56715 HOTCOPY4 1200000 0 SCOTT FX_NUM 3773218966 44b816a2374a4ecfd1c80ca4e0e6c096 TABLE/PROCEDURE 49302 HOTCOPY7 800000 0 SCOTT FX_NUM 1509772016 5aa610021f96b19db8b56a4459fd4af0 TABLE/PROCEDURE 84720 HOTCOPY2 800000 0 SCOTT FX_NUM 385266949 97923d5d4cfc8cfd6175dca316f6b505 TABLE/PROCEDURE 46341 HOTCOPY3 600000 0 SCOTT FX_NUM 2652599427 ef67af593682efa5dbecd4ee9e1b7483 TABLE/PROCEDURE 95363 HOTCOPY8 400000 0 SCOTT FX_NUM 1752733049 2a5adce93042ee1fd233ef8668789579 TABLE/PROCEDURE 38265 HOTCOPY12 400000 0 SCOTT FX_NUM 2357479237 9b6f379c93c9312cab9a22be8c844745 TABLE/PROCEDURE 18245 HOTCOPY5 400000 0 12 rows selected. --//我并行50个会话，仅仅打开11个hotcopy，依旧存在争用，从EXECUTIONS次数也可以看出来。 SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ; METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) -------------------- ---------- ---------------------- ------------- markhot_pfs=50 50 4190 209483 markhot_p=50 50 5989 299426 unmarkhot_p=50 50 6118 305892 7.总结：从测试可以看出,使用dbms_shared_pool.markhot标识sql语句带来的效果不是很大，主要遇到http://blog.itpub.net/267265/viewspace-2675362/的bug。利益还是在于链接http://blog.itpub.net/267265/viewspace-2675377/的总结，再次转抄一遍(原始链接有1个地方写错)： 1.使用DBMS_SHARED_POOL.MARKHOT标识热sql语句,反而性能更慢。我估计可能遇到bug。参考链接:http://blog.itpub.net/267265/viewspace-2675362/=>[20200212]使用DBMS_SHARED_POOL.MARKHOT与视图v$open_cursor.txt 2.使用DBMS_SHARED_POOL.MARKHOT标识热对象，效果不错，如果频繁调用可以尝试使用。 --//这里原始链接有错。 3.关于FULL_HASH_VALUE的计算： --// sql语句在原来的基础上加入 . mod(sid,cpu_count/2)+1数字的字符串参与运算。 --// 如果设置隐含参数 _kgl_hot_object_copies， sql语句在原来的基础上加入 . mod(sid,_kgl_hot_object_copies)+1数字的字符串。 --// 对象 object_name.owner.HOTCOPYNN中的NN \0X\0\0\0 （其中X标识namespace）。 --//另外 namespace可以查询 select distinct kglhdnsp,kglhdnsd,kglobtyd from x$kglob order by 1; 测试参考链接: http://andreynikolaev.wordpress.com/2011/05/01/divide-and-conquer-the-true-mutex-contention/ https://jonathanlewis.wordpress.com/2017/10/02/markhot/