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种模式都有):  

REM created by Maclean Liu
REM www.oracledatabase12g.com & www.askmaclean.com
REM released 2011-10-27 Version 1.0

REM Foreground + Background mode

set echo off;
set verify off;
alter session set nls_date_format='HH24:MI';

select time,
       sum(case activity
             when 'CPU' then
              1
             else
              0
           end) CPU,
       sum(case activity
             when 'Concurrency' then
              1
             else
              0
           end) Concurrency,
       sum(case activity
             when 'System I/O' then
              1
             else
              0
           end) "System I/O",
       sum(case activity
             when 'User I/O' then
              1
             else
              0
           end) "User I/O",
       sum(case activity
             when 'Administrative' then
              1
             else
              0
           end) "Administrative",
       sum(case activity
             when 'Configuration' then
              1
             else
              0
           end) "Configuration",
       sum(case activity
             when 'Application' then
              1
             else
              0
           end) "Application",
       sum(case activity
             when 'Network' then
              1
             else
              0
           end) "Network",
       sum(case activity
             when 'Commit' then
              1
             else
              0
           end) "Commit",
       sum(case activity
             when 'Scheduler' then
              1
             else
              0
           end) "Scheduler",
       sum(case activity
             when 'Cluster' then
              1
             else
              0
           end) "Cluster",
       sum(case activity
             when 'Queueing' then
              1
             else
              0
           end) "Queueing",
       sum(case activity
             when 'Other' then
              1
             else
              0
           end) "Other"
  from (select trunc(sample_time, 'MI') time,
               nvl(wait_class, 'CPU') activity
          from v$active_session_history)
 where time > sysdate - interval '&last_min' minute
 group by time
 order by time;

REM Foreground  mode

set echo off;
set verify off;
alter session set nls_date_format='HH24:MI';
select time,
       sum(case activity
             when 'CPU' then
              1
             else
              0
           end) CPU,
       sum(case activity
             when 'Concurrency' then
              1
             else
              0
           end) Concurrency,
       sum(case activity
             when 'System I/O' then
              1
             else
              0
           end) "System I/O",
       sum(case activity
             when 'User I/O' then
              1
             else
              0
           end) "User I/O",
       sum(case activity
             when 'Administrative' then
              1
             else
              0
           end) "Administrative",
       sum(case activity
             when 'Configuration' then
              1
             else
              0
           end) "Configuration",
       sum(case activity
             when 'Application' then
              1
             else
              0
           end) "Application",
       sum(case activity
             when 'Network' then
              1
             else
              0
           end) "Network",
       sum(case activity
             when 'Commit' then
              1
             else
              0
           end) "Commit",
       sum(case activity
             when 'Scheduler' then
              1
             else
              0
           end) "Scheduler",
       sum(case activity
             when 'Cluster' then
              1
             else
              0
           end) "Cluster",
       sum(case activity
             when 'Queueing' then
              1
             else
              0
           end) "Queueing",
       sum(case activity
             when 'Other' then
              1
             else
              0
           end) "Other"
  from (select trunc(sample_time, 'MI') time,
               nvl(wait_class, 'CPU') activity
          from v$active_session_history
         where session_type = 'FOREGROUND')
 where time > sysdate - interval '&last_min' minute
 group by time
 order by time;

  使用方法如下：  

SQL> @ashwc

Session altered.

Enter value for last_min: 10

TIME         CPU CONCURRENCY System I/O   User I/O Administrative Configuration
----- ---------- ----------- ---------- ---------- -------------- -------------
Application    Network     Commit  Scheduler    Cluster   Queueing      Other
----------- ---------- ---------- ---------- ---------- ---------- ----------
20:40          1           0          0          0              0             0
          0          0          0          0          0          0          0