Re: What is Analyze ?

"Arif" <arif_mohammed_at_i2.com> wrote in message news:3f2585f1.0106081123.28bc7f6_at_posting.google.com...
> There is a popular SQL script called analyze.sql
>

 huh?
>
> 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 right here).

Note though that changing your statistics collection method can and probably will change execution paths for some of your sql statements.

--

Niall Litchfield
Oracle DBA
Audit Commission UK