Scribd
Upload
8 views5 pages

AWR CPU and IO Workload Analysis

Uploaded by

Gamer Advaith
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
8 views5 pages

AWR CPU and IO Workload Analysis

Uploaded by

Gamer Advaith
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

set echo off verify off

column dbid new_value dbid

select [Link] dbid


, [Link] db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v$database d,
v$instance i;
COLUMN blocksize NEW_VALUE _blocksize NOPRINT
select distinct block_size blocksize from v$datafile;

ttitle center 'AWR CPU and IO Workload Report' skip 2


set pagesize 50000
set linesize 900

col tm format a15 heading "Snap|Start|Time"


col id format 99999 heading "Snap|ID"
col dur format 9990.00 heading "Snap|Dur|(m)"
col cpu format 9990 heading "C|P|U"
col cap format 999990.00 heading "***|Total|CPU|Time|(s)"
col dbt format 9999999990.00 heading "DB|Time"
col dbc format 9999999990.00 heading "DB|CPU"
col bgc format 9999999990.00 heading "Bg|CPU"
col rman format 9999999990.00 heading "RMAN|CPU"
col totora format 9999999990.00 heading "***|Total|Oracle|CPU|(s)"
col busy format 9999999990.00 heading "Busy|Time"
col load format 9999999990.00 heading "OS|Load"
col totos format 9999999990.00 heading "***|Total|OS|CPU|(s)"
col mem format 999999999999990.00 heading "Physical|Memory"
col IORs format 999990.000 heading "IOPs|r"
col IOWs format 999990.000 heading "IOPs|w"
col IORedo format 999990.000 heading "IOPs|redo"
col IORmbs format 999990.000 heading "IO r|(mb)/s"
col IOWmbs format 999990.000 heading "IO w|(mb)/s"
col redosizesec format 999990.000 heading "Redo|(mb)/s"
col logons format 99990 heading "Sess|Start"
col logone format 99990 heading "Sess|End"
col exsraw format 9999990.000 heading "Exec|raw|delta"
col exs format 9999990.000 heading "Exec|/s"
col oracpupct format 99990 heading "Oracle|CPU|%"
col rmancpupct format 99990 heading "RMAN|CPU|%"
col oscpupct format 99990 heading "OS|CPU|%"

