[20200409]使用ash_wait_chains注意的一个细节.txt

[20200409]使用ash_wait_chains注意的一个细节.txt --//上午使用ash_wait_chains检查生产系统数据库，遇到一个小问题做一个记录： > @ tpt/ash/ash_wait_chains upper(program2)||event2 1=1 trunc(sysdate) trunc(sysdate+1) -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This     SECONDS     AAS WAIT_CHAIN ------ ---------- ------- -------------------------   33%       24518      .3 -> (XXXXXX.EXE) ON CPU   24%       17933      .2 -> (JNNN) ON CPU   22%       15984      .2 -> (WNWP.EXE) ON CPU    2%        1279      .0 -> (ORACLE) ON CPU    1%        1033      .0 -> () ON CPU    ... --//我想看看WNWP.EXE的sql语句. > select * from gv$session where lower(program) like '%wnwp%'; no rows selected --//找了半天也没有找到这样的程序。实际上程序是w3wp.exe.可以通过如下确定： > @ tpt/ash/ash_wait_chains upper(program)||':'||event2 1=1 trunc(sysdate) trunc(sysdate+1) -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com ) %This     SECONDS     AAS WAIT_CHAIN ------ ---------- ------- ------------------------------------   33%       25084      .3 -> XXXXXXX.EXE:ON CPU   24%       17886      .2 -> ORACLE@XXXXXXXX (J001):ON CPU   21%       16158      .2 -> W3WP.EXE:ON CPU   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    1%        1043      .0 -> :ON CPU --//实际上的执行程序是w3wp.exe.执行比较实际上脚本把里面的数字转换成N。可以看出使用program2简化program外，还做了一些处理。 --//主要目的是为了一些合并。可以查看源代码发现： ash AS (SELECT /*+ QB_NAME(ash) LEADING(a) USE_HASH(u) SWAP_JOIN_INPUTS(u) */             a.*           , u.username           , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN               REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')             ELSE                 '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'             END || ' ' program2           , NVL(a.event||CASE WHEN a.event IN ('buffer busy waits', 'gc buffer busy', 'gc buffer busy acquire', 'gc buffer busy release')                               THEN ' ['||(SELECT class FROM bclass WHERE r = a.p3)||']' ELSE null END,'ON CPU')                        || ' ' event2           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex         FROM             gv$active_session_history a           , dba_users u         WHERE             a.user_id = u.user_id (+)         AND sample_time BETWEEN &3 AND &4     ),