[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
-
lfree
2020-02-17 16:25:39 -
Oracle
- 原创