spool [Link]
SELECT TO_CHAR(s0.END_INTERVAL_TIME,'MM/DD/YY HH24:MI') tm,
round([Link],2) AS load,
((([Link] - [Link])* &_blocksize)/1024/1024) / ((round(EXTRACT(DAY
FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME -
s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME -
s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME -
s0.END_INTERVAL_TIME) / 60, 2))*60)
as IORmbs,
((([Link] - [Link])* &_blocksize)/1024/1024) / ((round(EXTRACT(DAY
FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME -
s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME -
s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME -
s0.END_INTERVAL_TIME) / 60, 2))*60)
as IOWmbs,
(([Link] - [Link])/1024/1024) / ((round(EXTRACT(DAY FROM
s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME -
s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME -
s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME -
s0.END_INTERVAL_TIME) / 60, 2))*60)
as redosizesec,
[Link] logons,
-- [Link] logone, -- logons end value
((round((([Link] - [Link]) / 1000000) + (([Link] - [Link]) /
1000000),2)) / ((round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME -
s0.END_INTERVAL_TIME) * 1440

+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60

+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)

+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60,


2)*60)*[Link]))*100 as oracpupct,
((round(DECODE([Link],null,'null',([Link] - [Link]) / 1000000),2)) /
((round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440

+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60

+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)

+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60,


2)*60)*[Link]))*100 as rmancpupct,
((([Link] - [Link])/100) / ((round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME
- s0.END_INTERVAL_TIME) * 1440

+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60

+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)

+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60,


2)*60)*[Link]))*100 as oscpupct
FROM dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_osstat s1t0,
dba_hist_osstat s1t1,
dba_hist_osstat s2t1, -- osstat just get the end value
dba_hist_osstat s3t1, -- osstat just get the end value
dba_hist_osstat s4t1, -- osstat just get the end value
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1,
dba_hist_sys_time_model s6t0,
dba_hist_sys_time_model s6t1,
dba_hist_sys_time_model s7t0,
dba_hist_sys_time_model s7t1,
dba_hist_sys_time_model s8t0,
dba_hist_sys_time_model s8t1,
dba_hist_sysstat s9t0, -- logons current, sysstat absolute value should
not be diffed
dba_hist_sysstat s9t1,
dba_hist_sysstat s10t0, -- execute count, diffed
dba_hist_sysstat s10t1,
dba_hist_sysstat s11t0, -- physical reads, diffed
dba_hist_sysstat s11t1,
dba_hist_sysstat s12t0, -- physical writes, diffed
dba_hist_sysstat s12t1,
dba_hist_sysstat s13t0, -- redo writes, diffed
dba_hist_sysstat s13t1,
dba_hist_sysstat s14t0, -- redo size, diffed
dba_hist_sysstat s14t1,
dba_hist_sysstat s15t0, -- physical read IO requests, diffed
dba_hist_sysstat s15t1,
dba_hist_sysstat s16t0, -- physical write IO requests, diffed
dba_hist_sysstat s16t1
WHERE [Link] = &&dbid -- CHANGE THE DBID HERE!
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND s0.instance_number = 1
AND s1.instance_number = s0.instance_number
AND s1t0.instance_number = s0.instance_number
AND s1t1.instance_number = s0.instance_number
AND s2t1.instance_number = s0.instance_number
AND s3t1.instance_number = s0.instance_number
AND s4t1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s6t0.instance_number = s0.instance_number
AND s6t1.instance_number = s0.instance_number
AND s7t0.instance_number = s0.instance_number
AND s7t1.instance_number = s0.instance_number
AND s8t0.instance_number = s0.instance_number
AND s8t1.instance_number = s0.instance_number
AND s9t0.instance_number = s0.instance_number
AND s9t1.instance_number = s0.instance_number
AND s10t0.instance_number = s0.instance_number
AND s10t1.instance_number = s0.instance_number
AND s11t0.instance_number = s0.instance_number
AND s11t1.instance_number = s0.instance_number
AND s12t0.instance_number = s0.instance_number
AND s12t1.instance_number = s0.instance_number
AND s13t0.instance_number = s0.instance_number
AND s13t1.instance_number = s0.instance_number
AND s14t0.instance_number = s0.instance_number
AND s14t1.instance_number = s0.instance_number
AND s15t0.instance_number = s0.instance_number
AND s15t1.instance_number = s0.instance_number
AND s16t0.instance_number = s0.instance_number
AND s16t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s1t0.snap_id = s0.snap_id
AND s1t1.snap_id = s0.snap_id + 1
AND s2t1.snap_id = s0.snap_id + 1
AND s3t1.snap_id = s0.snap_id + 1
AND s4t1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
AND s6t0.snap_id = s0.snap_id
AND s6t1.snap_id = s0.snap_id + 1
AND s7t0.snap_id = s0.snap_id
AND s7t1.snap_id = s0.snap_id + 1
AND s8t0.snap_id = s0.snap_id
AND s8t1.snap_id = s0.snap_id + 1
AND s9t0.snap_id = s0.snap_id
AND s9t1.snap_id = s0.snap_id + 1
AND s10t0.snap_id = s0.snap_id
AND s10t1.snap_id = s0.snap_id + 1
AND s11t0.snap_id = s0.snap_id
AND s11t1.snap_id = s0.snap_id + 1
AND s12t0.snap_id = s0.snap_id
AND s12t1.snap_id = s0.snap_id + 1
AND s13t0.snap_id = s0.snap_id
AND s13t1.snap_id = s0.snap_id + 1
AND s14t0.snap_id = s0.snap_id
AND s14t1.snap_id = s0.snap_id + 1
AND s15t0.snap_id = s0.snap_id
AND s15t1.snap_id = s0.snap_id + 1
AND s16t0.snap_id = s0.snap_id
AND s16t1.snap_id = s0.snap_id + 1
AND s1t0.stat_name = 'BUSY_TIME'
AND s1t1.stat_name = s1t0.stat_name
AND s2t1.stat_name = 'LOAD'
AND s3t1.stat_name = 'NUM_CPUS'
AND s4t1.stat_name = 'PHYSICAL_MEMORY_BYTES'
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
AND s6t0.stat_name = 'DB CPU'
AND s6t1.stat_name = s6t0.stat_name
AND s7t0.stat_name = 'background cpu time'
AND s7t1.stat_name = s7t0.stat_name
AND s8t0.stat_name = 'RMAN cpu time (backup/restore)'
AND s8t1.stat_name = s8t0.stat_name
AND s9t0.stat_name = 'logons current'
AND s9t1.stat_name = s9t0.stat_name
AND s10t0.stat_name = 'execute count'
AND s10t1.stat_name = s10t0.stat_name
AND s11t0.stat_name = 'physical reads'
AND s11t1.stat_name = s11t0.stat_name
AND s12t0.stat_name = 'physical writes'
AND s12t1.stat_name = s12t0.stat_name
AND s13t0.stat_name = 'redo writes'
AND s13t1.stat_name = s13t0.stat_name
AND s14t0.stat_name = 'redo size'
AND s14t1.stat_name = s14t0.stat_name
AND s15t0.stat_name = 'physical read IO requests'
AND s15t1.stat_name = s15t0.stat_name
AND s16t0.stat_name = 'physical write IO requests'
AND s16t1.stat_name = s16t0.stat_name
and s0.end_interval_time > to_date('&start_date','MMDDYYYYHH24MI')
and s0.end_interval_time < to_date('&end_date','MMDDYYYYHH24MI')
ORDER BY s0.snap_id DESC;
spool off

You might also like