[20200217]使用snapper探究DBMS_SHARED_POOL.MARKHOT标识热对象的等待事件.txt

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

[20200217]使用snapper探究DBMS_SHARED_POOL.MARKHOT标识热对象的等待事件.txt --//snapper.sql是Tanel Poder探究会话相关等待事件的脚本，参考链接： http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper --//上个星期使用我写的wait.sql不是很好，仅仅看一个点,无法完整的分析。 --//反正现在上班没什么事情，重复上个星期的测试，探究DBMS_SHARED_POOL.MARKHOT标识热对象的等待事件。 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 SYS@book> @ hide _kgl_hot_object_copies NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ---------------------- ----------------------------------- ------------- ------------- ------------ ----- --------- _kgl_hot_object_copies Number of copies for the hot object TRUE 0 0 FALSE FALSE $ alias zzdate alias zzdate='date +'\''trunc(sysdate)+%H/24+%M/1440+%S/86400 == %Y/%m/%d %T'\''' --//rename job_times to job_times_20200217; create table job_times (sid number, sessionid number,time_ela number,method varchar2(20)); --//建立脚本bb.txt: $ cat bb.txt SELECT owner ,name ,hash_value ,full_hash_value ,namespace ,child_latch ,property hot_flag ,executions ,invalidations FROM v$db_object_cache WHERE name = 'DBMS_APPLICATION_INFO' AND owner = 'SYS' order by executions desc ; --//建立测试脚本m3.txt set verify off --//host sleep $(echo &&3/50 | bc -l ) 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; begin for i in 1 .. &&1 loop execute immediate 'begin dbms_application_info.set_client_info(''mutex'');end;'; 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; quit 2.测试1： --//不使用DBMS_SHARED_POOL.MARKHOT的情况 $ sqlplus / as sysdba SYS@book> @ tpt/snapper all 436 1 "select 1,sid from v$session where username='SCOTT' and program like 'sqlplus%'" $ zzdate;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 1e6 p=50 {} >/dev/null;zzdate trunc(sysdate)+10/24+31/1440+30/86400 == 2020/02/17 10:31:30 trunc(sysdate)+10/24+38/1440+30/86400 == 2020/02/17 10:38:30 SYS@book> @ tpt/snapper all 436 1 "select 1,sid from gv$session where username='SCOTT' and program like 'sqlplus%'" Sampling SID select 1,sid from gv$session where username='SCOTT' and program like 'sqlplus%' with interval 436 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! :) --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//这么没有信息，取样太长，还是别的原因，或者会话太多不允许... ---------------------------------------------------------------------------------------------------- Active% | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS ---------------------------------------------------------------------------------------------------- 2086% | 1 | 11c4s0a35j48v | 0 | library cache: mutex X | Concurrency 1196% | 1 | | | library cache: mutex X | Concurrency 932% | 1 | 11c4s0a35j48v | 0 | ON CPU | ON CPU 310% | 1 | 93sbqnyzaas4t | 0 | ON CPU | ON CPU 211% | 1 | | | ON CPU | ON CPU 23% | 1 | | | cursor: pin S | Concurrency 19% | 1 | 93sbqnyzaas4t | 0 | cursor: pin S | Concurrency 5% | 1 | 11c4s0a35j48v | 0 | cursor: pin S | Concurrency 3% | 1 | 11c4s0a35j48v | | ON CPU | ON CPU 1% | 1 | 3s8yd6xsr6fu6 | 0 | library cache: mutex X | Concurrency -- End of ASH snap 1, end=2020-02-17 10:38:45, seconds=436, samples_taken=428 PL/SQL procedure successfully completed. SYS@book> set verify off SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 username='SCOTT' trunc(sysdate)+10/24+31/1440+30/86400 trunc(sysdate)+10/24+38/1440+30/86400 -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This SECONDS AAS WAIT_CHAIN ------ ---------- ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 40% 8368 19.9 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 30% 6323 15.1 -> SCOTT:(sqlplus) ON CPU 19% 3883 9.2 -> SCOTT:(sqlplus) library cache: mutex X 5% 1041 2.5 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 4% 924 2.2 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 1% 150 .4 -> SCOTT:(sqlplus) cursor: pin S 0% 56 .1 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 0% 38 .1 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 0% 25 .1 -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) ON CPU 0% 12 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) ON CPU 0% 11 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) cursor: pin S 0% 8 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) ON CPU 0% 7 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 0% 3 0 -> SCOTT:(sqlplus) latch free -> SCOTT:(sqlplus) ON CPU 0% 2 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) 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 -> SCOTT:(sqlplus) ON CPU 16 rows selected. --//主要是library cache: mutex X等待事件，并且形成一个链条。 3.测试2: --//使用DBMS_SHARED_POOL.MARKHOT的情况,首先使用dbms_shared_pool.markhot设置，注意必须sys用户执行： SYS@book> @ bb.txt OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ ---------------------------------------- ---------- -------------------------------- ---------------- ----------- ------------ ---------- ------------- SYS DBMS_APPLICATION_INFO 539807965 eab253aef59cd250bd8b8a13202cd0dd BODY 53469 52816131 0 SYS DBMS_APPLICATION_INFO 2876716960 5a81de0b29b19e757e67708dab7737a0 TABLE/PROCEDURE 79776 0 0 SYS@book> exec dbms_shared_pool.markhot('SYS','DBMS_APPLICATION_INFO',1); PL/SQL procedure successfully completed. SYS@book> exec dbms_shared_pool.markhot('SYS','DBMS_APPLICATION_INFO',2); PL/SQL procedure successfully completed. SYS@book> @ tpt/snapper ash 120 1 "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1" $ zzdate;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 1e6 markhot_p=50 {} >/dev/null;zzdate trunc(sysdate)+10/24+55/1440+18/86400 == 2020/02/17 10:55:18 trunc(sysdate)+10/24+57/1440+16/86400 == 2020/02/17 10:57:16 SYS@book> @ tpt/snapper ash 120 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 120 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 ---------------------------------------------------------------------------------------------------- 2311% | 1 | 11c4s0a35j48v | 0 | ON CPU | ON CPU 909% | 1 | 93sbqnyzaas4t | 0 | ON CPU | ON CPU 412% | 1 | 11c4s0a35j48v | 0 | library cache: mutex X | Concurrency 397% | 1 | | | ON CPU | ON CPU 246% | 1 | | | library cache: mutex X | Concurrency 214% | 1 | | | cursor: pin S | Concurrency 150% | 1 | 93sbqnyzaas4t | 0 | cursor: pin S | Concurrency 86% | 1 | 11c4s0a35j48v | 0 | cursor: pin S | Concurrency 26% | 1 | 11c4s0a35j48v | 0 | latch free | Other 8% | 1 | 11c4s0a35j48v | | ON CPU | ON CPU -- End of ASH snap 1, end=2020-02-17 10:57:18, seconds=120, samples_taken=119 PL/SQL procedure successfully completed. SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 username='SCOTT' trunc(sysdate)+10/24+55/1440+18/86400 trunc(sysdate)+10/24+57/1440+16/86400 -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This SECONDS AAS WAIT_CHAIN ------ ---------- ---------- ------------------------------------------------------------------------------------ 78% 4430 37.5 -> SCOTT:(sqlplus) ON CPU 7% 401 3.4 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 7% 398 3.4 -> SCOTT:(sqlplus) library cache: mutex X 6% 333 2.8 -> SCOTT:(sqlplus) cursor: pin S 2% 117 1 -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) ON CPU 0% 24 .2 -> SCOTT:(sqlplus) latch free -> SCOTT:(sqlplus) ON CPU 0% 9 .1 -> SCOTT:(sqlplus) latch free 0% 1 0 -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) cursor: pin S 0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 9 rows selected. 4.对比: 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 11445 572258 p=50 50 41783 2089136 --//明显标识热对象后，library cache: mutex X 等待事件大大减少,快了大约4倍. --//标识热对象后，依旧存在library cache: mutex X等待事件，但是因为分散开来，形成一个链条明显变小。 --//另外看等待事件cursor: pin S，标识热对象后占 333 秒，并且SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) ON CPU占117秒。 --//而没有标识前占 150 秒，这是因为减少library cache: mutex X了等待,大量重复的sql语句 --//begin dbms_application_info.set_client_info(''mutex'');end; 而导致在cursor: pin S上时间增加。 --//如果修改 begin dbms_application_info.set_client_info(''mutex'');end; 为 begin /*+ &&3 */ dbms_application_info.set_client_info(''mutex'');end; --//这样cursor: pin S会相应减少。 4.测试3: --//修改测试脚本m3.txt，加入注解看看。 set verify off --//host sleep $(echo &&3/50 | bc -l ) 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; begin for i in 1 .. &&1 loop execute immediate 'begin /*+ &&3 */ dbms_application_info.set_client_info(''mutex'');end;'; 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; quit --//使用DBMS_SHARED_POOL.MARKHOT的情况： SYS@book> @ tpt/snapper ash 120 1 "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1" $ zzdate;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 1e6 markhotx_p=50 {} >/dev/null;zzdate trunc(sysdate)+11/24+14/1440+52/86400 == 2020/02/17 11:14:52 trunc(sysdate)+11/24+16/1440+49/86400 == 2020/02/17 11:16:49 SYS@book> @ tpt/snapper ash 120 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 120 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 ---------------------------------------------------------------------------------------------------- 2335% | 1 | 11c4s0a35j48v | 0 | ON CPU | ON CPU 895% | 1 | 93sbqnyzaas4t | 0 | ON CPU | ON CPU 451% | 1 | | | ON CPU | ON CPU 376% | 1 | 11c4s0a35j48v | 0 | library cache: mutex X | Concurrency 227% | 1 | | | library cache: mutex X | Concurrency 214% | 1 | | | cursor: pin S | Concurrency 166% | 1 | 93sbqnyzaas4t | 0 | cursor: pin S | Concurrency 65% | 1 | 11c4s0a35j48v | 0 | cursor: pin S | Concurrency 13% | 1 | 11c4s0a35j48v | 0 | latch free | Other 6% | 1 | 11c4s0a35j48v | | ON CPU | ON CPU -- End of ASH snap 1, end=2020-02-17 11:16:51, seconds=120, samples_taken=119 PL/SQL procedure successfully completed. SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 username='SCOTT' trunc(sysdate)+11/24+14/1440+52/86400 trunc(sysdate)+11/24+16/1440+49/86400 -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This SECONDS AAS WAIT_CHAIN ------ ---------- ---------- ------------------------------------------------------------------------------------------------------------------- 78% 4473 38.2 -> SCOTT:(sqlplus) ON CPU 7% 407 3.5 -> SCOTT:(sqlplus) library cache: mutex X 6% 338 2.9 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 5% 262 2.2 -> SCOTT:(sqlplus) cursor: pin S 3% 171 1.5 -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) ON CPU 0% 28 .2 -> SCOTT:(sqlplus) latch free -> SCOTT:(sqlplus) ON CPU 0% 14 .1 -> SCOTT:(sqlplus) latch free 0% 2 0 -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) library cache: mutex X 0% 2 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X 0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) ON CPU 0% 1 0 -> SCOTT:(sqlplus) cursor: pin S -> SCOTT:(sqlplus) cursor: pin S 0% 1 0 -> SCOTT:(sqlplus) library cache: mutex X -> SCOTT:(sqlplus) cursor: pin S 12 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) -------------------- ---------- ---------------------- ------------- markhotx_p=50 50 11398 569916 markhot_p=50 50 11445 572258 p=50 50 41783 2089136 --//仅仅快一点点，不明显。 --//cursor: pin S 占 262，明显较少。

编辑推荐：

返回列表

[20200217]使用snapper探究DBMS_SHARED_POOL.MARKHOT标识热对象的等待事件.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推