[color=darkred]Oracle统计信息收集具体方法及GATHER_FIXED_OBJECTS_STATS注意事项

[backcolor=rgb(248, 248, 248)]一、统计信息收集具体方法[backcolor=rgb(248, 248, 248)]Oracle提供了丰富的统计信息采集方法。[backcolor=rgb(248, 248, 248)]
[backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)]（1）数据库级统计信息采集[backcolor=rgb(248, 248, 248)]建议对数据规模较小、数据变化比较大，而且时间和资源充裕的系统，在整个数据库级采集统计信息。例如：[backcolor=inherit]SQL> exec DBMS_STATS.GATHER_DATABASE_STATS(estimate_[backcolor=inherit] [backcolor=inherit]percent[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]10, [backcolor=inherit]Degree[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]8, [backcolor=inherit]Cascade[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]TRUE, [backcolor=inherit]Granularity[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]'ALL');

[backcolor=rgb(248, 248, 248)]（2）schema级统计信息采集[backcolor=rgb(248, 248, 248)]其次，可考虑在schema级进行统计信息采集。例如：
[backcolor=inherit]SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>' ORADWA[backcolor=inherit]', [backcolor=inherit]estimate_percent[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]10, [backcolor=inherit]Degree[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]8, [backcolor=inherit]Cascade[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]TRUE, [backcolor=inherit]Granularity[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]'ALL');

[backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)][font=Courier New]（3）表级统计信息采集[backcolor=rgb(248, 248, 248)][font=Courier New]建议对数据量较大、数据变化也比较大的表，在表级进行统计信息采集。例如：
[backcolor=inherit]SQL> exec DBMS_STATS. GATHER_TABLE_STATS (ownname=>' [backcolor=inherit]ORADWA[backcolor=inherit]', [backcolor=inherit]tabname[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]'表名',[backcolor=inherit]estimate_percent[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]10, [backcolor=inherit]Degree[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]8, [backcolor=inherit]Cascade[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]TRUE, [backcolor=inherit]Granularity[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]'ALL');

[backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)]（4）分区级统计信息采集[backcolor=rgb(248, 248, 248)]建议对数据量较大、数据变化也比较大的分区表，在分表级进行统计信息采集，特别是只采集数据变化较大的分区。例如：
[backcolor=inherit]SQL> exec DBMS_STATS. GATHER_TABLE_STATS (ownname=>' [backcolor=inherit]ORADWA[backcolor=inherit]', [backcolor=inherit]tabname[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]'表名', [backcolor=inherit]partname[backcolor=inherit]=[backcolor=inherit]'分区名'[backcolor=inherit],estimate_[backcolor=inherit]percent[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]5, [backcolor=inherit]Degree[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]8, [backcolor=inherit]Cascade[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]TRUE, [backcolor=inherit]Granularity[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]'[backcolor=inherit]PARTITION', [backcolor=inherit]method_opt[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]' FOR ALL INDEXED COLUMNS');
[backcolor=rgb(248, 248, 248)]其中，Granularity用于定义采集粒度，具体值的含义如下。[backcolor=rgb(248, 248, 248)]① ALL：采集Global、partition、subpartition等粒度统计信息。[backcolor=rgb(248, 248, 248)]② AUTO：根据分区类型，由Oracle确定统计信息采集粒度。[backcolor=rgb(248, 248, 248)]③ PARTITION：只采集partition粒度统计信息。[backcolor=rgb(248, 248, 248)]④ SUBPARTITION：只采集subpartition粒度统计信息。[backcolor=rgb(248, 248, 248)]建议根据各个系统分区的特点，有针对性地设置Granularity参数。例如设置为ALL，统计信息精度最高，但资源消耗最大。而如果SQL语句只在分区或子分区内部进行操作，即没有跨分区操作，则可设置为PARTITION或SUBPARTITION。[backcolor=rgb(248, 248, 248)]
[backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)]（5）数据字典统计信息采集
[backcolor=rgb(248, 248, 248)]由于10g只支持CBO，因此数据字典统计信息也需要进行采集和及时更新，从而保障对数据字典表访问的SQL语句性能最优。特别是数据库结构发生变化时，例如增加新表、表结构变化时，应进行数据字典统计信息采集。例如：
[backcolor=inherit]SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS (estimate_[backcolor=inherit]percent[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]100, [backcolor=inherit]Degree[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]8, [backcolor=inherit]Cascade[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]TRUE, [backcolor=inherit]Granularity[backcolor=inherit]=[color=rgb(0,102,153)][backcolor=inherit]>[backcolor=inherit]'ALL');

[backcolor=rgb(248, 248, 248)]（6）动态性能表统计信息采集[backcolor=rgb(248, 248, 248)]由于10g只支持CBO，因此动态性能表(Fixed Object)统计信息也需要进行采集和及时更新，从而保障当使用AWR、Statspack等工具时，对动态性能表访问的SQL语句性能最优。特别是业务负载（Workload）发生变化之后应进行此项工作。例如：
[backcolor=inherit]SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
[backcolor=rgb(248, 248, 248)][font=Courier New]（7）硬件统计信息采集[backcolor=rgb(248, 248, 248)][font=Courier New]为更准确地评估SQL语句执行的成本和代价，10g增加了对硬件指标统计信息的采集功能。例如I/O寻址速度、I/O传输速度、CPU处理速度等。建议在某个典型业务开始之前，执行如下语句：
SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('START');
[backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)] [backcolor=rgb(248, 248, 248)]在典型业务结束之后，执行如下语句：[backcolor=inherit]SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('STOP');
（8）v$lock 视图访问慢解决方法
V$ 视图访问慢 --解决方法分析：可能是有数据字典统计信息过久，造成。exec dbms_stats.gather_fixed_objects_stats;----收集所有数据字典的fixed table 的统计信息，请勿在业务高峰期间执行。
如果是v$lock 视图访问慢，可以只收集它的统计信息exec dbms_stats.gather_fixed_objects_stats('x$ksuse'); ----还是需要关注系统性能情况
如果需要单独对这张fixed_table进行统计分析也可以用：SQL>exec DBMS_STATS.GATHER_TABLE_STATS ('SYS', 'X$KSUSE');SQL>[font=Monaco,]exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'RECYCLEBIN$');
然后确认统计信息的正确性：select count(*) from sys.tab_stats$;select num_rows, last_analyzed from user_tab_statistics where table_name = 'x$ksuse';

