oracle_X$ 统计信息(GATHER_FIXED_OBJECTS_STATS)注意事项

Applies to:

Oracle Database - Personal Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Oracle Database - Standard Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
Any 10g and 11g release database is affected.


 

Symptoms

• Missing or bad statistics on the X$ / fixed tables can lead to performance degradation or hangs. Various X$ views are protected by latches and as a result can be very expensive to query in large / busy systems.  x$表被latch保护，错误或者丢失的统计信息会产生大量消耗。
• Most commonly this issue is seen on the underlying X$ tables for DBA_EXTENTS, V$ACCESS, V$RMAN_BACKUP_JOB_DETAILS, and V$RMAN_STATUS, but any fixed table protected through latching can experience this. 
• Another commonly seen symptom is extreme TEMP space usage driven by poor plans against the fixed tables.极度的临时空间的使用导致对x$查询产生坏的执行计划。
• RMAN, Data Guard, Streams, and Grid Control make heavy usage of the fixed tables through the DBA/V$ views and so can often bear the brunt of performance issues. rman,DG,streams,GC 通过V$/dba视图大量使用x$表，也会导致性能问题 
• These are not an exhaustive list of symptoms.  Any item using X$ views and getting bad plans may be experiencing poor plans because of missing or bad statistics.

 

Cause

Starting with 10.1, the optimizer uses dynamic sampling when there are no statistics rather than defaulting to rule based optimization as previously. If the dynamic sampling is time consuming this can result in contention performance problems and possible 'hang-like' symptoms.

从10.1开始，当没有统计信息时，优化器使用采样统计信息而不是之前的RBO，如果动态采集消耗大量时间，会导致hang-like现象。

This may be expected behavior.

Latching on large fixed objects is expensive, so without proper statistics, performance degradation is expected when sub-optimal plans happen (in some cases, there are instance wide effects, such as effectively serializing access to the shared pool) . This is more prevalent in very large, or very busy systems, as the number of times the latch is requested, and/or the length of time the latch is held, will increase with load and volume in the X$ being queried through the views.

在x$表上的latch是非常昂贵的，所以如果没有适当的统计信息，性能下降是可想而知的(有些情况下，会对实例产生广泛的影响，例如对shared pool的序列访问)。在巨大，繁忙系统，大量latch请求时间，通过views查询x$表，latch 被hold住，是很普遍的。

 

Solution

As such, it is a standard recommendation to gather fixed objects statistics under load so the optimizer can determine optimal paths. Some load is required so that the database has representative volume/content for as many of the views as possible.

这样，标准的建议是收集x$表的统计信息，以便优化器决定更加好的路径。一些负载是必须的以便表示数据库的体积和内容尽可能的多的。

Note: performance degradation may be experienced while the statistics are gathering.
For example, if gathering of the fixed objects statistics is done under heavy load  this can result in the exact same contention issues, leading to performance degradation or hangs.

当在收集统计信息的时候，性能下降可能是要经历的。例如，如果在系统高峰期收集统计信息，可能会导致竞争，使性能下降或hang住。

There are some cases where having no statistics on a fixed object may produce the best plans, but in general, better plans are achieved by gathering statistics on these tables than by not gathering statistics.

 有些情况下没有统计信息会产生好的，但是大部分情况是，收集统计信息比没有统计信息更能产生好的执行计划。

The purpose of this note is to address how to plan for fixed object statistics needs and gathering.

1. Having no statistics (and then using dynamic sampling) is better than bad statistics, but representative statistics are what should be the strategic goal  没有统计信息(使用动态采样)比错误的统计信息要好，但是具有代表性的统计信息应该是战略目标。
2. Representative statistics can be gathered in non-peak hours, one simply has to plan for the different volumes involved.     代表性的统计信息，在业务低峰统计。要计划不同的相关内容。
3. 1. At a high level, there are 3 basic categories of fixed object tables (the X$ tables under the V$ views) to consider when planning for gathering fixed object statistics:
   2. 1. (Relatively) Static Data once the instance is warmed -this is mainly structural data, for example, views covering datafiles, controlfile contents, etc
      2. Data that changes session based on the number of sessions connected, for example: v$session, v$access, etc.
      3. Volatile data, based on workload mix -- v$sql, v$sql_plan, etc
   3. Choose a time of day that will give a representative sampling for as many of the above categories as possible. If gathering under peak load is not possible, then try to gather after the instance has been warmed up / running for some time so that "Static" data is relatively fixed.  If the instance has a high number of sessions under normal workload, attempt to gather the statistics when there are still a large number of sessions connected (even if the sessions are idle).选择在业务低峰，数据库暖起来后，如果实例在正常的负载的情况下有大量会话，尝试在大量会话下收集信息。
   4. There are some fixed tables that are simply very volatile by nature and it will be extremely hard to get accurate statistics on. In general though, in the case of these volatile fixed tables, better plans are achieved by gathering statistics on these tables than by not gathering statistics. 有些X$表是不稳定的，很难获取准确的统计信息，在这中情况下，没有统计信息会比较好。
