AnalyzeThis FAQ

Wat is it?

AnalyzeThis is a pl/sql application that helps you in managing the cbo statistics for your database.
It does this by allowing you to store the dbms_stats parameters into a table, enabling the use of default settings on different levels. You can, for instance, spefify a set of parameters to be used for all partitions of a table and then make an exception for one speficif partition.

AnalyzeThis keeps also track of the old cbo statistics in a history table (together with which parameters where being used to gather these statistics) and keep track of the time it took to analyze an object.

Where can I get it?

You can download AnalyzeThis here

How do I install it

The installation is very easy:

C:\uptime\dev\Uptime\DBA\dhoogfr\gather_stats2\releases\10g_beta3>sqlplus "sys@loki.ragna.vm as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 20 21:09:11 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options

sys@LOKI> @install_analyzethis.sql
Enter user to hold the analyze packages [analyzethis]:
Enter password for user analyzethis [analyzethis]:

TABLESPACE_NAME                CONTENTS          MAX_MB       CURR_MB       FREE_MB PCT_FREE
------------------------------ ---------- ------------- ------------- ------------- --------
SYSAUX                         PERMANENT      32.767,98        180,00     32.609,61    99,52
SYSTEM                         PERMANENT      32.767,98        470,00     32.347,11    98,72
TEMP                           TEMPORARY      32.767,98        507,00     32.260,98    98,45
TEST                           PERMANENT         500,00        500,00        387,94    77,59
UNDOTBS1                       UNDO           32.767,98        355,00     32.617,55    99,54

Enter default tablespace for user analyzethis [SYSAUX]:
Enter temporary tablespace for user analyzethis [TEMP]:

creating user analyzethis
granting the necessary privileges
granting privileges needed for the packages getLastAnalyzedDate and getIndexTable
granting privileges needed for the getIndexTable package
granting privileges needed for the analyzedb package

installing the debugging objects and packages
creating the db objects

installing the packages
No errors.
No errors.

installing the analyzethis base objects and packages
creating the db objects

creating the helper packages
No errors.
No errors.
No errors.
No errors.

creating the core packages
No errors.
No errors.

installing the extention packages
No errors.
No errors.

sys@LOKI>

After this the packages have been installed into a new user and the statoptions table contains now 2 records: 1 containing the database level details for table objects and 1 for the database level details for indexes.

Ok, it is installed. Wat’s next?

Well, during installation 2 records are created in the statoptions table setting the defaults parameters for the tables and indexes in the entire database. These parameters are there to mimic the default settings used by dbms_stats.

Now for every table/index you wish to use different parameters you have to create a separate record in this statsoptions table.
The statsoptions table consists in fact out of 2 pieces, 1 piece indicating for which object (or set of objects) the record is valid and a second piece with the dbms_stats parameters that must be used.
In the first piece you can use an ‘*’ as a wild card (but only for the entire field, you can’t set the object_name field to “TEST*” to indicate the table TEST1 and TEST2).

The following tables shows which option combination are possible and in which order they will be searched for.
If none of these combinations exists then build in defaults will be used

    A) Subpartitions
       -------------

        Line    Owner       Table       Partition       SubPartition
        ------------------------------------------------------------
         1           A           T           T1              T1_1
         2           A           T           T1              *
         3           A           T           *               *
         4           A           *           *               *
         5           *           NULL        NULL            NULL

    B) Partitions
       ----------

        Line    Owner       Table       Partition       SubPartition
        ------------------------------------------------------------
         1      A           T           T1              NULL
         2      A           T           *               NULL
         3      A           *           *               NULL
         4      *           NULL        NULL            NULL             

    C) Tables / indexes
       ----------------

        Line    Owner       Table       Partition       SubPartition
        ------------------------------------------------------------
         1      A           T           NULL            NULL
         2      A           *           NULL            NULL
         3      *           NULL        NULL            NULL

For example: if you have a partition T.T2 the parameters on line 2 of table B would be used,
while for partition T.T1 analyzethis would use the parameters from table B line 1.

To help you test if the configuration is correct (the result is what you expect it to be), the function AnalyzeThis.ListObjOptions was created. With this function you can pass the name of an object and it will output the statoptions record (as a clob) that would be used when statistics are gathered for this object.

/* Retrieves the statistic options from the statoptions table that will be used
when new statistics for this object are generated.
Returns a clob containing the options in a report format
p_object_type:     the type of the object: TABLE or INDEX
p_owner:           the owner of the object, corresponds with the statoptions.owner field
p_object_name:     the name of the object, corresponds with the statoptions.object_name field
p_part_name:       the name of the partition, default null, corresponds with the statoptions.part_name field
p_subpart_name:    the name of the subpartition, default null, corresponds with the statoptions.subpart_name field
*/

