OEM(同行),待研究确定;

本文提供了一种在Oracle 10g中不使用EM界面即可显示Active Session Count by Wait Class的方法。通过SQL脚本,可以在命令行环境中获取按等待类型划分的活动会话计数。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

from:http://www.oracledatabase12g.com/archives/script-10g-show-active-session-count-wait-class.html

参考:http://www.oracledatabase12g.com/know-maclean-liu


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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值