A little bit background why we need to analyze tables, indexes, clusters:
Sometimes we have changes for the tables, for example if we have regular ETL process that constantly modifies the database structure or table contents. The statistics that Oracle collects from the last table analyzation might be out of sync with the current data dictionary. And this information is often used by the optimizer when querying. Therefore querys base against the old statistics would run slow and the database performance could be decreased because of the changed statistic not being collected accordingly.
What statistics does Oracle collect by the analyzation?
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.
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
Note:Before analyzing a table with the Oracle ANALYZE TABLE command you must create function based indexes on the table.
Two options for analyzing tables: Computes Statistics VS Estimate Statistics
|Computes Statistics||Estimate Statistics|
|Method||Full Table Scan||Sampling|
|Accuracy||High||Depends on the sample|
Computer Statistics Analyzation uses the full table scan again the entire Oracle table. Upon completion of the command, the data dictionary would be updated with the highly accurate statistics by the cost-based optimizer.However, Estimate Statistics Analyzaiton would take samples from the table and the samples are stored in the data dictionary.
Note: You need to weight the time and database resource against the accuracy of the statistics before considering which method should take at your current point of view. For small to medium tables, use analyze table table_name compute statistics. For larger tables take a sample (for example, analyze table table_name estimate statistics sample 20 percent).
Utilize TOAD for table analyzations
Login Toad with your database connection. Select the table you want to analyze and right click on Analyze Table.
Once you get to the analyzation page, select the tables you want to do analyzation on and click on the green arrow to start analyzing the table.By default the method would be estimate statistics as shown in the picture below.
The success information is displayed after the analyzation.
You could also change the mode to be compute statistics by switching to the Options tab and modify the Analyze Functions as shown below:
Utilize SQLPLUS for table analyzations
Login to SQLPLUS with your database connection as below:
A better solution : Utilize DBMS_STATS to collect statistics
Here is a code snippet for how we are gonna invoke the dbms_stats package in 10g.Just note that Oracle Analyze table commands are now considered to be old fashioned. And the dbms_stats package are used more and more frequently in packages because it provides high quality infomation about tables and indexes.
options => 'GATHER AUTO',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt=>'for all columns size repeat',
cascade => true,
For a more detailed information on the features that Oracle 11g Database has offered. Please take a look at the guru’s blog as below: