Statistics_level
The parameter is a switch for collecting statistical parameters of a control system introduced at oracle9.2. There are three values in total: basic, typical, and all. alter is supported.
Session, alter system
Dynamic Modification. If you want to use statspack or AWR to collect system performance statistics, the value of this parameter must be typical or all. Generally, all is a comprehensive collection, including
Statistics on operating systems and SQL Execution paths, unless you encounter very serious performance problems or use statistics_level = all,
Statistics_level = typeical is enough to break down 99% of performance problems.
Example: seting statistics_level alter system set statistics_level = basic;
Alter system set statistics_level = typical;
Alter system set statistics_level = all;
Or
Alter session set statistics_level = basic;
Alter session set statistics_level = typical;
Alter session set statistics_level = all;
Oracle also provides the v $ statistics_level view to record the statistical information that can be collected by oracle under each parameter:
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 level settable
--------------------------
Buffer cache advice disabled typical no
MTTR advice disabled typical no
PGA advice disabled typical no
Plan execution statistics disabled all Yes
Segment level statistics disabled typical no
Shared Pool advice disabled typical no
Timed OS statistics disabled all Yes
Timed statistics disabled typical Yes
8 rows selected.
Statistics_level = Basic, Oracle disables collection of all performance data, that is, if you want to disable AWR or statspack collection, you just need to set alter system set statistics_level = Basic;
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 level settable
--------------------------
Buffer cache advice enabled typical no
MTTR advice enabled typical no
PGA advice enabled typical no
Plan execution statistics disabled all Yes
Segment level Statistics enabled typical no
Shared Pool advice enabled typical no
Timed OS statistics disabled all Yes
Timed Statistics ENABLED TYPICAL YES
8 rows selected.
Statistics_level = typical
Wait, except plan_executetion_statistics and OS statistics cannot be collected, other items can be collected. To collect these two items, you must set statistics_level = all;
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 Level Settable
--------------------------
Buffer Cache Advice ENABLED TYPICAL NO
MTTR Advice ENABLED TYPICAL NO
PGA Advice ENABLED TYPICAL NO
Plan Execution Statistics ENABLED ALL YES
Segment Level Statistics ENABLED TYPICAL NO
Shared Pool Advice ENABLED TYPICAL NO
Timed OS Statistics ENABLED ALL YES
Timed Statistics enabled typical Yes
If statistics_level = all, the system collects all statistics.
