DBMS_UTILITY - Catch!

In July 1998 'Tip of the month' I briefly mentioned about the package 'DBMS_UTILITY' as an example to scheduling jobs. This month let's see a catch in using this package!

Cost based optimization improves the query performance (if the query is not specifically tuned for rule based). This is acheived by using the statistics available on the tables and indexes involved in the query. Oracle collect the physical storage characteristics and data distribution of an index, table, column, and/or cluster and store them in the data dictionary. You can 'Compute' or 'Estimate' statistics. Computation always provides exact values, but can take longer than estimation. Estimation is often much faster than computation and the results are usually nearly exact. Use estimation, rather than computation, unless you feel you need exact values. Some statistics are always computed exactly, regardless of whether you specify computation or estimation. If you choose estimation and the time saved by estimating a statistic is negligible, Oracle computes the statistic exactly. If you do a large data load or your application does lot of inserts, updates, deletes, be sure to collect the statistics periodically. Having obsolete statistics can drastically affect the performance. Oracle uses the Cost based optimization only if statistics available in the data dictionary.

Oracle provide various methods to collect statistics. Do not analyze any SYS owned tables (data dictionary).

To collect the statistics for a table or index or cluster, use the SQL command ANALYZE. You can also delete the statistics collected using this command.

To collect the statistics for a user or schema owner, use the Oracle supplied PL/SQL procedure DBMS_UTILITY.ANALYZE_SCHEMA. [Example: exec sys.dbms_utility.analyze_schema ('USER', 'COMPUTE');]

To collect the statistics for the entire database, use the Oracle supplied PL/SQL procedure DBMS_UTILITY.ANALYZE_DATABASE. [Example: exec sys.dbms_utility.analyze_database ('COMPUTE');]

Here is the catch I mentioned earlier. All the Oracle data dictionary objects are optimized for RULE based. Analyze_database procedure does not spare the 'SYS' user. It collects statistics for all the data dictionary objects too!! This will slow down the performance of your queries involving data dictionary. Wonder why Oracle does this...??? Well this is a bug which is supposed to be fixed in version 8.0.6!! So If you have the ANALYZE_DATABASE procedure set up to run periodically, make sure you do a ANALYZE_SCHEMA ('SYS', 'DELETE'); You may want to change the ANALYZE_DATABASE procedure to a script which collects the user names from ALL_USERS view and use the ANALYZE_SCHEMA procedure to collect the statistics. (This avoids once collecting and then deleting!)

The user needs ANALYZE ANY privilege to use ANALYZE_SCHEMA and ANALYZE_DATABASE procedures.