|
The Proactive Tuning Approach
With the incorporation of the tuning
tool known as STATSPACK utility into the Oracle database kernel
as a new feature called Automated Workload Repository (AWR),
Oracle tuning professionals were given a data repository that
allows the leisurely analysis of Oracle performance statistics
and trends over time.
The AWR allows DBAs to devise a general
tuning strategy that addresses different kinds of processing
that can take place within the Oracle application and apply
broad brush silver bullets to their Oracle database.
This approach is known as proactive
tuning. In proactive tuning, the Oracle DBA’s goal is to tune
the database by finding the optimal global parameters and
settings that will maximize Oracle throughput at any given point
in time. By using a proactive approach to Oracle tuning, the
Oracle DBA can ensure that the database is optimally tuned for
the type of processing that is demanded of it.
The dynamic SGA feature of Oracle, which
was initiated in Oracle9i, allows AWR information to be used for
dynamic SGA reconfiguration. For example, if the AWR shows that
the demands on the shared pool become very high between 1:00 pm
and 2:00 pm, the DBA could trigger a dynamic increase of the
shared_pool_size parameter during this time period.
AWR tables are quite simple. Whenever a
snapshot is requested, Oracle interrogates the in-memory v$
structures and places the information in the appropriate Oracle
dba_hist tables.
Having AWR
information captured over periods of time gives the DBA the
opportunity to accurately model an optimal performance plan for
the database. |
