BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








STATSPACK Reports for Forecasting

Oracle Tips by Burleson Consulting

The standard STATSPACK collection mechanism has a host of great information for trend analysis. The scripts I have placed on the companion web site for this book contain prewritten STATSPACK extract scripts.

There are three types of scripts:

  • Long-term trend extract scripts These simply display the hourly observations for the desired metric.

  • Averages by hour of the day These reports show averages aggregated by the hour of the day. These scripts are in the form rpt_avg_xxx_hr.sql and are very useful for identifying regular trends in hourly processing.

  • Averages by day of the week These reports show averages aggregated by day of the week. They are in the form rpt_avg_xxx_dy.sql and report on regular trends in daily processing.

Basic STATSPACK Metrics for Trend Analysis

The following STATSPACK data are especially interesting for trend analysis:

  • Physical reads and physical writes (rpt_io.sql, rpt_avg_io_dy.sql, rpt_avg_io_hr.sql)These reports are great for showing trends in the physical read and write activity within Oracle. Note that an Oracle physical read is not necessarily an actual disk read because of the RAM caches on most disk arrays.

  • Data buffer hit ratio (rpt_bhr.sql, rpt_avg_bhr_hr.sql, rpt_avg_bhr_dy.sql) Plotting the data buffer hit ratio over time can give the Remote DBA great insight into times when the database I/O patterns are changing. Especially useful are the average data buffer hit ratios by hour of the day and day of the week. These will often show theretofore hidden patterns in buffer processing.

  • I/O waits (rpt_io_wait.sql, rpt_avg_io_wait_hr.sql, rpt_avg_io_wait_dy.sql) The I/O wait report is useful for showing times when the database was forced to wait for a latch before granting access to an Oracle datafile.

  • Chained row fetches (rpt_chain.sql, rpt_avg_chain_dy.sql, rpt_avg_chain_hr.sql)Tracking the fetching of continued rows can help the Remote DBA decide the optimal time to reorganize the database.

  • Full table scans (rpt_fts.sql, rpt_avg_fts_dy.sql, rpt_avg_fts_hr.sql)Tracking full table scans can help the Remote DBA understand those time periods when large aggregation or summarization queries are being run. On poorly tuned databases, this metric will tell the Remote DBA when to explain the SQL in stats$sql_summary for SQL tuning.

  • Cumulative logons (rpt_logon.sql, rpt_avg_logon_dy.sql, rpt_avg_logon_hr.sql)This report tracks changes in the cumulative logons statistic in stats$sesstat. For lone databases, the hourly and daily average reports can show when the end-user community is most active.

  • Sorting activity (rpt_sorts.sql, rpt_avg_sort_dy.sql, rpt_avg_sort_hr.sql) Tracking disk sorts is essential in setting the appropriate value for sort_area_size. If the disk sorts are too high, increasing sort_area_size can greatly improve the performance of sorting tasks.

STATSPACK Extensions for Database Server Trend Analysis

If you implement the STATSPACK extensions for server statistics (see Chapter 5), you also have scripts to track the following server metrics. The scripts are called rpt_vmstat.sql, rpt_vmstat_dy.sql, and rpt_vmstat_hr.sql. These scripts allow you to choose the metric that you are interested in tracking.

NOTE: This script requires you to specify the name of the server. Since the stats$vmstat table collects data from the database server and the web servers, you need to filter the report for the server you are interested in getting a trend analysis for.

The database server and web server reports are especially useful for hardware trend analysis. These metrics include the following.

  • Run queue trends The CPU run queue is a great way to measure the load average for the CPU. This data is great for load balancing and forecasting when to add more CPUs.

  • Page-in trends Any time of nonzero page-in activity indicates a shortage of RAM memory. These reports can forecast when the server will exceed RAM capacity.

  • CPU trends Plotting the average user CPU plus system CPU values can tell the Remote DBA the times when the server is overloaded.

Let's start by examining the generic STATSPACK script and see how it is used
to track CPU usage. In the next example, we choose to track the average user CPU plus system CPU values.


connect perfstat/perfstat;
set pages 9999;

set feedback off;
set verify off;

column my_date heading 'date' format a20
column c2      heading runq   format 999
column c3      heading pg_in  format 999
column c4      heading pg_ot  format 999
column c5      heading usr    format 999
column c6      heading sys    format 999
column c7      heading idl    format 999
column c8      heading wt     format 999

 to_char(start_date,'yyyy-mm-dd') my_date,
-- avg(runque_waits)       c2
-- avg(page_in)            c3,
-- avg(page_out)           c4,
avg(user_cpu + system_cpu)           c5,
-- avg(system_cpu)         c6,
-- avg(idle_cpu)           c7,
avg(wait_cpu)           c8
   server_name = 'prodb1'
group  BY
order by

The following data was generated from running this script:

2001-12-20              6    0
2001-12-21              6    0
2001-12-22              4    0
2001-12-23              6    0
2001-12-24              4    0
2001-12-25              7    0
2001-12-26              4    0
2001-12-27              4    0
2001-12-28              5    0
2001-12-29              5    0
2001-12-30              4    0
2001-12-31              4    0
2001-01-01              3    0
2001-01-02              4    0
2001-01-03             24    0
2001-01-04             33    0
2001-01-05             23    0
2001-01-06             14    0
2001-01-07             13    0
2001-01-08             19    0
2001-01-09             22    0
2001-01-10             21    0
2001-01-11             13    0
2001-01-12             13    0
2001-01-13             10    0
2001-01-14              9    0
2001-01-15             21    0
2001-01-16             20    0
2001-01-17             32    0
2001-01-18             25    0
2001-01-19             27    0
2001-01-20             24    0
2001-01-21             24    0
2001-01-22             40    0
2001-01-23             27    0
2001-01-24             25    0
2001-01-25             23    0
2001-01-26             21    0
2001-01-27             18    0
2001-01-28             20    0
2001-01-29             40    0
2001-01-30             29    0
2001-01-31             21    0
2001-02-01             30    0
2001-02-02             33    0
2001-02-03             25    0
2001-02-04             17    0
2001-02-05             27    0

As is, this data is not particularly meaningful, but when plotted, we see a clear trend, as shown in Figure 15-9).

Figure 15-122: A plot of CPU usage over time

Here we see that the average CPU usage is increasing at a rate of 20 percent per month. At existing rates, the CPU subsystem will reach the maximum capacity in five months. This information can be critical to top IS management who must plan for computer hardware upgrades.

We can quickly modify this script to display page-in values, and we see that this database server is in need of additional RAM. Remember, any nonzero values for RAM page-in indicate that the RAM capacity has been exceeded.

Now, let's look at tracking server trends by hour of the day and day of the week.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.




Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter