STATISTICS_LEVEL

最新推荐文章于 2021-09-24 13:07:49 发布

原创最新推荐文章于 2021-09-24 13:07:49 发布 · 203 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #Cache #SQL Server #OS #SQL

oracle 专栏收录该内容

52 篇文章

订阅专栏

本文详细介绍了 Oracle 数据库中 STATISTICS_LEVEL 参数的作用及其不同设置对数据库特性的影响，包括自动负载仓库快照、自动数据库诊断监视器等。此外，还展示了如何查询当前参数设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

看图说话：

Oracle Advisory BASIC TYPICAL ALL
-------------------------------------------
Buffer Cache Advice X X
MTTR Advice X X
Shared Pool Advice X X
Segment Level Statistics X X
PGA Advice X X
Timed Statistics X X
Timed OS Statistics X
Plan Execution Statistics X

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

Automatic Workload Repository (AWR) Snapshots

Automatic Database Diagnostic Monitor (ADDM)

All server-generated alerts

Automatic SGA Memory Management

Automatic optimizer statistics collection

Object level statistics

End to End Application Tracing (V$CLIENT_STATS)

Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)

Service level statistics

Buffer cache advisory

MTTR advisory

Shared pool sizing advisory

Segment level statistics

PGA Target advisory

Timed statistics

Monitoring of statistics

查看当前的设置：


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
------------------------------ ---------- ---------- ---------- ----------
Active Session History         ENABLED    ENABLED    TYPICAL    NO
Bind Data Capture              ENABLED    ENABLED    TYPICAL    NO
Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO
Global Cache Statistics        ENABLED    ENABLED    TYPICAL    NO
Longops Statistics             ENABLED    ENABLED    TYPICAL    NO
MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO
Modification Monitoring        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
Threshold-based Alerts         ENABLED    ENABLED    TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        YES
Timed Statistics               ENABLED    ENABLED    TYPICAL    YES
Ultrafast Latch Statistics     ENABLED    ENABLED    TYPICAL    NO
Undo Advisor, Alerts and Fast  ENABLED    ENABLED    TYPICAL    NO
Ramp up


16 rows selected.

参考文献：
Values for init.ora parameter STATISTICS_LEVEL [ID 255452.1]

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#sthref639