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 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
and include the URL for the page.
