Oracle9i New Feature Series: Memory and MTTR Tuning Advisories
To help DBA tune the database, Oracle9i Release 2 introduces
the following tuning advisories:
 | Dynamic
Buffer Cache Advisory |
| MTTR
Advisory |
| Shared
Pool Advisory |
| PGA
Target Advisory |
The advisories are turned on based on the value of
initialization parameter STATISTICS_LEVEL. It could have values BASIC, TYPICAL
or ALL. The parameter is dynamic – can be changed using ALTER SYSTEM or ALTER
SESSION. The default value is TYPICAL. BASIC does not collect any statistics or
advisories. Dictionary view V$STATISTICS_LEVEL gives the various statistics
collection and advisories turned on in the database and their explanation. Here
is one from a database:
|
Statistics name
|
Description
|
Ssession Status
|
System Status
|
Activation Level
|
Statistics View Name
|
Session Settable
|
|
Buffer Cache Advice
|
Predicts the impact of different cache sizes on number of
physical reads
|
ENABLED
|
ENABLED
|
TYPICAL
|
V$DB_CACHE_ADVICE
|
NO
|
|
MTTR Advice
|
Predicts the impact of different MTTR settings on number of physical
I/Os
|
ENABLED
|
ENABLED
|
TYPICAL
|
V$MTTR_TARGET_ADVICE
|
NO
|
|
PGA Advice
|
Predicts the impact of different values of pga_aggregate_target
on the performance of memory intensive SQL operators
|
ENABLED
|
ENABLED
|
TYPICAL
|
V$PGA_TARGET_ADVICE
|
NO
|
|
Plan Execution Statistics
|
Enables collection of plan execution statistics
|
DISABLED
|
DISABLED
|
ALL
|
V$SQL_PLAN_STATISTICS
|
YES
|
|
Segment Level Statistics
|
Enables gathering of segment access statistics
|
ENABLED
|
ENABLED
|
TYPICAL
|
V$SEGSTAT
|
NO
|
|
Shared Pool Advice
|
Predicts the impact of different values of shared_pool_size on
elapsed parse time saved
|
ENABLED
|
ENABLED
|
TYPICAL
|
V$SHARED_POOL_ADVICE
|
NO
|
|
Timed OS Statistics
|
Enables gathering of timed operating system statistics
|
DISABLED
|
DISABLED
|
ALL
|
|
YES
|
|
Timed Statistics
|
Enables gathering of timed statistics
|
ENABLED
|
ENABLED
|
TYPICAL
|
|
YES
|
The above table pretty much explains what you get with the
advisories and which views to look for results.
Biju Thomas is Oracle7.3 OCP, Oracle8 OCP,
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA |
Questions/Comments? Write to webmaster@bijoos.com.
© The scripts, tips and articles appearing on BijooS.com cannot be reproduced
elsewhere without the prior permission from the webmaster.
|
|
