Oracle pivot queries and Statspack mashup.
Today we’d like to share the simple idea of using pivot queries for Oracle workload reporting.
We’ve used the Statspack tables as a data source and queried for total Program Global Area memory utilization, as an example.
He’re is the query used (looks a lot better when not wrapped 
) :
select ps.NAME, to_char(snap_time,’YYYY-MM-DD’),
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘00′ then ps.value else null end)*10/1024/1024/1024)/10 h00,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘01′ then ps.value else null end)*10/1024/1024/1024)/10 h01,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘02′ then ps.value else null end)*10/1024/1024/1024)/10 h02,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘03′ then ps.value else null end)*10/1024/1024/1024)/10 h03,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘04′ then ps.value else null end)*10/1024/1024/1024)/10 h04,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘05′ then ps.value else null end)*10/1024/1024/1024)/10 h05,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘06′ then ps.value else null end)*10/1024/1024/1024)/10 h06,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘07′ then ps.value else null end)*10/1024/1024/1024)/10 h07,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘08′ then ps.value else null end)*10/1024/1024/1024)/10 h08,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘09′ then ps.value else null end)*10/1024/1024/1024)/10 h09,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘10′ then ps.value else null end)*10/1024/1024/1024)/10 h10,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘11′ then ps.value else null end)*10/1024/1024/1024)/10 h11,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘12′ then ps.value else null end)*10/1024/1024/1024)/10 h12,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘13′ then ps.value else null end)*10/1024/1024/1024)/10 h13,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘14′ then ps.value else null end)*10/1024/1024/1024)/10 h14,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘15′ then ps.value else null end)*10/1024/1024/1024)/10 h15,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘16′ then ps.value else null end)*10/1024/1024/1024)/10 h16,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘17′ then ps.value else null end)*10/1024/1024/1024)/10 h17,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘18′ then ps.value else null end)*10/1024/1024/1024)/10 h18,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘19′ then ps.value else null end)*10/1024/1024/1024)/10 h19,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘20′ then ps.value else null end)*10/1024/1024/1024)/10 h20,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘21′ then ps.value else null end)*10/1024/1024/1024)/10 h21,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘22′ then ps.value else null end)*10/1024/1024/1024)/10 h22,
floor(avg(case when to_char(ss.snap_time,’HH24′) = ‘23′ then ps.value else null end)*10/1024/1024/1024)/10 h23
from perfstat.stats$snapshot ss,
perfstat.stats$pgastat ps
where ps.name = ‘total PGA allocated’
and ss.SNAP_ID = ps.SNAP_ID
group by ps.name,to_char(snap_time,’YYYY-MM-DD’);
You can now copy and paste returned results into excel and use conditional formatting (just a proposal).
Result (click to enlarge) :
The report presented above is showing a common workload pattern of bigger memory usage during office hours.
