Script:10g中不用EM显示Active Session Count by Wait Class

<11g中AWR新快照视图>一文中我介绍了作为新特性加入11g的一些动态性能视图,另外也提及了通过一个SQL脚本就可以不打开EM界面而显示ASH的按等待类型(Wait Class)累计的Active Session Count,实际在EM中图形化的效果是这样的:

 

 

在11g 中可以使用如下SQL从命令行获得实例每分钟的AAS信息(注意没有对后台进程做过滤,所以是Foreground + Background 的模式):

 

set echo off;
set verify off;
alter session set nls_date_format='HH24:MI';
select *
  from (select nvl(wait_class, 'CPU') activity,
               trunc(sample_time, 'MI') time
          from v$active_session_history) v pivot(count(*) for activity in('CPU' as
                                                                          "CPU",
                                                                          'Concurrency' as
                                                                          "Concurrency",
                                                                          'System I/O' as
                                                                          "System I/O",
                                                                          'User I/O' as
                                                                          "User I/O",
                                                                          'Administrative' as
                                                                          "Administrative",
                                                                          'Configuration' as
                                                                          "Configuration",
                                                                          'Application' as
                                                                          "Application",
                                                                          'Network' as
                                                                          "Network",
                                                                          'Commit' as
                                                                          "Commit",
                                                                          'Scheduler' as
                                                                          "Scheduler",
                                                                          'Cluster' as
                                                                          "Cluster",
                                                                          'Queueing' as
                                                                          "Queueing",
                                                                          'Other' as
                                                                          "Other"))
 where time > sysdate - interval '&last_min' minute
 order by time;

 

因为以上这段SQL脚本使用了11g 才引入的pivot函数,所以在10g 中是无法运行的。Google 了以下似乎没有可用的10g版本(大约化了一个小时),寻思着还是自己给改以下吧,结果5分钟搞定。。

现在共享出来给经常不能使用EM的乙方同学(Foreground only 和 Foreground + background 2种模式都有):

 

 

使用方法如下:

 

Trackbacks

  1. […] Script:利用外部表实现SQL查询Oracle告警日志Alert.log Script: 收集RAC DRM 诊断信息 Script:10g中不用EM显示Active Session Count by Wait Class Script:数据库最近的性能度量 Script:收集数据库中用户的角色和表空间等信息 […]

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号