Tuning Advisories

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.

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

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.