Oracle professionals are now applying the proven predictive modeling techniques from
Oracle data mining (ODM) and we are seeing them use special techniques to analyze
database signatures and predict upcoming database stress. This excerpt has specific
examples and scripts to get you started fast.
This is an excerpt from the bestselling book Oracle Tuning: The Definitive Reference by Alexey Danchenkov and Donald Burleson, technical editor Mladen Gogala.
Click here to read the full chapter.
Predicting the future with AWR
Predictive modeling is one of the best ways to perform long-term Oracle instance tuning,
and the AWR tables are very helpful in this pursuit. In the predictive model of Oracle
tuning, the DBA is charged with taking the existing AWR statistics and predicting the
future needs for all instance and I/O areas within the Oracle database. For example, the
AWR physical reads could be analyzed and compared to the memory usage within the
Oracle db_cache_size. The information from the comparison could be extrapolated and
used to predict the times at which the Oracle data buffers would need to be increased in
order to maintain the current levels of performance.
The DBA can also make a detailed analysis of Oracle's data buffer caches, including the
KEEP pool, DEFAULT pool, RECYCLE pool and the pools for multiple block
sizes like db_32k_cache_size. With that information, the DBA can accurately measure the
performance of each one of the buffer pools, summarized by day-of-the-week and hour-of-
the-day over long periods of time. Based upon existing usage, the DBA can accurately
predict at what time additional RAM memory is needed for each of these data buffers.
The AWR tables also offer the DBA an opportunity to slice off the information in brand
new ways. In the real world, all Oracle applications follow measurable, cyclical patterns
called signatures. For example, an Oracle Financials application may be very active on the
first Monday of every month when all of the books are being closed and the financial
reports are being prepared. Using AWR data, information can be extracted from every
first Monday of the month for the past year which will yield a valid signature of the
specific performance needs of the end of the month Oracle financials applications.
Starting with Oracle8i, DBAs could dynamically change the Oracle database RAM regions
and other instance parameters depending upon the performance needs of the
applications. By making many initialization parameters alterable, Oracle is moving
toward a dynamic database configuration, whereby the configuration of the system can
be adjusted according to the needs of the Oracle application. The AWR can identify
these changing needs.
With Oracle9i r2, there were three predictive utilities included with the standard
STATSPACK report:
- PGA advice: Oracle9i introduced an advisory utility dubbed v$pga_target_advice. This utility shows the marginal changes in optimal, one-pass and multipass PGA execution for different sizes of pga_aggregate_target, ranging from 10% to 200% of the current value.
- Shared Pool advice: This advisory functionality was extended in Oracle9i r2 to include
an advice called v$shared_pool_advice.
- Data Cache advice: The v$db_cache_advice utility shows the marginal changes in physical
data block reads for different sizes of db_cache_size. The data from STATSPACK can
provide similar data as v$db_cache_advice, and most Oracle tuning professionals use
STATSPACK and v$db_cache_advice to monitor the effectiveness of their data buffers.
These advisory utilities are extremely important for the Oracle DBA who must adjust the
sizes of the RAM areas to meet processing demands. The following display_cache_advice.sql
query can be used to perform the cache advice function once the v$db_cache_advice has
been enabled and the database has run long enough to give representative results.
column c1 heading 'Cache Size (meg)' format 999,999,999,999
column c2 heading 'Buffers' format 999,999,999
column c3 heading 'Estd Phys|Read Factor' format 999.90
column c4 heading 'Estd Phys| Reads' format 999,999,999
select
size_for_estimate c1,
buffers_for_estimate c2,
estd_physical_read_factor c3,
estd_physical_reads c4
from
v$db_cache_advice
where
name = 'DEFAULT'
and
block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
and
advice_status = 'ON';
Click here to read the rest of this chapter.
