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
Next
Back to DBAsupport.com
