 |
|
Oracle Tips by Burleson |
Chapter 4 Oracle Instance
Design
Figure 4.1 – A sample listing from a STATSPACK
BHR report
The predictive models for Oracle RAM areas
began with the v$db_cache_advice utility in Oracle9i. The new
v$db_cache_advice view is similar to an Oracle7 utility that also
predicted the benefit of adding data buffers. The Oracle7 utility
used the x$kcbrbh view to track buffer hits and the x$kcbcbh view to
track buffer misses.
Oracle9i r2 now has three predictive utilities
-
PGA advice - Oracle9i has introduced
a new advisory utility dubbed v$pga_target_advice. This utility
will show 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 has been extended in Oracle9i release 2 to include
a new advice called v$shared_pool_advice, and there is talk to
expending the advice facility to all SGA RAM areas in future
releases of Oracle.
-
Data Cache advice - The
v$db_cache_advice utility show the marginal changes in physical
data block reads for different sizes of db_cache_size. Bear in
mind that 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 current processing demands.
Using v$db_cache_advice
The following query can be used to perform the
cache advice function, once the db_cache_advice has been enabled and
the database has run long enough to give representative results.
--
***********************************************************
-- Display cache advice
-- ***********************************************************
Got
Oracle Scripts?
BC
shares their personal arsenal of Oracle data dictionary
scripts in this comprehensive download. With decades of
experience using Oracle monitoring scripts and Oracle tuning
scripts, we shares our secrets for navigating the Oracle
data dictionary.
Packed with more than 690 ready-to-use Oracle scripts, this is the
definitive collection for every senior Oracle DBA.
It
would take man-years to develop these scripts from scratch,
making this download the best value in the Oracle industry.
http://www.dba-oracle.com/bp/bp_elec_adv_mon_tuning.htm
|
