| |
 |
|
statistics_level parameter tips
Oracle Database Tips by Donald BurlesonMarch 7, 2015
|
The statistics_level initialization parameter
was introduced in Oracle 9i Release 2 to control all major statistics
collections or advisories in the database. Unsetting the default
statistics_level=basic will
disable AWR and the advisory utilities.
Also see my notes
on using the gather_plan_statistics hint.
There are three values for statistics_level, and it
can be altered dynamically (and also simulated with the
gather_plan_statistics hint). It also requires that
statistics_level=true:
alter system set
statistics_level=basic;
alter system set
statistics_level=typical;
alter system set statistics_level=all;
Oracle ACE and author of ?Oracle
Job Scheduling? and ?PL/SQL
Tuning Secrets?,
Dr. Tim Hall published this script to show the values for the different
statistics_level. Note that statistics_level=typical activates the
advisory statistics collections, but it does not activate 'timed OS statistics?
and ?Plan Execution Statistics?. To activate these statistics collection, you
must set statistics_level=all. There is very little overhead for
collecting typical statistics but there will be extra overhead for collecting
the OS and plan execution statistics, and statistics_level=all should
only be used when troubleshooting a performance problem. For single SQL
statements, we recommend using the gather_plan_statistics hint instead.:
SQL> ALTER SYSTEM SET statistics_level=basic;
System altered.
SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name;
Session System Activation Session
Statistics Name Status Status Level Settable
------------------------------ ---------- ---------- ----------
----------
Buffer Cache Advice DISABLED DISABLED TYPICAL NO
MTTR Advice DISABLED DISABLED TYPICAL NO
PGA Advice DISABLED DISABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Segment Level Statistics DISABLED DISABLED TYPICAL NO
Shared Pool Advice DISABLED DISABLED TYPICAL NO
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics DISABLED DISABLED TYPICAL YES
8 rows selected.
SQL> ALTER SYSTEM SET statistics_level=typical;
System altered.
SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name;
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
8 rows selected.
SQL> ALTER SYSTEM SET statistics_level=all;
System altered.
SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6 FROM v$statistics_level
7 ORDER BY statistics_name;
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 ENABLED ENABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Timed OS Statistics ENABLED ENABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES
References:
Oracle ACE and author of ?Oracle
11g New Features?,
Lutz Hartmann also notes how statistics_level functions in Oracle
10g.
Note: The dbms_xplan utility can be
used with the gather_plan_statistics hint to display the
estimated and actual rows for a SQL statement:
select /*+
gather_plan_statistics */
cust_name
from
mytab
where
stuff='tRUE?;
select *
from
table
(dbms_xplan.display_cursor (format=>'ALLSTATS LAST'));
------------------------------------------------------------------------------------------
| Id | Operation
| Name |
Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------------------
| 1 | SORT GROUP BY
|
| 1 |
1 | 1 |
|* 2 | FILTER
|
| 1 |
| 1728K |
| 3 | NESTED LOOPS
|
| 1 |
1 | 1728K |
|* 4 | HASH JOIN
|
| 1 |
1 | 1728K |
| 5 | PARTITION LIST
SINGLE
|
| 1 | 6844 | 3029
|
|* 6 | INDEX RANGE SCAN
| PROV_IX13 | 1
| 6844 | 3029 |
| 7 | PARTITION LIST
SINGLE
|
| 1 | 5899 | 5479K
|
|* 8 | TABLE ACCESS BY
LOCAL INDEX ROWID | SERVICE |
1 | 5899 | 5479K |
|* 9 | INDEX SKIP
SCAN
| SERVICE_IX8 | 1 |
4934 | 5479K |
| 10 | PARTITION LIST SINGLE
|
| 1728K | 1 | 1728K
|
|* 11 | INDEX RANGE SCAN
| CLAIM_IX7 | 1728K |
1 | 1728K |
------------------------------------------------------------------------------------------
 |
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
|
|
|
and include the URL for the page.
