Manual querying AWR for trend analysis and capacity planning

Summary: System resource usage and trend analysis data gathering for capacity planning can profit of Oracle AWR 10g repository as the source of data, both for RAC and single instance.

Title:	Manual querying AWR for trend analysis and capacity planning
Version:	1
Created on:	January 29, 2009 7:04 AM by Monica - Moderator
Last Modified:	January 29, 2009 7:04 AM by Monica - Moderator
Viewed:	537 times
Category:	Oracle Database
Community:	Database Tuning

Important Note: this document has been authored by an Oracle customer

Submitted by: Luca Canali
Senior DBA
CERN - European Organization for Nuclear Research
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

https://canali.web.cern.ch/canali/docs/article_metalink_on_AWR.htm

1. System metric

I had the need to provide management with system resource utilization data for trend analysis and capacity planning of a production database. Production is on 10g RAC. Standard OS monitoring tools provide system metrics for each cluster node separately, which would require the development of custom scripts to aggregate the data in a cluster-wide view. Instead I have used AWR to extract system resource usage metrics for the whole cluster. This has saved me considerable time and allowed for easier analysis. In particular I ran the query reported here below against dba_hist_sysmetric_summary. The result of the query was spooled to a file and imported to a spreadsheet to produce graphs from the collected metric values.
----------------------------
set lines 250
set pages 9999
spool sysmetric_outp.log

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

select min(begin_time), max(end_time),
       sum(case metric_name when 'Physical Read Total Bytes Per Sec' then average end) Physical_Read_Total_Bps,
       sum(case metric_name when 'Physical Write Total Bytes Per Sec' then average end) Physical_Write_Total_Bps,
       sum(case metric_name when 'Redo Generated Per Sec' then average end) Redo_Bytes_per_sec,
       sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then average end) Physical_Read_IOPS,
       sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end) Physical_write_IOPS,
       sum(case metric_name when 'Redo Writes Per Sec' then average end) Physical_redo_IOPS,
       sum(case metric_name when 'Current OS Load' then average end) OS_LOad,
       sum(case metric_name when 'CPU Usage Per Sec' then average end) DB_CPU_Usage_per_sec,
       sum(case metric_name when 'Host CPU Utilization (%)' then average end) Host_CPU_util, --NOTE 100% = 1 loaded RAC node
       sum(case metric_name when 'Network Traffic Volume Per Sec' then average end) Network_bytes_per_sec,
       snap_id
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;

spool off
--------------------------

Prior to running this and the following analysis I had set the retention period for AWR to 31 days (default is 7 days, which may be too short for trend analysis): EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 60*24*31)


2. Service Activity

On a production 10g RAC database we run several database applications. Each application is associated with a dedicated Oracle service. For performance and capacity planning analysis I needed to find the list of most active applications/services and their activity trend. Service activity is defined as the ratio of 'DB Time' over 'Elapsed Time' (i.e. the percentage of time a given service is in a DB call, as opposed to processing application logic or waiting for input). I extracted the application activity data from the AWR repository, and in particular the DBA_HIST_SERVICE_STAT view. The query I used makes use of analytic functions to extract the deltas of the 'DB time' metric between successive AWR snapshots and is RAC aware.

--------------
set lines 250
set pages 9999
col service_name for a40
col Interval_time for a40

spool service_activity.log

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

select service_name,max(END_INTERVAL_TIME) Interval_time,round(sum(DeltaValue/DeltaT_sec)*(100/1000000),0) Pct_active_time
from (
   select sn.snap_id,ss.service_name,sn.END_INTERVAL_TIME,ss.instance_number,ss.stat_name,ss.value,
       lag(ss.value) over (partition by ss.service_name,ss.instance_number order by sn.snap_id) prevValue,
       ss.value - lag(ss.value) over (partition by ss.service_name,ss.instance_number order by sn.snap_id nulls first) BlindDelta,
       nvl2(lag(ss.value) over (partition by ss.service_name,ss.instance_number order by sn.snap_id),  -- if the instance restarted Delta=0, error by defect
            ss.value - lag(ss.value) over (partition by ss.service_name,ss.instance_number order by sn.snap_id), 0 ) DeltaValue,
       extract(hour from END_INTERVAL_TIME-begin_interval_time)*3600
              + extract(minute from END_INTERVAL_TIME-begin_interval_time)* 60
              + extract(second from END_INTERVAL_TIME-begin_interval_time) DeltaT_sec
   from DBA_HIST_SERVICE_STAT ss, dba_hist_snapshot sn
   where  ss.snap_id=sn.snap_id and ss.instance_number=sn.instance_number
          and ss.stat_name='DB time'
   )
group by snap_id, service_name
having sum(DeltaValue/DeltaT_sec)*(100/1000000)>10  -- Filters out points of low activity (<10% PCT_active_time) and negative values due to instance restarts
order by snap_id, service_name;

spool off
------------------

Note: The scripts reported here have been tested against Oracle Enterprise Edition 10.2.0.3 for Linux i386 with RAC option, however they can be used against all 10g versions, both RAC and single instance.