Awr captures a wealth of database performance statistics and metrics. Enterprise manager is usually the preferred tool to interpret and analyze this data. However lot of times i resort to using sql statements to graph and look for trends in the awr data. Below are some of the sql’s that i use (Tested only in 11gr2) to extract this information. Please keep in mind that you need the “Database tuning pack” license to access the dba_hist views.
The full script can be downloaded here.
Awr Snapshot info by DbId, by Day
select d.dbid,d.db_name,to_char(s.begin_interval_time,'DD-MON-RR') begintime,min(s.snap_id) minsnap,max(s.snap_id) maxsnap
from dba_hist_snapshot s,dba_hist_database_instance d where s.instance_number = 1
and s.instance_number = d.instance_number and s.dbid = d.dbid
group by d.dbid,d.db_name,to_char(s.begin_interval_time,'DD-MON-RR') order by 1
/
DBID DB_NA BEGINTIME MINSNAP MAXSNAP
------------------- ----- -------------------- --------- ---------
220594996 QAD 16-NOV-11 5205 5217
220594996 QAD 17-NOV-11 5218 5220
2085202933 RK01 15-MAY-12 3 4
Database version and platform
select distinct version,platform_name from dba_hist_database_instance where dbid=&dbid
/
VERSION PLATFORM_NAME
----------------- ---------------------------
11.2.0.2.0 Linux x86 64-bit
Cpu’s/Sockets/Cores/Load Average
select * from (
select stat_name,value
from dba_hist_osstat
where dbid = &dbid
and instance_number = 1
and snap_id = &esnap
)
pivot (sum(value) for stat_name in ('NUM_CPUS','NUM_CPU_SOCKETS','NUM_CPU_CORES','PHYSICAL_MEMORY_BYTES','LOAD'))
/
'NUM_CPUS' 'NUM_CPU_SOCKETS' 'NUM_CPU_CORES' 'PHYSICAL_MEMORY_BYTES' 'LOAD'
---------- ----------------- --------------- ----------------------- ----------
24 2 12 1.0122E+11 .209960938
O/S Cpu Usage
select
to_char(begintime,'DD-MON-YY HH24:MI:SS') begintime,
to_char(endtime,'DD-MON-YY HH24:MI:SS') endtime,
inst,
snapid,
round((utdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100) utpct,
round((ntdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100) ntpct,
round((stdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100) stpct,
round((iowtdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100) iowtpct,
(100-
(
round((utdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)+
round((ntdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)+
round((stdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)+
round((iowtdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)
)) itpct
from
(
select begintime,endtime,(extract(Minute from endtime-begintime)*60+extract(Second from endtime-begintime)) secs,
snapid,inst,
ut-(nvl(lag(ut) over (partition by inst order by inst,snapid),0)) utdiff,
bt-(nvl(lag(bt) over (partition by inst order by inst,snapid),0)) btdiff,
it-(nvl(lag(it) over (partition by inst order by inst,snapid),0)) itdiff,
st-(nvl(lag(st) over (partition by inst order by inst,snapid),0)) stdiff,
iowt-(nvl(lag(iowt) over (partition by inst order by inst,snapid),0)) iowtdiff,
nt-(nvl(lag(nt) over (partition by inst order by inst,snapid),0)) ntdiff,
vin-(nvl(lag(vin) over (partition by inst order by inst,snapid),0)) vindiff,
vout-(nvl(lag(vout) over (partition by inst order by inst,snapid),0)) voutdiff
from
(
select sn.begin_interval_time begintime,
sn.end_interval_time EndTime,oss.snap_id SnapId,oss.instance_number Inst,
sum(decode(oss.stat_name,'USER_TIME',value,0)) ut,
sum(decode(oss.stat_name,'BUSY_TIME',value,0)) bt,
sum(decode(oss.stat_name,'IDLE_TIME',value,0)) it,
sum(decode(oss.stat_name,'SYS_TIME',value,0)) st,
sum(decode(oss.stat_name,'IOWAIT_TIME',value,0)) iowt,
sum(decode(oss.stat_name,'NICE_TIME',value,0)) nt,
sum(decode(oss.stat_name,'VM_IN_BYTES',value,0)) vin,
sum(decode(oss.stat_name,'VM_OUT_BYTES',value,0)) vout
from dba_hist_osstat oss,dba_hist_snapshot sn
where oss.dbid=&dbid
and oss.dbid = sn.dbid
and oss.instance_number = sn.instance_number
and oss.snap_id = sn.snap_id
and oss.snap_id between &bsnap - 1 and &esnap
and oss.stat_name in (
'USER_TIME',
'BUSY_TIME',
'IDLE_TIME',
'SYS_TIME',
'IOWAIT_TIME',
'NICE_TIME',
'VM_IN_BYTES',
'VM_OUT_BYTES'
)
group by sn.begin_interval_time,sn.end_interval_time,oss.snap_id,oss.instance_number
order by oss.instance_number,oss.snap_id
)
)
where snapid between &bsnap and &esnap
order by inst,snapid
/
BEGINTIME ENDTIME in SNAPID UTPCT NTPCT STPCT IOWTPCT ITPCT
-------------------- -------------------- --- ---------- ------ ------ ------ ------- ------
16-NOV-11 21:00:12 16-NOV-11 21:20:16 1 5209 1.00 .00 .00 .00 99.00
16-NOV-11 21:20:16 16-NOV-11 21:40:12 1 5210 4.00 .00 1.00 .00 95.00
16-NOV-11 21:40:12 16-NOV-11 22:00:03 1 5211 31.00 .00 2.00 .00 67.00
16-NOV-11 22:00:03 16-NOV-11 22:20:05 1 5212 58.00 .00 2.00 .00 40.00
Elapsed Time/DB Time/Concurrent Active Users
select
to_char(begintime,'DD-MON-YY HH24:MI:SS') begintime,
to_char(endtime,'DD-MON-YY HH24:MI:SS') endtime,
inst,
snapid,
round(dbtdiff/(1000000*60),2) dbt,
round(secs/60) mins,
round(dbtdiff/(1000000*60*round(secs/60))) concactive
from
(
select begintime,endtime,(extract(Minute from endtime-begintime)*60+extract(Second from endtime-begintime)) secs,
snapid,inst,
dbt-(nvl(lag(dbt) over (partition by inst order by inst,snapid),0)) dbtdiff
from
(
select sn.begin_interval_time begintime,
sn.end_interval_time EndTime,tm.snap_id SnapId,tm.instance_number Inst,
sum(decode(tm.stat_name,'DB time',value,0)) dbt
from dba_hist_sys_time_model tm,dba_hist_snapshot sn
where tm.dbid=&dbid
and tm.dbid = sn.dbid
and tm.instance_number = sn.instance_number
and tm.snap_id = sn.snap_id
and tm.snap_id between &bsnap - 1 and &esnap
and tm.stat_name in (
'DB time'
)
group by sn.begin_interval_time,sn.end_interval_time,tm.snap_id,tm.instance_number
order by tm.instance_number,tm.snap_id
)
)
where snapid between &bsnap and &esnap
order by inst,snapid
/
BEGINTIME ENDTIME in SNAPID DBT MINS CONCACTIVE
-------------------- -------------------- --- ---------- --------- ------- ----------
16-NOV-11 21:00:12 16-NOV-11 21:20:16 1 5209 1 20 0
16-NOV-11 21:20:16 16-NOV-11 21:40:12 1 5210 319 20 16
16-NOV-11 21:40:12 16-NOV-11 22:00:03 1 5211 657 20 33
16-NOV-11 22:00:03 16-NOV-11 22:20:05 1 5212 972 20 49
16-NOV-11 22:20:05 16-NOV-11 22:40:06 1 5213 457 20 23
16-NOV-11 22:40:06 16-NOV-11 23:00:08 1 5214 736 20 37
16-NOV-11 23:00:08 16-NOV-11 23:20:25 1 5215 3 20 0
Top 5 Foreground Waits
The percentages i get here, do not seem to match up exactly, with the percentages you get, when you run awrrpt.sql, to generate the corresponding awr report. However it always seems to be within + or – 3% of the awrrpt.sql value. I think that the variation is because of the way that the awrrpt.sql might be rounding values, and probably how it accouts for Idle events.
with se as (
select sn.begin_interval_time begintime,
sn.end_interval_time EndTime,se.snap_id SnapId,se.instance_number Inst,
se.event_name stat,se.time_waited_micro_fg value,
nvl(lag(se.time_waited_micro_fg) over(partition by se.instance_number,se.event_name
order by se.instance_number,se.snap_id,se.event_name),0) prevval,
se.time_waited_micro_fg-
nvl(lag(se.time_waited_micro_fg) over(partition by se.instance_number,se.event_name
order by se.instance_number,se.snap_id,se.event_name),0) valuediff
from dba_hist_system_event se,dba_hist_snapshot sn
where se.dbid=&dbid
and se.dbid = sn.dbid
and se.instance_number = sn.instance_number
and se.snap_id = sn.snap_id
and se.snap_id between &bsnap-1 and &esnap
and se.wait_class != 'Idle'
order by se.snap_id,se.instance_number,se.event_name
) ,
sdbcpu as (
select sn.begin_interval_time begintime,sn.end_interval_time EndTime,
stm.snap_id snapid,stm.instance_number inst,stm.stat_name stat
,stm.value value
,nvl(lag(stm.value) over(partition by stm.instance_number order by stm.instance_number,stm.snap_id),0) prevval
,stm.value-
nvl(lag(stm.value) over(partition by stm.instance_number order by stm.instance_number,stm.snap_id),0) valuediff
from dba_hist_sys_time_model stm,dba_hist_snapshot sn
where
stm.stat_name = ('DB CPU')
and stm.dbid = &dbid
and stm.snap_id between &bsnap-1 and &esnap
and stm.dbid = sn.dbid
and stm.instance_number = sn.instance_number
and stm.snap_id = sn.snap_id
order by stm.snap_id,stm.instance_number
) ,
sunion as (
select begintime,endtime,snapid,inst,stat,valuediff from se
union all
select begintime,endtime,snapid,inst,stat,valuediff from sdbcpu
order by 3,4
),
spct as (
select begintime,endtime,snapid,inst,stat,valuediff,
round(ratio_to_report(valuediff) over (partition by snapid,inst),4) as pct
from sunion
order by 3,4 asc,7 desc
)
select * from (
select to_char(begintime,'DD-MON-RR HH24:MI:SS') begintime
,to_char(endtime,'DD-MON-RR HH24:MI:SS') endtime,snapid,inst,stat,valuediff,round(pct*100,2) pct,
row_number() over (partition by snapid,inst order by snapid,inst asc,pct desc) as rnum
from spct
)
where rnum < 6 and snapid between &bsnap and &esnap
/
BEGINTIME ENDTIME SNAPID in STAT VALUEDIFF PCT
-------------------- -------------------- ---------- --- ------------------------------ ------------- ------
16-NOV-11 21:00:12 16-NOV-11 21:20:16 5209 1 DB CPU 28856557 83.24
16-NOV-11 21:00:12 16-NOV-11 21:20:16 5209 1 name-service call wait 2073440 5.98
16-NOV-11 21:00:12 16-NOV-11 21:20:16 5209 1 control file sequential read 843201 2.43
16-NOV-11 21:00:12 16-NOV-11 21:20:16 5209 1 enq: PS - contention 634127 1.83
16-NOV-11 21:00:12 16-NOV-11 21:20:16 5209 1 undo segment extension 423219 1.22
16-NOV-11 21:00:12 16-NOV-11 21:20:16 5209 2 DB CPU 34408715 89.73
16-NOV-11 21:00:12 16-NOV-11 21:20:16 5209 2 enq: PS - contention 852207 2.22
16-NOV-11 21:00:12 16-NOV-11 21:20:16 5209 2 reliable message 351905 .92
16-NOV-11 21:00:12 16-NOV-11 21:20:16 5209 2 control file sequential read 323355 .84
16-NOV-11 21:00:12 16-NOV-11 21:20:16 5209 2 row cache lock 286882 .75
Physical and Logical I/O
select to_char(begintime,'DD-MON-RR HH24:MI') begintime,to_char(endtime,'DD-MON-RR HH24:MI') endtime
,(extract(Minute from endtime-begintime)*60+extract(Second from endtime-begintime)) secs,
snapid,inst,
prd-nvl(lag(prd) over (partition by inst order by inst,snapid),0) prddiff,
pwrt-nvl(lag(pwrt) over (partition by inst order by inst,snapid),0) pwrtdiff,
iordreq-nvl(lag(iordreq) over (partition by inst order by inst,snapid),0) iorddiff,
iowrtreq-nvl(lag(iowrtreq) over (partition by inst order by inst,snapid),0) iowrtdiff,
prmbr-nvl(lag(prmbr) over (partition by inst order by inst,snapid),0) prmbrdiff,
cgets-nvl(lag(cgets) over (partition by inst order by inst,snapid),0) cgetsdiff,
dbgets-nvl(lag(dbgets) over (partition by inst order by inst,snapid),0) dbgetsdiff
from
(
select sn.begin_interval_time begintime,
sn.end_interval_time EndTime,ss.snap_id SnapId,ss.instance_number Inst,
sum(decode(ss.stat_name,'physical read total bytes',value,0)) prd,
sum(decode(ss.stat_name,'physical write total bytes',value,0)) pwrt,
sum(decode(ss.stat_name,'physical read total IO requests',value,0)) iordreq,
sum(decode(ss.stat_name,'physical write total IO requests',value,0)) iowrtreq,
sum(decode(ss.stat_name,'physical read total multi block requests',value,0)) prmbr,
sum(decode(ss.stat_name,'consistent gets',value,0)) cgets,
sum(decode(ss.stat_name,'db block gets',value,0)) dbgets
from dba_hist_sysstat ss,dba_hist_snapshot sn
where ss.dbid=&dbid
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.snap_id = sn.snap_id
and ss.snap_id between &bsnap-1 and &esnap
and ss.stat_name in (
'physical read total bytes',
'physical write total bytes',
'physical read total IO requests',
'physical write total IO requests',
'physical read total multi block requests',
'consistent gets',
'db block gets'
)
group by sn.begin_interval_time,sn.end_interval_time,ss.snap_id,ss.instance_number
order by ss.instance_number,ss.snap_id
)
where snapid between &bsnap and &esnap
order by 4,5
/
BEGINTIME ENDTIME SECS SNAPID in PRDDIFF PWRTDIFF IORDDIFF IOWRTDIFF PRMBRDIFF CGETSDIFF DBGETSDIFF
-------------------- -------------------- ---------- ---------- --- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
16-NOV-11 20:40 16-NOV-11 21:00 1210.766 5208 1 309967486976 544388304896 17660147 36335142 1139579 4468788730 2328671039
16-NOV-11 20:40 16-NOV-11 21:00 1210.782 5208 2 165472269312 510302864896 16065810 34894618 144948 4168326257 2064355182
16-NOV-11 20:42 16-NOV-11 21:00 1079.081 5208 3 3122675712 153791488 56249 6006 10713 1307859 60272
16-NOV-11 20:43 16-NOV-11 21:00 1028.123 5208 4 1318486016 59018752 26009 5436 7229 1199275 56027
Top 10 sql statements By Elapsed Time
select * from (
select ss.snap_id snapid,ss.instance_number inst,ss.sql_id sqlid
,round(sum(ss.elapsed_time_delta)) elapsed
,nvl(round(sum(ss.executions_delta)),1) execs
,round(sum(ss.buffer_gets_delta)) gets
,round(sum(ss.rows_processed_delta)) rowsp
,round(sum(ss.disk_reads_delta)) reads
,dense_rank() over(partition by snap_id,instance_number order by sum(ss.elapsed_time_delta) desc) sql_rank
from
dba_hist_sqlstat ss
where
ss.dbid = &dbid and
ss.snap_id between &bsnap and &esnap
group by ss.snap_id,ss.instance_number,ss.sql_id
)
where sql_rank < 11 and snapid between &bsnap and &esnap
/
SNAPID in SQLID ELAPSED EXECS GETS ROWSP READS SQL_RANK
---------- --- ------------------------------ ------------- ---------- -------------- -------------- -------------- ----------
5209 1 1zr2ujm5sa5gc 4547685 1 224434 1 0 1
5209 1 3g2ugdp1af2h8 4478848 13 221134 13 0 2
5209 1 5k5v1ah25fb2c 3965629 39 360232 39 0 3
5209 1 0r1zf55mxaujd 2959144 1 237437 1 0 4
5209 1 8vwv6hx92ymmm 2633353 51062 360669 51062 0 5
5209 1 2w1s3wu1zw63a 1958993 2 153110 4 0 6
5209 1 f1y8kbhh6v9sv 1709282 1 7
5209 1 6pw8uk8k0dv0q 1699402 1 8