Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Resource Planning

Oracle Database Tips by Donald Burleson

As we just mentioned, the STATSPACK utility captures performance and tuning statistics over long periods of time. Hence, the STATSPACK utility is very useful for doing resource planning for an entire IT organization. Within an IT organization, managers are often charged with predicting the amount of resources that are going to be needed by Oracle and ensuring that those resources are delivered in time so that the Oracle database does not experience any kind of resource related outages. A typical sample resource plan is shown in Figure 2-5.

Figure 18: A sample resource plan for an Oracle database

By using the STATSPACK tables, resource managers can plot the growth patterns of objects (tables and indexes) within the Oracle database, and come up with linear regression models that will accurately predict the disk storage needs of the Oracle database at future points in time. This resource modeling capability can also be used for predicting hardware resources, such as pending needs within the central processing units and the RAM memory structures. In Chapters 5 through 8 we will be taking a close look at how we can extend the STATSPACK tables in order to capture these types of server statistics and how we can plot the statistics in order to do predictive resource modeling for all areas within the Oracle enterprise.

Predictive Modeling

Predictive modeling is one of the most important new areas of Oracle tuning, and one that lends itself very well to the use of the STATSPACK tables. In a predictive model, the Oracle DBA is charged with taking existing statistics and predicting future needs for all areas within the Oracle database. For example, the Oracle DBA could analyze the STATSPACK data buffer hit ratio and compare it to the memory usage within the Oracle db_cache_size. The DBA can then extrapolate the information from the studies, and predict the times at which the Oracle data buffers would need to be increased in order to maintain the current levels of performance.

Likewise, the Oracle DBA can also make a detailed analysis of Oracle's data buffer caches (the KEEP pool, DEFAULT pool, the RECYCLE pool, and the pools for multiple block sizes), and accurately measure the performance of each one of these pools over long periods of time. Based upon existing usage, the Oracle DBA can accurately predict at what time additional RAM memory is needed for each of these data buffers in order to keep the current performance levels for the system.

When discussing predictive modeling, the STATSPACK tables also offer the Oracle DBA an opportunity to slice off the information according to previously unavailable parameters. In the real world, Oracle applications commonly follow cyclical patterns. For example, an Oracle Financials application may be very active on the last Friday of every month when all of the books are being closed and financial reports are being prepared. Using the STATSPACK data, the Oracle DBA can extract information from the last Friday of every month for the past year, and take a look at the specific performance needs of the end-of-month Oracle financials applications.

In Oracle8i, and on into Oracle9i, we see new features that allow the database administrator to dynamically change the database depending upon the performance needs of the applications. In Oracle9i, the Oracle DBA has the ability to dynamically alter the memory configuration of the Oracle instance. By making all initialization parameters alterable, Oracle is moving towards a dynamic database configuration, whereby the configuration of the system can be adjusted according to the needs of the Oracle application, and STATSPACK can identify these changing needs.

Once the Oracle administrators recognize cyclic performance patterns in the Oracle database, they are now in a position to reconfigure the database in order to meet the specific processing needs of the system.

Prior to Oracle9i, it was not uncommon to find several versions of Oracle's init.ora files, each one customized to the processing needs of the application that is running at the time. For example, it was not uncommon to see a special version of the Oracle instance (with a different initialization file) that was dedicated towards batch processing tasks that might occur, say, on every Friday, while another version of the init.ora file is customized for OLTP transactions. Some Oracle shops also created additional init.ora files that were suited to data warehouse processing that might occur on the weekend. In each of these cases, the Oracle database has to be stopped and restarted with the appropriate init.ora configuration file.

Starting with Oracle9i, STATSPACK can be used to identify any specific recurring time when a component of Oracle is stressed, and the DBA can then trigger a script to dynamically change Oracle during these times.  In sum, STATSPACK data is ideally suited to work with the dynamic SGA features of Oracle9i, and later chapters will show scripts for using STATSPACK to identify out-of-bounds conditions.


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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.