文档 ID 457926.1

最新推荐文章于 2021-02-23 11:22:43 发布

转载最新推荐文章于 2021-02-23 11:22:43 发布 · 943 阅读

收录于

Study & Testing

本文介绍了如何收集Oracle数据库中SYS和'固定'对象的性能统计信息，包括SYS对象的统计信息收集以及固定对象的统计信息收集方法，以优化内部递归查询和应用查询性能。

How to Gather Statistics on SYS Objects and 'Fixed' Objects? (文档 ID 457926.1)

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.1 [Release 12.1]
Information in this document applies to any platform.

GOAL

Outline how to Gather Optimizer Statistics For SYS and 'fixed' objects?

SOLUTION

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 if the load is heavy and if the system is busy. For more details when to gather fixed object stats, read following note:

Document:798257.1GATHER_FIXED_OBJECTS_STATS Considerations

REFERENCES

NOTE:798257.1 - Fixed Objects Statistics(GATHER_FIXED_OBJECTS_STATS) Considerations
NOTE:392364.1 - DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL') Fails With ORA-20000 ORA-6512