 |
|
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.
and include the URL for the page.
