> There is a popular SQL script called analyze.sql
> 1. What does this script do to the database ?
It is likely that it updates the statistics used by the cost based optimiser
to calculate optimal execution plans for sql statements.
If it either loops through your tables and issues
analyze table blah <some parameters>; or issues
dbms_utility.analyze_schema( <some other parameters>);
then this is what it is doing.
> 2. Is it advisable to execute this script on all databases, or are
> there only certain cases where it helps to execute this script ?
You should collect and keep up to date statistics for all databases where
you are using the cost based optimiser. In my view that should be all
databases unless your application vendor specifically specifies otherwise
*and can justify it*.
> 3. My intent is to get user_tables and user_tab_columns tables
> populated. I know if I run Analyze.sql these tables get populated. Is
> there any other quicker way to get these two tables populated because
> analyze.sql takes too long to execute.
You might be able to run the script less frequently (if the tables don't
change much over time). you might also be able to change the parameters
for a ten billion row table
analyze table big_tab compute statistics for all indexed columns;
will take an awful lot longer than
analyze table big_tab estimate statistics sample 1%; (if i've got the syntax
Note though that changing your statistics collection method can and probably
will change execution paths for some of your sql statements.
Audit Commission UK