Using AWR data for OS information December 22, 2009
Posted by mwidlake in performance.Tags: performance, SQL
trackback
This is a demo of using AWR data to get some OS performance information.
This posting was actually a comment by (and is the work of) Bernard Polarski on a recent post about command_types I did a week or two back. I’ve lifted it into a separate posting as (a) I thought it might be useful to people and it would not get as much exposure as a comment as it would as a post and (b) I want to try and lay it out better for Bernard (WordPress likes to garble code in comments, which it did to Bernard’s efforts) and (c) I strognly believe in nicking other people’s code, if they are happy to put it “out there”. Why re-code what someone has already done and is happy to share 
I tried the code (I added the SYS. ownership on tables, so it will work even if you can’t log in as sys but have access to the objects) and it worked fine for me.
I should just add, use of AWR data is covered by an oracle licence, the diagnostic pack. If you do not have a licence for it, Oracle deem the WRH$ views as not yours to view, even though they are on your box, in the database you paid for, containing data gathered by your CPUs by code that is damned tricky to turn off and is on by default. Shesh.
Any mistakes or typoes, blame me not Bernard. I took the liberty of tweaking some of the English, hope that is OK Bernard.
*************************************
Okay a challenge : Let me try to surprise you with something improbable. Some months ago while investigating an unsual load on a Linux box, the unix sysadmin told us he could not go back more than 2 weeks with ‘sar’ history. Since Oracle takes also system stats I quickly enriched smenu with an Oracle sar.
The example below gives the sar stats for the last 10 snaps, it is stored into shortcut sts and called with param ‘-sar’
( replace workd Superior_TO and Inferior_to with mathematic signs, system eat them)
/tmp: sts -sar 10
+ sqlplus -s '/ as sysdba'
+
set feed off verify off lines 190 pages 66
col inst new_value inst noprint;
col id1 head 'Idle time(%)' justify c
col usr1 head 'User time(%)' justify c
col sys1 head 'Sys time(%)' justify c
col io1 head 'Io Wait time(%)' justify c
col nice1 head 'Nice time (%)' justify c
col snap_len head 'Interval| (Secs)' justify c
col num_cpus new_value p_num_cpus head 'Number of CPU';
col a1 new_value secs noprint;
col SNAP_BEGIN format a20 head 'Snap begin' justify c
col SNAP_END format a20 head 'Snap end' justify c
select instance_number inst from v$instance;
SELECT value num_cpus
FROM v$osstat WHERE stat_name = 'NUM_CPUS';
prompt
prompt Negatives values correspond to Shutdown:
prompt
select snap_id, snap_len, round(id1 /snap_len *100,2) id1,
round(usr1 /snap_len *100,2) usr1,
round(sys1 /snap_len *100,2) sys1,
round(io1 /snap_len *100,2) io1,
round(nice1 /snap_len *100,2) nice1
, snap_begin, snap_end
from (
select snap_id, id1, usr1,sys1, io1, nice1
, snap_begin, snap_end ,
round( extract( day from diffs) *24*60*60*60+
extract( hour from diffs) *60*60+
extract( minute from diffs )* 60 +
extract( second from diffs )) snap_len
-- above is the exact length of the snapshot in seconds
from ( select /*+ at this stage, each row show the cumulative value.
r1 7500 8600
r2 7300 8300
r3 7200 8110
we use [max(row) - lag(row)] to have the difference
between [row and row-1], to obtain differentials values:
r1 200 300
r2 100 190
r3 0 0
*/
a.snap_id,
(max(id1) - lag( max(id1)) over (order by a.snap_id))/100 id1 ,
(max(usr1) - lag( max(usr1)) over (order by a.snap_id))/100 usr1,
( max(sys1) - lag( max(sys1)) over (order by a.snap_id))/100 sys1,
( max(io1) - lag( max(io1)) over (order by a.snap_id))/100 io1,
( max(nice1) - lag( max(nice1)) over (order by a.snap_id))/100 nice1,
-- for later display
max(to_char(BEGIN_INTERVAL_TIME,' YYYY-MM-DD HH24:mi:ss')) snap_begin,
-- for later display
max(to_char(END_INTERVAL_TIME,' YYYY-MM-DD HH24:mi:ss')) snap_end,
-- exact len of snap used for percentage calculation
( max(END_INTERVAL_TIME)-max(BEGIN_INTERVAL_TIME)) diffs
from ( /*+ perform a pivot table so that the 5 values selected appear
on one line. The case, distibute col a.value among 5 new
columns, but creates a row for each. We will use the group
by (snap_id) to condense the 5 rows into one. If you don't
see how this works, just remove the group by and max function,
then re-add it and you will see the use of max().
Here is what you will see:
Raw data : 1000 2222
1000 3333
1000 4444
1000 5555
1000 6666
The SELECT CASE creates populate our inline view with structure:
ID IDLE USER SYS IOWAIT NICE
1000 2222
1000 3333
1000 4444
1000 5555
1000 6666
the group by(1000) condenses the rows in one:
ID IDLE USER SYS IOWAIT NICE
1000 2222 3333 4444 5555 6666
*/
select a.snap_id,
case b.STAT_NAME
when 'IDLE_TIME' then a.value / &p_num_cpus
end id1,
case b.STAT_NAME
when 'USER_TIME' then a.value / &p_num_cpus
end usr1 ,
case b.STAT_NAME
when 'SYS_TIME' then a.value / &p_num_cpus
end sys1 ,
case b.STAT_NAME
when 'IOWAIT_TIME' then a.value / &p_num_cpus
end io1,
case b.STAT_NAME
when 'NICE_TIME' then a.value / &p_num_cpus
end nice1
from sys.WRH$_OSSTAT a, sys.WRH$_OSSTAT_NAME b
where
a.dbid = b.dbid and
a.STAT_ID = b.stat_id and
instance_number = &inst and
b.stat_name in ('IDLE_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME','NICE_TIME') and
a.snap_id > (( select max(snap_id) from sys.WRH$_OSSTAT) - 10 -1 )
order by 1 desc
) a, sys.wrm$_snapshot s
where a.snap_id = s.snap_id
group by a.snap_id
order by snap_id desc
)
)where rownum < (10+1);
Number of CPU
-------------
2
Negatives values correspond to Shutdown:
Interval
SNAP_ID (Secs) Idle time(%) User time(%) Sys time(%) Io Wait time(%) Nice time (%) Snap begin Snap end
---------- ---------- ------------ ------------ ----------- --------------- ------------- -------------------- --------------------
2212 3603 87.07 9.43 2.84 16.19 0 2009-12-15 14:00:19 2009-12-15 15:00:21
2211 3613 90.15 7.31 2.14 18.97 0 2009-12-15 13:00:06 2009-12-15 14:00:19
2210 3593 92.58 5.91 1.4 7.91 0 2009-12-15 12:00:13 2009-12-15 13:00:06
2209 3574 91.24 6.78 1.73 9.37 0 2009-12-15 11:00:38 2009-12-15 12:00:13
2208 3620 90.04 7.8 1.96 11.81 0 2009-12-15 10:00:19 2009-12-15 11:00:38
2207 3594 89.8 6.89 2.37 12.99 0 2009-12-15 09:00:25 2009-12-15 10:00:19
2206 3611 91.75 5.81 1.87 8.29 0 2009-12-15 08:00:14 2009-12-15 09:00:25
2205 3599 92.38 5.63 1.68 8.57 0 2009-12-15 07:00:15 2009-12-15 08:00:14
2204 3605 91.72 6.51 1.58 10.79 0 2009-12-15 06:00:11 2009-12-15 07:00:15
2203 3601 86.36 10.35 2.94 10.62 0 2009-12-15 05:00:10 2009-12-15 06:00:11
[...] This post was mentioned on Twitter by Wataru Morohashi, oracleusersph. oracleusersph said: Using AWR data for OS information http://mwidlake.wordpress.com/2009/12/22/using-awr-data-for-os-information/ http://bit.ly/7r2mag [...]
This code works for me also :p
I guess what I mean is, Bernard, the code still works after I messed around with it to fix the layout!!! There is no doubt it originally worked of course.
similar technique used by Christian Antognini for gathering live sar information from db by using v$OSSTATS is here (Bernards Script also shines on comments on that page)
http://antognini.ch/2009/05/report-information-about-cpu-activity-in-sqlplus/
[...] 8-How to gather historic sar type info from AWR snaps? Martin Widlake-Using AWR data for OS information [...]