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