文档 ID 457926.1

最新推荐文章于 2021-02-21 11:31:01 发布

1ihj2015

最新推荐文章于 2021-02-21 11:31:01 发布

阅读量864

点赞数

分类专栏： Study & Testing

Study & Testing 专栏收录该内容

66 篇文章 0 订阅

订阅专栏

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