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 "email@example.com 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.