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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle 10g changes to dbms_stats

Oracle Database Tips by Donald Burleson

With Oracle Database 10g, there are some new arguments available for the dbms_stats package subprograms. Those parameters are granularity and degree.


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.


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.

10g DML Table Monitoring Changes

With Oracle Database 10g, the statistics_level initialization parameter functions as a global option for the table monitoring mechanism. This mechanism overrides the table level MONITORING clause. In other words, the [NO] MONITORING clauses are now obsolete. The statistics_level parameter was available in 9i.

If the statistics_level parameter is set to BASIC, the monitoring feature is disabled. When it is set to TYPICAL (which is the default setting) or ALL, then the global table monitoring is enabled.

These changes are aimed at simplifying operations and also making them consistent with other related statistics. The modification monitoring mechanism is now enabled by default, and users of the GATHER AUTO or STALE feature of dbms_stats no longer have to enable monitoring explicitly for every table under the default settings.

[NOTE: You can still use the [NO] MONITORING clauses in the {CREATE | ALTER } TABLE statements as well as the alter_schema_tab_monitoring and alter_database_tab_monitoring procedures of the dbms_stats package, but these clauses and procedures are now considered as no operation. They execute without giving any error, but have no effect.]

[NOTE: There is also no table monitoring for temporary tables.]

Also see: Oracle 11g changes to dbms_stats


  • ��  
    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 -  2020

    All rights reserved by Burleson

    Oracle ® is the registered trademark of Oracle Corporation.