Oracle 参数 STATISTICS_LEVEL 官方解释，作用，如何配置最优化建议
本站中文解释
STATISTICS_LEVEL参数是Oracle在生成统计信息时使用的控制参数，其可设定取值范围为ALL、TYPICAL或NONE。 缺省值是 TYPICAL。
1、当设置为TYPICAL时，Oracle会自动执行统计信息（如数据库对象里面的索引，表，视图等）的收集，并将其存储在数据字典里面。
2、当设置为ALL时，Oracle会进一步针对数据库对象的索引，表，视图等做大量的收集，同时，它还可以收集表里面的行数，数据段大小重建频率等信息。
3、当设置为NONE时，Oracle不会收集任何统计信息，SQL执行计划会基于基本假设来执行，可能会降低性能。
正确的设置：为了提高Oracle的性能，在正常情况下，应该设置为 TYPICAL 或 ALL，以便收集更详细的统计信息。
官方英文解释
STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.
| Property | Description |
|---|---|
|
Parameter type |
String |
|
Syntax |
|
|
Default value |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Basic |
No |
The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.
When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed operating system statistics and plan execution statistics.
Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:
-
All server-generated alerts
-
Automatic Database Diagnostic Monitor (ADDM)
-
Automatic optimizer statistics collection
-
Automatic SGA Memory Management
-
Automatic Workload Repository (AWR) Snapshots
-
Buffer cache advisory
-
Database time distribution statistics (
V$SESS_TIME_MODELandV$SYS_TIME_MODEL) -
End to End Application Tracing (
V$CLIENT_STATS) -
Monitoring of statistics
-
MTTR advisory
-
Object Activity Tracking System (OATS)
-
Object level statistics
-
PGA Target advisory
-
Segment level statistics
-
Service level statistics
-
Shared pool sizing advisory
-
Timed statistics
Note:
Oracle strongly recommends that you do not disable these important features and functionality.
When the STATISTICS_LEVEL parameter is modified by ALTER SYSTEM, all advisories or statistics are dynamically turned on or off, depending on the new value of STATISTICS_LEVEL. When modified by ALTER SESSION, the following advisories or statistics are turned on or off in the local session only. Their systemwide state is not changed:
-
Timed statistics
-
Timed operating system statistics
-
Plan execution statistics
The V$STATISTICS_LEVEL view displays information about the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter. See “V$STATISTICS_LEVEL”.
See Also:
Oracle Database
Performance Tuning Guide for more information about this parameter
