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