Implications of Setting Oracle9iR2’s Statistics Collection Level

Concerned about looking at statistics within Oracle? You should
take a quick look at Oracles’ new method of turning collections on or off.

It’s New

In Oracle9iR2, Oracle has introduced a new initialization
parameter called STATISTICS_LEVEL. Through this parameter, Oracle gives you the
option to set different levels of statistical collection for major statistics
and advisories.

Its Settings

There are three settings for this parameter: BASIC, TYPICAL,
and ALL. The default setting is TYPICAL. Here is a quick matrix of what
statistics and advisories are turned on depending on the setting you choose. I
have added two columns to this matrix. The first is Session Affected which
indicates those statistics that are affected by issuing the ALTER SESSION
command for the STATISTICS_LEVEL parameter. The second is the initialization
parameter that you can set in the SPFILE to override the STATISTICS_LEVEL
setting.

Statistic / Advisory

BASIC

TYPICAL

ALL

Session Affected

Control Through
SPFILE

Buffer Cache Advice

X

X

db_cache_advice

MTTR Advice

 

X

X

 

 

Shared Pool Advice

 

X

X

 

 

Segment Level Statistics

 

X

X

 

 

PGA Advice

 

X

X

 

 

Timed Statistics

 

X

X

X

timed_statistics

Timed OS Statistics

 

 

X

X

timed_os_statistics

Plan Execution Statistics

 

 

X

X

 

To take a look at the advisories and statistics controlled
by the setting of the STATISTICS_LEVEL parameter or the entries in the SPFILE
and what their current settings are, just issue the following SQL:


col statistics_name for a30 head “Statistics Name”
col session_status for a10 head “Session|Status”
col system_status for a10 head “System|Status”
col activation_level for a10 head “Activation|Level”
col session_settable for a10 head “Session|Settable”

SELECT STATISTICS_NAME,
SESSION_STATUS,
SYSTEM_STATUS,
ACTIVATION_LEVEL,
SESSION_SETTABLE
FROM v$statistics_level
ORDER BY 1
/
Session System Activation Session
Statistics Name Status Status Level Settable
—————————— ———- ———- ———- ———-
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles