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

 
 Home
 E-mail Us
 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 


 

 

 


 

 

 
 

 

Oracle ANALYZE TABLE

Oracle ANALYZE TABLE Tips by Burleson Consulting

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 . . .

Oracle ANALYZE TABLE

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

Oracle ANALYZE TABLE

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.

Oracle ANALYZE TABLE

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 dba poster
 

 
 
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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.