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.

rpt_vmstat.sql

The following data was generated from running this script:

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.