4. Since its not possible to predict the likelihood of individual environments experiencing noticeable performance degradation, testing is strongly encourage. Performance Degradation has not been able to be replicated in Oracle test instances, but potential for such problems is known to exist.因为它不可能预测个体环境的可能性经历显著的性能下降,测试是强烈鼓励。性能下降不可能被复制在测试环境中，但是潜在已知的问题是存在的。
5. Plan for performance degradation while gathering the statistics. It is possible the degradation could appear to be a hang which lasts the length of stats gathering. It is also possible the instance will experience little to no degradation, particularly on smaller or less loaded systems.  Key points to consider are volume of data in the fixed tables and level of concurrency in the system. 当在收集统计信息时，性能可能会下降。需要考虑并发级别。
6. If there are severe issues, diagnose what table gathering is 'stuck' on and lock that table's statistics as a short term workaround. From a long term solution standpoint, it would be preferable to have the statistics but having a running system is likely to be the priority.   如果有严重问题,诊断表收集是什么“stuck”在和锁表的统计数据作为一个短期的解决方案。从一个长远的解决方案的角度来看,它会比有统计但有一个运行的系统可能是优先.
7. If no statistics are gathered, the instance reverts to dynamic sampling to determine statistics for the plan when the query is parsed. Plans may change on re-parse as a result, and the instance may or may not get accurate statistics in this manner.  For volatile tables (see 2.3 above) it may be extremely difficult to generate accurate statistics.                如果没有收集统计信息，实例使用动态采集当编译的时候。
8. While X$ tables last only the life of the instance, the statistics, when gathered, are stored to disk and used until deleted or replaced.  They do NOT need to be regathered on instance restart.  They only need to be regathered if workload changes significantly. 不用重新收集在实例重启的时候，只需要实例变换严重的时候，进行收集。系统自动收集任务不收集X$ For example:
9.  

SQL> select sysdate from dual; SYSDATE --------- 08-APR-09 SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP'; OWNER TABLE_NAME LAST_ANAL -------- ------------------------------ --------- SYS X$KGLDP 04-APR-09 SQL> shutdown immediate; ... SQL> startup ... Database mounted. Database opened. SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP'; OWNER TABLE_NAME LAST_ANAL -------- ------------------------------ --------- SYS X$KGLDP 04-APR-09

 

Note that the last analyzed data has stayed static even though the database has been re-started

Additionally, current Statistics can be recorded in a stats table and exported for reload later as follows:

SQL> truncate table my_user.stats_table; Table truncated. SQL> exec dbms_stats.export_fixed_objects_stats(stattab=>'STATS_TABLE', statown=>'MY_USER'); PL/SQL procedure successfully completed. SQL> select count(*) from my_user.stats_table; COUNT(*) ---------- 8846

1. If the instance experiences problems after gathering statistics, verify whether or not the statistics are representative prior to deleting them.  For example, in addition to the causes listed in this article, the EXACT same behavior as:

Note:748251.1  EM Agent DBSNMP Using Up Excessive Temp Space In Database (Doc ID 748251.1)

can be witnessed because statistics are not representative, and can be resolved by gathering representative statistics.

1. In Oracle 10g, the Automated statistics gathering job (GATHER_STATS_JOB) does NOT gather statistics against fixed objects.

SQL> select sysdate from dual; SYSDATE ------------------- 04/04/2009 12:01:48 SQL> !date Sat Apr 4 12:01:53 MST 2009 SQL> exec dbms_stats.delete_fixed_objects_stats(); PL/SQL procedure successfully completed. SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP'; OWNER TABLE_NAME LAST_ANALYZED -------- ------------------------------ ------------- SYS X$KGLDP SQL> exec dbms_scheduler.run_job('GATHER_STATS_JOB'); PL/SQL procedure successfully completed. SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP'; OWNER TABLE_NAME LAST_ANALYZED -------- ------------------------------ ------------- SYS X$KGLDP SQL> exec dbms_stats.gather_fixed_objects_stats(); PL/SQL procedure successfully completed. SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP'; OWNER TABLE_NAME LAST_ANALYZED -------- ------------------------------ ------------- SYS X$KGLDP 04/04/2009 13:09:54

 

Note: The example above was not performed sequentially so the timings are not realistic. The execution of the command did not take an hour as implied ! There were interruptions in between  the steps in the test.

 
In 11g,the Automated statistics gathering job (GATHER_STATS_JOB) is replaced with "Automatic Maintenance Tasks ". See:

Note:743507.1 Why Has the GATHER_STATS_JOB been removed in 11g? (Doc ID 743507.1)

Fixed table statistics still need to be gathered separately:

Oracle Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-04
Chapter 13 Managing Optimizer Statistics
"
 You must manually collect statistics on fixed objects, such as the dynamic performance tables, using GATHER_FIXED_OBJECTS_STATS procedure. Fixed objects record current database activity. You should gather statistics when the database has representative activity.