Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

dbms_stats.gather_fixed_objects_stats tips

Oracle Database Tips by Donald Burleson

 

Also see there important notes on analyzing fixed object tables

Starting in Oracle 10g we see a new package gather_fixed_stats for analyzing the dictionary fixed structures (the X$ tables).  We now have three types of stats to analyze for the SQL optimizer:

  • Table/object/schema stats: Via dbms_stats.gather_table_stats. Or gather_schema_stats.  Traditional metadata from data tables.

  • System stats:  Via dbms_stats.gather_system_stats:  OS statistics (disk, CPU timings).

  • Dictionary objects:  Used to make dictionary queries more efficient.  The gather_fixed_objects_stats collects the same metadata as gather_table_stats, excepts for the number of blocks.  This is because the x$ structures and the v$ views only exists in the RAM of the SGA.

The docs note "Analyze fixed objects only once, unless the workload footprint changes. You must be connected a SYS (or a user with SYSDBA) to invoke dbms_stats.gather_fixed_objects_stats.

 

Just like the workload statistics, Oracle recommends that you analyze the x$ tables only once, and during a typical database workload.

exec dbms_stats.gather_schema_stats('sYS?,gather_fixed=>TRUE)

 

exec dbms_stats.gather_fixed_objects_stats(?ALL?);

Oracle notes that the data dictionary now contains several classes of x$ structures and v$ views:  See here for all important v$ views.

 

  • Structural fixed data - for example, v$datafile, v$datafile_header, &c

v$archive_dest
v$archive_dest_status
v$archive_gap
v$archive_processes
v$archived_log
v$bh
v$buffer_pool
v$buffer_pool_statistics
v$controlfile
v$controlfile_record_section
v$database
v$datafile
v$datafile_header
v$dataguard_status

  • Session based fixed data - Such as v$session, v$access, &c.

v$session
v$session_event
v$session_longops
v$session_wait
v$session_wait_history
v$sessmetric
v$sesstat

  • Workload fixed data - Such as  v$sql, v$sql_plan

  • SQL data:

v$sql
v$sql_bind_capture
v$sql_bind_data
v$sql_cursor
v$sql_plan
v$sql_text_with_newlines
v$sql_workarea
v$sqlarea
v$sqltext
v$sqltext_with_newlines

 

We also see the new procedures dbms_stats.export_fixed_objects_stats and dbms_stats.import_fixed_ objects_stats for migrating production workload statistics into test and development instances.

 

Re-Analyzing fix object statistics

Oracle recommends a single analyze of data dictionary and x$ fixed structures for the cost-based optimizer, but it is not clear when it is necessary to re-analyze the v$ views and x$ structures.

If it ain't broke, don't fix it.  However, Oracle recommends that major parameter changes (db_cache_size, shared_pool_size. sga_target, &c ) may be followed-up with a re-analyze using dbms_stats.gather_fixed_stats.

 

gather_fixed_objects_stats usage tips

  • You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
     

  • Also note Bug 3982803 - OERI[kcbshcb_1] with DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
     

  • Andrew Holdsworth of Oracle Corporation notes that dbms_stats is essential to good SQL performance, and it should always be used before adjusting any of the Oracle optimizer initialization parameters:

'the payback from good statistics management and execution plans will exceed any benefit of init.ora tuning by orders of magnitude?

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 I/O cost, as the rows reside in memory, CBO takes into account the CPU cost of reading rows.

Oracle suggests the following best practices for collecting statistics.

  • Collect statistics for normal data dictionary objects using the same interval that you would analyze objects in your schemas. In addition, you need to analyze the dictionary objects after a sufficient amount of DDL operations have occurred.
  • Use the procedures gather_database_stats or gather_schema_stats with options set to GATHER AUTO. With this feature, only the objects that need to be re-analyzed are processed every time.
  • For fixed objects, the initial collection of statistics is usually sufficient. A subsequent collection is not usually needed, unless workload characteristics have changed dramatically.

In the next section, we will examine the changes introduced for the dbms_stats package.

With Oracle Database 10g, there are some new arguments available for the dbms_stats package subprograms. Those parameters are as follows:

  • granularity
  • degree

granularity

This parameter is used in subprograms such as gather_table_stats and gather_schema_stats. This parameter indicates the granularity of the statistics that you want to collect, particularly for partitioned tables. As an example, you can gather the global statistics on a partitioned table, or you can gather global and partition-level statistics. It has two options. They are: AUTO and GLOBAL AND PARTITION.

When the AUTO option is specified, the procedure determines the granularity based on the partitioning type. Oracle collects global, partition-level, and sub-partition level statistics if sub-partition method is LIST. For other partitioned tables, only the global and partition level statistics are generated.

When the GLOBAL AND PARTITION option is specified, Oracle gathers the global and partition level statistics. No sub-partition level statistics are gathered even it is composite partitioned object.

degree

With this parameter, you are able to specify the degree of parallelism. In general, the ?degree? parameter allows you to parallelize the statistics gathering process. The degree parameter can take the value of auto_degree .

When you specify the auto_degree, Oracle will determine the degree of parallelism automatically. It will be either 1 (serial execution) or default_degree (the system default value based on number of CPUs and initialization parameters), according to the size of the object. Take care if Hyper Threading is used, as you will have less computational power than Oracle assumes.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational