oracle solution day,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.

Having no statistics (and then using dynamic

sampling) is better than bad statistics, but

representative statistics are what should be the

strategic goal  没有统计信息(使用动态采样)比错误的统计信息要好，但是具有代表性的统计信息应该是战略目标。

Representative statistics can be gathered in non-peak hours,

one simply has to plan for the different volumes involved.

代表性的统计信息，在业务低峰统计。要计划不同的相关内容。

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:

(Relatively) Static Data once the instance is warmed -this is

mainly structural data, for example, views covering datafiles,

controlfile contents, etc

Data that changes session based on the number of sessions

connected, for example: v$session, v$access, etc.

Volatile data, based on workload mix -- v$sql, v$sql_plan,

etc

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).选择在业务低峰，数据库暖起来后，如果实例在正常的负载的情况下有大量会话，尝试在大量会话下收集信息。

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$表是不稳定的，很难获取准确的统计信息，在这中情况下，没有统计信息会比较好。

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.因为它不可能预测个体环境的可能性经历显著的性能下降,测试是强烈鼓励。性能下降不可能被复制在测试环境中，但是潜在已知的问题是存在的。

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. 当在收集统计信息时，性能可能会下降。需要考虑并发级别。

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”在和锁表的统计数据作为一个短期的解决方案。从一个长远的解决方案的角度来看,它会比有统计但有一个运行的系统可能是优先.

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.

如果没有收集统计信息，实例使用动态采集当编译的时候。

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:

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

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.

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.

"