Oracle introduced a pivot tables in version 11g. If you want to use it there is still some limitation but as least it gave us a nice tool to display Active Session History information in various way. Please find one of possible example below - database CPU utilization for 5 minutes period with waits split into percent across wait classes
col "Administrative" format 999.99
col "Application" format 999.99
col "Cluster" format 999.99
col "Commit" format 999.99
col "Concurrency" format 999.99
col "Configuration" format 999.99
col "Network" format 999.99
col "Other" format 999.99
col "SYSTEM I/O" format 999.99
col "USER I/O" format 999.99
col "CPU" format 999.99
col Scheduler format 999.99
col Queueing format 999.99
col AAS format 999.99
select to_char(to_date(ts*300,'SSSSS'),'HH24:MI') sample_time, AAS,
nvl("'ON CPU'",0) "CPU",
nvl("'Scheduler'",0) Scheduler ,
nvl("'User I/O'",0) "USER I/O" ,
nvl("'System I/O'",0) "SYSTEM I/O" ,
nvl("'Concurrency'",0) Concurrency ,
nvl("'Application'",0) Application ,
nvl("'Commit'",0) Commit,
nvl("'Configuration'",0) Configuration,
nvl("'Administrative'",0) Administrative ,
nvl("'Network'",0) Network ,
nvl("'Queueing'",0) Queueing ,
nvl("'Cluster'",0) "CLUSTER",
nvl("'Other'",0) Other
from (
select trunc(to_char(sample_time, 'SSSSS')/300) ts,
decode(session_state,'WAITING',wait_class,'ON CPU') wait_class,
count(*) cnt,
sum(count(*)) over (partition by trunc(to_char(sample_time, 'SSSSS')/300)) sum,
sum(count(*)) over (partition by trunc(to_char(sample_time, 'SSSSS')/300))/300 AAS
from v$active_session_history
group by trunc(to_char(sample_time, 'SSSSS')/300),
decode(session_state,'WAITING',wait_class,'ON CPU')
order by 1
)
pivot (
sum(round(cnt/sum*100,2))
for (wait_class) in
('Administrative','Application','Cluster','Commit','Concurrency',
'Configuration','Network','Other','Queueing','Scheduler','System I/O',
'User I/O','ON CPU'
)
) order by 1;
SAMPLE_TIME AAS CPU SCHEDULER USER I/O SYSTEM I/O CONCURRENCY APPLICATION COMMIT CONFIGURATION ADMINISTRATIVE NETWORK QUEUEING CLUSTER OTHER
---------------- ------- ------- --------- -------- ---------- ----------- ----------- ------- ------------- -------------- ------- -------- ------- -------
2011-05-06 14:58 3.38 6.40 .00 38.42 .49 .00 .00 .00 .00 54.68 .00 .00 .00 .00
2011-05-06 14:59 3.67 4.55 .00 44.09 .00 .00 .00 .00 .00 51.36 .00 .00 .00 .00
2011-05-06 15:00 2.98 5.59 .00 29.05 .56 .00 .00 .00 .00 64.80 .00 .00 .00 .00
2011-05-06 15:01 2.53 7.89 .00 16.45 .00 .00 .00 .00 .00 75.66 .00 .00 .00 .00
2011-05-06 15:02 2.62 7.64 .00 21.66 .00 .00 .00 .00 .00 70.70 .00 .00 .00 .00
2011-05-06 15:03 2.32 9.35 .00 11.51 .00 .00 .00 .00 .00 79.14 .00 .00 .00 .00
2011-05-06 15:04 2.37 6.34 .00 30.99 .70 .00 .00 .00 .00 61.97 .00 .00 .00 .00
2011-05-06 15:05 2.93 10.80 .00 6.25 .00 .00 .00 .00 .00 82.95 .00 .00 .00 .00
2011-05-06 15:06 3.28 12.69 .00 14.72 1.02 .00 .00 .00 .00 71.07 .00 .00 .00 .51
2011-05-06 15:07 4.57 10.58 .00 8.39 .00 .00 .00 .00 .00 81.02 .00 .00 .00 .00
2011-05-06 15:08 4.72 5.30 .00 13.78 .00 .00 .00 .00 .00 80.92 .00 .00 .00 .00
2011-05-06 15:09 4.80 6.25 .00 15.28 .00 .00 .00 .00 .00 78.47 .00 .00 .00 .00
2011-05-06 15:10 4.42 8.30 .00 7.92 .00 .00 .00 .00 .00 83.77 .00 .00 .00 .00
2011-05-06 15:11 4.65 6.45 .00 12.19 .00 .00 .00 .00 .00 81.36 .00 .00 .00 .00
2011-05-06 15:12 4.48 7.06 .00 9.67 .00 .00 .00 .00 .00 83.27 .00 .00 .00 .00
2011-05-06 15:13 4.58 9.09 .00 13.09 .00 .00 .00 .00 .00 77.82 .00 .00 .00 .00
2011-05-06 15:14 4.40 10.61 .00 7.20 .00 .00 .00 .00 .00 82.20 .00 .00 .00 .00
2011-05-06 15:15 4.00 7.92 .00 .00 .00 .00 .00 .00 .00 92.08 .00 .00 .00 .00
2011-05-06 15:16 4.33 8.46 .00 6.15 .00 .00 .00 .00 .00 85.00 .38 .00 .00 .00
2011-05-06 15:17 4.60 5.07 .00 9.42 .00 .00 .00 .00 .00 85.51 .00 .00 .00 .00
2011-05-06 15:18 4.80 4.86 .00 15.97 .00 .00 .00 .00 .00 79.17 .00 .00 .00 .00
2011-05-06 15:19 4.27 5.08 .00 5.08 .00 .00 .00 .00 .00 89.84 .00 .00 .00 .00
2011-05-06 15:20 4.00 2.92 .00 .00 .00 .00 .00 .00 .00 97.08 .00 .00 .00 .00
2011-05-06 15:21 4.37 3.05 .00 8.02 .00 .00 .00 .00 .00 88.93 .00 .00 .00 .00
2011-05-06 15:22 7.23 17.05 .00 32.03 .00 .00 .00 .00 .00 50.92 .00 .00 .00 .00
2011-05-06 15:23 .55 6.06 .00 3.03 .00 .00 .00 .00 .00 90.91 .00 .00 .00 .00
That query can be easily change by adding filtering condition for sessions or sample time. Unfortunately list of values for pivot table can't be generated dynamically without XML output so it is required to hard coded number of wait class into this code.
regards,
Marcin
