Active Session History at work
来源:互联网 发布:sai for mac 最新版本 编辑:程序博客网 时间:2024/05/22 02:31
Performance Monitoring: Active Session History at work
Teaching an Oracle Database 10g Performance Tuning course this week, I introduced the 10g New Feature Active Session History (ASH) to the students. That was one major improvement – together with the Automatic Workload Repository (AWR) and the Automatic Database Diagnostic Monitor (ADDM) – of the 10g version. Way better than STATSPACK was before!
Imagine you are a DBA on a production system and get an emergency call like “The Database is dead slow!”. You are supposed to spot the cause as soon as possible. ASH kicks in here: We sample the Wait-Events of active sessions every second into the ASH-Buffer. It is accessed most comfortable with the Enterprise Manager GUI from the Performance Page (Button ASH Report there). Or with little effort from the command line like this:
--------------------------------------------- Top 10 CPU consumers in last 5 minutes-------------------------------------------SQL> select * from(select session_id, session_serial#, count(*)from v$active_session_historywhere session_state= 'ON CPU' and sample_time > sysdate - interval '5' minutegroup by session_id, session_serial#order by count(*) desc)where rownum <= 10;------------------------------------------------ Top 10 waiting sessions in last 5 minutes----------------------------------------------SQL> select * from(select session_id, session_serial#,count(*)from v$active_session_historywhere session_state='WAITING' and sample_time > sysdate - interval '5' minutegroup by session_id, session_serial#order by count(*) desc)where rownum <= 10;
These 2 queries should spot the most incriminating sessions of the last 5 minutes. But who is that and what SQL was running?
------------------------ Who is that SID?----------------------set lines 200col username for a10col osuser for a10col machine for a10col program for a10col resource_consumer_group for a10col client_info for a10SQL> select serial#, username, osuser, machine, program, resource_consumer_group, client_infofrom v$session where sid=&sid;----------------------------- What did that SID do?---------------------------SQL> select distinct sql_id, session_serial# from v$active_session_historywhere sample_time > sysdate - interval '5' minuteand session_id=&sid;-------------------------------------------------- Retrieve the SQL from the Library Cache:------------------------------------------------col sql_text for a80SQL> select sql_text from v$sql where sql_id='&sqlid';
You may spot the cause of the current performance problem in very short time with the shown technique. But beware: You need to purchase the Diagnostic Pack in order to be allowed to use AWR, ADDM and ASH
- Active Session History at work
- Performance Monitoring: Active Session History at work
- Oracle ASH(Active Session History) 说明
- ASH(Active Session History)活动会话历史说明
- ASH(Active Session History)——概述(1)!
- ASH(Active Session History)——分析(2)!
- ASH(Active Session History)——根据SID生产报告(3)!
- Oracle 11g AWR 系列七:Active Session History (ASH) 报告
- Active Session History (ASH) Performed An Emergency Flush Messages In The Alert Log
- C++ At Work 专栏...
- Wasting time at work?
- about work at seven
- Founders at Work
- C++ At Work 专栏...
- Coders at work
- Coders At Work:开始篇
- Coders at Work一些摘录
- 读codes at work有感
- AppFuse占位
- 程序人生
- 如何删除Android系统中的私有应用程序
- 捕获异常方法
- android中调用.net web service
- Active Session History at work
- 使用WRAP工具对ORACLE代码进行加密
- 现在的活动靠谱么?破解淘123活动,实现自动邀请,汽车轻松到手
- C/C++中判断某一文件或目录是否存在
- foreach与for用法的区别
- 判断字符串中是否包含某些子串
- 有点浪费青春的嫌疑
- Efficiently Drawing Multiple Instances of Geometry (Direct3D 9)(英)
- 网站开发人员应该知道的61件事