Saturday, May 7, 2011

Pivot tables and Active Session History in Oracle 11g

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