关于基表的查询：SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$LOCK';or:select dbms_metadata.get_ddl('VIEW', 'DBA_EXTENTS') from dual;

Gather_schema_stats gathers statistics for objects owned by the SYS Schema. We recommend gathering statistics for the SYS schema, specifically if you are using Oracle APPS.If your database encounters a lot of changes (DMLs) for SYS schema objects, then it is recommended to collect SYS schema statistics. The collection of statistics on SYS Schema objects will optimize the performance of internal recursive queries and application queries on SYS schema objects.

To gather dictionary stats, execute one of the following:SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Gather_fixed_objects_stats also gathers statistics for dynamic tables, e.g. the X$ tables which loaded in SGA during the startup. Gathering statistics for fixed objects would normally be recommended if poor performance is encountered while querying dynamic views ,e.g. V$ views. Since fixed objects record current database activity, statistics gathering should be done when database has a representative load so that the statistics reflect the normal database activity.

To gather the fixed objects stats, use the following:EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

二、GATHER_FIXED_OBJECTS_STATS注意事项

该gather_fixed_objects_stats存储过程收集的X$基表对象如下，一般建议在系统高峰时段收集 例如大量session登陆之后，以保证v$SESSION、V$PROCESS、V$LOCK等常用视图相关的SQL语句执行计划恰当。select table_name,num_rows,last_analyzed from dba_tab_statistics where last_analyzed is not null order by last_analyzed desc;
[table=451,rgb(255, 255, 255)]
[td=262]ble_name[td=60]Num_rows[td=129]Last_analyzed
X$XS_SESSION_NS_ATTRIBUTES[align=right]0[align=right]2013/8/16 9:34
X$XS_SESSION_ROLES[align=right]0[align=right]2013/8/16 9:34
X$XS_SESSIONS[align=right]0[align=right]2013/8/16 9:34

以是官方的关于GATHER_FIXED_OBJECTS_STATS使用注意事项：

APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
Any 10g and 11g release database is affected.

Symptoms

[list]
[*]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保护，错误或者丢失的统计信息会产生大量消耗。[list]
[*]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$查询产生坏的执行计划。[list]
[*]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$表，也会导致性能问题 [list]
[*]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.
[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)] Cause[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]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.[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]从10.1开始，当没有统计信息时，优化器使用采样统计信息而不是之前的RBO，如果动态采集消耗大量时间，会导致hang-like现象。[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]This may be expected behavior.[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]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.[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]在x$表上的latch是非常昂贵的，所以如果没有适当的统计信息，性能下降是可想而知的(有些情况下，会对实例产生广泛的影响，例如对shared pool的序列访问)。在巨大，繁忙系统，大量latch请求时间，通过views查询x$表，latch 被hold住，是很普遍的。[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)] Solution[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]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.[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]这样，标准的建议是收集x$表的统计信息，以便优化器决定更加好的路径。一些负载是必须的以便表示数据库的体积和内容尽可能的多的。[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]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.
[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]当在收集统计信息的时候，性能下降可能是要经历的。例如，如果在系统高峰期收集统计信息，可能会导致竞争，使性能下降或hang住。
[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]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.
[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)] 有些情况下没有统计信息会产生好的，但是大部分情况是，收集统计信息比没有统计信息更能产生好的执行计划。[color=rgb(70,70,70)][backcolor=rgb(229, 231, 231)]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. 代表性的统计信息，在业务低峰统计。要计划不同的相关内容。[list=1]
[*]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:
[list=1]
[*]01.(Relatively) Static Data once the instance is warmed -this is mainly structural data, for example, views covering datafiles, controlfile contents, etc02.Data that changes session based on the number of sessions connected, for example: v$session, v$access, etc.
03.Volatile data, based on workload mix -- v$sql, v$sql_plan, etc2）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).选择在业务低峰，数据库暖起来后，如果实例在正常的负载的情况下有大量会话，尝试在大量会话下收集信息。
3）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$表是不稳定的，很难获取准确的统计信息，在这中情况下，没有统计信息会比较好。
3、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.因为它不可能预测个体环境的可能性经历显著的性能下降,测试是强烈鼓励。性能下降不可能被复制在测试环境中，但是潜在已知的问题是存在的。
4、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. 当在收集统计信息时，性能可能会下降。需要考虑并发级别。
5、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”在和锁表的统计数据作为一个短期的解决方案。从一个长远的解决方案的角度来看,它会比有统计但有一个运行的系统可能是优先.
6、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. 如果没有收集统计信息，实例使用动态采集当编译的时候。
7、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$
[mw_shl_code=applescript,true]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:

Document 748251.1 EM Agent DBSNMP Using Up Excessive Temp Space In Database

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:

Document 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.
"[/mw_shl_code]