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 ANALYZE TABLE Tips by Donald Burleson

Update.  The dbms_stats procedure should always be used to analyze Oracle tables, and the "analyze" command is only for Oracle 8i and earlier.

exec dbms_stats.gather_table_stats(MYOWNER, 'MYTABLE', cascade => TRUE);

Below is for Oracle 8i and earlier releases only . . .


The Oracle DOCs note the following syntaxes for the ANALYZE TABLE command:


Oracle ANALYZE TABLE basics

Oracle will collect statistic on the number of rows, the number of empty data blocks, the number of blocks below the high water mark, the average data block free space, the average row length, and the number of chained rows in a table when the Oracle ANALYZE TABLE command is performed:

Oracle ANALYZE TABLE can be used to collect statistics on a specific table. Before analyzing a table with the Oracle ANALYZE TABLE command you must create function based indexes on the table.

When using Oracle ANALYZE TABLE all domain indexes marked LOADING or FAILED will be skipped.

Oracle will also calculate PCT_ACESS_DIRECT statistics for index-organized tables when using Oracle ANALYZE TABLE

More on the Oracle ANALYZE TABLE command

We have two options available within the Oracle ANALYZE TABLE command, the computes statistics command syntax and the estimate statistics command syntax. When we issue ANALYZE TABLE with the computes statistics command, the entire Oracle table is analyzed via a full-table scan. Upon completion of the Oracle ANALYZE TABLE command, very accurate statistics are then placed inside the data dictionary for use by the cost-based optimizer. By using ANALYZE TABLE with the estimate statistics syntax, samples are taken from the table, and the samples are stored within the data dictionary. One factor to take into consideration when running the Oracle ANALYZE TABLE command is the time and database resource consumption required to reanalyze all of the tables.


Essentially we see the debate regarding cost-based optimization falling along two dimensions, the frequency of use of Oracle ANALYZE TABLE and the depth of the analysis. We also have to remember that if we plan to use optimizer plan stability on all our queries, the statistics returned by Oracle ANALYZE TABLE are meaningless to the cost-based optimizer because the execution plan has been predetermined and saved in a stored outline.

Computing statistics for tables using Oracle ANALYZE TABLE can be a very time-consuming operation, especially for data warehouses as systems that have many gigabytes or terabytes of information. Most Oracle professionals use the ANALYZE TABLE estimate statistics clause, sample a meaningful percentage of their data, and choose a reanalysis frequency that coincides with any scheduled changes to the database that might affect the distribution of values. For example, if a database runs purges from their transaction table each month, the period immediately following the purge would be a good time to reanalyze the CBO statistics using Oracle ANALYZE TABLE.

Related ANALYZE TABLE articles:

Collecting Statistics




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.