[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
    ),