日期:2014-05-16  浏览次数:20414 次

使用ASH信息,发现高CPUsession
ASH信息是我们Troubleshooting一个很重要的信息来源,当然,我们也不一定要收集一个ASH报告来分析,
一般从v$active_session_history可以得到想要的信息,如果前面视图里已经不存在,那么可以通过
DBA_HIST_ACTIVE_SESS_HISTORY来获取需要的信息,看个小例子:
昨天某套库的CPU使用一度达到99%,作为维护人员,我们肯定要去关注,查找原因。在主机层面没有发现异常,

那么是数据库主机,问题定位在数据库上。

SELECT * FROM (
  SELECT a.module
      , a.sql_id
      , a.session_state
      , count(*)
      , lpad(round(ratio_to_report(count(*)) over () * 100)||'%',10,' ') percent
    FROM
        dba_hist_active_sess_history a
    WHERE
        a.sample_time BETWEEN to_date('2012:08:01:15:00:00','YY-MM-DD:HH24:MI:SS') 
        AND to_date( '2012:08:01:15:40:00','YY-MM-DD:HH24:MI:SS')
    GROUP BY
        a.module
      , a.sql_id
      , a.session_state
    ORDER BY
        percent DESC
)
WHERE ROWNUM <= 30;
我很喜欢用如上的方式,按照某些条件来分组,来查看session对系统的影响程度,进而来分析:
MODULE                                           SQL_ID        SESSION_STATE   COUNT(*) PERCENT
------------------------------------------------ ------------- ------------- ---------- -------------
PL/SQL Developer                               1qbw09atztvzn ON CPU              7312        59%
PL/SQL Developer                               8tg77v2fjyvus ON CPU               472         4%
JTC db-app-g17-c1b1-srv1 7387@pc-dbdadb17.s    0a3db3m5h72rb ON CPU               220         2%
JTC db-app-g17-c1b1-srv2 7384@pc-dbdadb17.s    0a3db3m5h72rb ON CPU               232         2%
JTC db-app-g17-c1b1-srv3 26446@pc-dbjadb17.    0a3db3m5h72rb ON CPU               197         2%
JTC db-app-g17-c1b1-srv4 26442@pc-dbjadb17.    0a3db3m5h72rb ON CPU               199         2%
PL/SQL Developer                               9r57k363xqdvg ON CPU               236         2%
JTC db-app-g17-c1b2-srv1 7369@pc-dbdadb17.s    0a3db3m5h72rb ON CPU               114         1%
JTC db-app-g17-c2b3-srv4 26404@pc-dbjadb17.    0a3db3m5h72rb ON CPU                67         1%
JTC db-app-g17-c2b4-srv2 7358@pc-dbdadb17.s    0a3db3m5h72rb ON CPU                70         1%
JTC db-app-g17-c3b5-srv1 7372@pc-dbdadb17.s    0a3db3m5h72rb ON CPU                75         1%
JTC db-app-g17-c3b5-srv3 26372@pc-dbjadb17.    0a3db3m5h72rb ON CPU                80         1%
JTC db-app-g17-c4b7-srv4 26417@pc-dbjadb17.    0a3db3m5h72rb ON CPU                63         1%
JTC db-app-g17-c4b7-srv3 26366@pc-dbjadb17.    0a3db3m5h72rb ON CPU                67         1%
JTC db-app-g17-c4b7-srv2 7363@pc-dbdadb17.s    0a3db3m5h72rb ON CPU                65         1%
JTC db-app-g17-c4b7-srv1 7393@pc-dbdadb17.s    0a3db3m5h72rb ON CPU                66         1%
JTC db-app-g17-c3b6-srv1 7352@pc-dbdadb17.s    0a3db3m5h72rb ON CPU                62         1%
JTC db-app-g17-c3b5-srv4 26545@pc-dbjadb17.    0a3db3m5h72rb ON CPU                82         1%
JTC db-app-g17-c3b5-srv2 7362@pc-dbdadb17.s    0a3db3m5h72rb ON CPU                81         1%
JTC db-app-g17-c2b4-srv3 26543@pc-dbjadb17.    0a3db3m5h72rb ON CPU                72         1%
JTC db-app-g17-c2b4-srv1 7381@pc-dbdadb17.s    0a3db3m5h72rb ON CPU                62         1%
JTC db-app-g17-c2b3-srv3 26460@pc-dbjadb17.    0a3db3m5h72rb ON CPU                66         1%
JTC db-app-g17-c1b2-srv2 7373@pc-dbdadb17.s    0a3db3m5h72rb ON CPU               113         1%
JTC db-app-g17-c1b2-srv4 26415@pc-dbjadb17.    0a3db3m5h72rb ON CPU               113         1%
JTC db-app-g17-c1b2-srv3 26523@pc-dbjadb17.    0a3db3m5h72rb ON CPU               119         1%
   ?  @dbdacsb3 (TNS V1-V3)                    farj17af7zpq5 ON CPU                 1         0%
   ?  @dbdasso1 (TNS V1-V3)                    fa292z74w82s5 WAITING                1         0%
   ?  @dbdasso1 (TNS V1-V3)                                    ON CPU               2         0%
   ?  @dbdasso1 (TNS V1-V3)