1. 首页
  2. IT资讯

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

[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/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182928.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code