使用ASH信息,发现高CPUsession
使用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) WAITING 2 0%
- ? @dbjacsb1 (TNS V1-V3) 8zay3dhyxpk1s ON CPU 1 0%
- 30 rows selected
通过如上信息,你可以判断哪些module对CPU使用贡献度最大,你肯定发现PLSQL DEVELOPER执行的sql:1qbw09atztvzn
那么我们再加入session_id来分组,哪些session在使用PLSQL DEVELOPER来执行该SQL.
- MODULE SESSION_ID SQL_ID SESSION_STATE COUNT(*) PERCENT
- ---------------------------- ------------- ------------- ---------- -----------
- PL/SQL Developer 336 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 460 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 566 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 1053 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 1661 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 1774 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 1980 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 2156 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 2435 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 4482 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 4400 8tg77v2fjyvus ON CPU 236 2%
- PL/SQL Developer 4374 1qbw09atztvzn ON CPU 235 2%
- PL/SQL Developer 4289 1qbw09atztvzn ON CPU 235 2%
- PL/SQL Developer 3067 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 2938 9r57k363xqdvg ON CPU 236 2%
- PL/SQL Developer 2893 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 2601 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 2486 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 5699 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 5674 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 5570 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 5405 1qbw09atztvzn ON CPU 235 2%
- PL/SQL Developer 5280 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 2434 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 2057 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 1871 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 1709 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 1505 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 1437 1qbw09atztvzn ON CPU 236 2%
- PL/SQL Developer 1059 8tg77v2fjyvus ON CPU 236 2%
- 30 rows selected
这么多PLSQL DEVELOPER在执行基本同一sql。接下来,再看看改SQL:
- select object_name,
- owner object_owner,
- status,
- object_type,
- created,
- last_ddl_t object_type = :"SYS_B_0" and object_name not like :"SYS_B_1"
- order by decode(ow owner, object_name;
这么一个简单的sql,即使很多session在执行也不至于让CPU持续90%以上,而且这个sql是通过4A平台来执行,一个维护的sql,经常执行的。至于如何导致cpu如此高,因为这些连接都僵死了,但却占用的主机资源。很easy,干掉它们。
评论暂时关闭