[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

),