Script:10g中显示Active Session Count by Wait Class
摘自:
http://www.askmaclean.com/archives/script-10g-show-active-session-count-wait-class.html
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;
10G中不能使用pivot函数,这个思路值得参考。