ITPub博客

首页 > 数据库 > Oracle > [20200217]使用snapper探究DBMS_SHARED_POOL.MARKHOT标识热对象的等待事件.txt

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

原创 Oracle 作者:lfree 时间:2020-02-17 16:25:39 0 删除 编辑

[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,明显较少。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2675910/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2671
  • 访问量
    6429040