Re: Telling When Statistics Present

In response to earlier question, if any one cluster, table, or index in an SQL statement has statistics present, Oracle will use the version 7 cost-based optimization. The cost-based optimizer will make assumptions and educated guesses as to the composition of the other objects which do not have statistics.

Next... I would NEVER recommend using export/import to generate statistics. Statistics are part of the Data Dictionary and are not included in an export. The presence of the STATISTICS parameter in the export will cause ANALYZE statements to be included in the export file. Upon subsequent import, the ANALYZE statements will then be executed to generate fresh statistics. However, you lose some capabilities of the ANALYZE command by using them in the export/import. Specifically, you lose the ability to designate the desired "sample" population to use when generating statistics. The ANALYZE statement will let you specify an absolute number of rows or a fixed percentage of rows to use for the statistics. Typically, 20% of the rows is enough to collect good statistics. BUT, when using the STATISTICS=ESTIMATE parameter in the export, you are not given the opportunity to specify the # of rows or percentage to use. Therefore, the ANALYZE statement will use the default estimate values (1064 rows). This population is way too small to generate accurate statistics for medium to large tables.

If you decide to use STATISTICS=COMPUTE in the export, then the import may take a LONG time because it is now using 100% of the population to determine the statistics. It is usually not necessary to go to this extreme to generate good statistics.

Best bet - don't use the STATISTICS parameter on the export. If you ever have to import your data, use a separate script with the appropriate ANALYZE statements which ESTIMATE using 20-30% of the rows. Use the dba_analyze_objects view to get a list of objects in the database which can be analyzed. I believe this view is included with the Oracle only when the procedural option is installed. (A script may need to be run by the DBA to get this view defined).

One last thing... Do NOT ANALYZE an index if you intend to ANALYZE its table. The ANALYZE of the table/cluster will automatically ANALYZE all associated indexes. A separate ANALYZE of the index will simply be duplicate work which prolongs the timeframe of collecting the statistics. Received on Wed Oct 25 1995 - 00:00:00 CET