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