Oracle Tips by Burleson

Oracle 10g Fixed Table Statistics

The data dictionary has many fixed tables, such as X$ tables. Oracle suggests that you also collect statistics for these objects, however, less frequently than the other normal objects.

There is a new parameter gather_fixed available in the procedure gather_database_stats which when set to TRUE, collects the statistics for data dictionary fixed tables. gather_fixed is set to FALSE by default, and causes statistics not to be gathered for fixed tables. It may not be necessary to collect statistics very often for data dictionary fixed tables.

Another procedure, gather_fixed_objects_stats, is primarily aimed at collecting statistics of fixed objects. This procedure takes the following arguments:

• STATTAB: The user statistics table identifier describing where to save the current statistics. Default value is NULL for dictionary collection.
   

• STATID: The optional identifier to associate with these statistics within STATTAB. Default value is also NULL
   

• STATOWN: The schema containing STATAB. Default value is NULL.
   

• NO_INVALIDATE: Do not invalidate the dependent cursors if it is set to TRUE. Default value is FALSE.

It is also possible to delete statistics on all fixed tables by using the new procedure delete_fixed_objects_stats. You can also perform export or import statistics on fixed tables by using the export_fixed_objects_stats and import_fixed_objects_stats procedures respectively.

The following example shows different formats:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS', -gather_fixed=>TRUE) ;

PL/SQL procedure successfully completed.

You can also use the gather_fixed_objects_stats procedure to collect statistics.

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (´ALL´);

In addition to what has been shown above, it is also possible to collect statistics for individual fixed tables. The procedures in the dbms_stats package that accept a table name as an argument are enhanced to accept a fixed table name as an argument.  Since the fixed tables do not have

Get the complete Oracle10g story:

The above text is an excerpt from "Oracle Database 10g New Features: Oracle10g Reference for Advanced Tuning and Administration", by Rampant TechPress.  Written by top Oracle experts, this book has a complete online code deport with ready to use scripts. 

To get the code instantly, click here:

http://rampant-books.com/book_2003_2_oracle10g.htm

Need an Oracle Mentor?

BEI is now offering personal mentors for Oracle DBAs where you can have an Oracle expert right at your fingertips, anytime day or night. We work with hundreds of Oracle databases every year, so we know exactly how to quickly assist you with any Oracle question.

Why risk an unplanned outage? You can now get telephone access to Don Burleson or any of his Oracle Certified DBAs with more than 20 years of full-time IT experience. Click here for details:

http://www.dba-oracle.com/service_oracle_backup.htm