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.
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';