FUNCTION ListObjOptions
( p_object_type     in      varchar2,
p_owner           in      varchar2,
p_object_name     in      varchar2,
p_part_name       in      varchar2    default null,
p_subpart_name    in      varchar2    default null
)
RETURN CLOB;

so now you know how to specify for which tables/indexes a given set of options must be used, but which options can I set?

Well, to keep it short, for every parameter you can use with dbms_stats.gather_[table|index]_stats a column is provided. When a dbms_stats parameter accepts a constant, a separate column is provided which can be set to ‘TRUE’ to indicate that the constant must be used.
For instance when the dbms_stats.auto_sample_size constant must be used in the estimate_percent parameter, you must set the auto_sample_size column to ‘TRUE’. This will overide any value set the estimate_percent column itself.

I have done the configuration, now how do I gather statistics?

There are 4 different methods you can use to gather statistics.
First you can specify a specific object for which you want to gather new statistics, by calling the AnalyzeThis.GatherObjStats procedure.

/* Gather statistics for the given object.
p_object_type:  object type, either TABLE or INDEX
p_owner:        owner of the object
p_object_name:  the object name
p_part_name:    the partname of the object (if applicable)
default NULL
p_subpart_name: the subpart name of the object (if applicable). Default NULL
p_backup:       if the current statistics of all objects of p_owner must backupped or not. Default true
*/

PROCEDURE GatherObjStats
( p_object_type     in      varchar2,
p_owner           in      varchar2,
p_object_name     in      varchar2,
p_part_name       in      varchar2    default NULL,
p_subpart_name    in      varchar2    default NULL,
p_backup          in      boolean     default TRUE
);

The second option is to gather statistics on all stale/empty objects in a given schema by calling the AnalyzeThis.GatherSchemaStats procedure.

/* Gather statistics for a given schema.
The options used in the internal calls to dbms_stats are taken from the statoptions table.
The startime, endtime and number of errors are stored in the run_history table and in run_history_details
the starttime, endtime, status and used statistics options are stored. The new generated statistics are also
put in statistics_history table
p_schema:     Name of the schema to analyze
p_backup:     Backup the current statistics to the statistics_history table before calculating new ones
(the new ones are always stored in the statistics_history table)
default TRUE
p_option:     Maps to the options parameter in the dbms_stats.gather_schema_stats procedure
AUTO:  for all objects with empty or out of date (stale) statistics
STALE: for all objects with out of date (stale) statistics
EMPTY: for all objects that currently don't have statistics
default 'AUTO'
p_GatherTemp: If statistics should be calculated for temporary tables
*/
PROCEDURE GatherSchemaStats
( p_schema      in      varchar2,
p_backup      in      boolean     default TRUE,
p_option      in      varchar2    default 'AUTO',
p_GatherTemp  in      boolean     default FALSE
);

Option 3 is to gather statistics on all stale/empty objects in the database by using the AnalyzeDB.GatherDBStats procedure, which just calls the AnalyzeThis.GatherSchemaStats procedure for each schema in the db minus the dictionary schema’s.

/* Gather statistics for the objects in the database,
for which the statistics are stale or empty
(filtering out the dictionary schema's, listed in dba_registry + system)
p_backup:     Backup the current statistics to the statistics_history table before calculating new ones
(the new ones are always stored in the statistics_history table)
default TRUE
p_option:     Maps to the options parameter in the dbms_stats.gather_schema_stats procedure
AUTO:  for all objects with empty or out of date (stale) statistics
STALE: for all objects with out of date (stale) statistics
EMPTY: for all objects that currently don't have statistics
default 'AUTO'
p_GatherTemp: If statistics should be calculated for temporary tables
*/
PROCEDURE GatherDBStats
( p_backup      in      boolean     default TRUE,
p_option      in      varchar2    default 'AUTO',
p_GatherTemp  in      boolean     default false
);

The fourth and final procedure you can use is the AnalyzeThis.GatherStats procedure, which accepts a reference cursor containing the list of objects for which new statistics needs to be generated.
This procedures is actually the core procedure that gets used by the three other procedures.

/* Calculates new statistics for all objects given in the passed reference cursor.
This function can be used to create statistics on custom object lists, when the
GatherSchemaStats or the GatherObjStats procedures are not sufficient.
p_obj_cursor:  reference cursor containing the list of objects for which new statistics needs to be generated.
the calling procedure is responsible for constructing the ref cursor and to close it afterwards
p_log_message: text that will be used as value for the operation field in the run_history table.
*/
PROCEDURE GatherStats
( p_obj_cursor   in out     rfc_statsinput,
p_log_message  in         varchar2
);

The ref cursor being passes is of th type rfc_statsinput, which is a strong typed ref cursor using the dbms_stats.ObjectElem as record structure.