What is Analyze ?

What is Analyze ?

Post by Ari » Sun, 10 Jun 2001 04:23:38



There is a popular SQL script called analyze.sql

1. What does this script do to the database ?

2. Is it advisable to execute this script on all databases, or are
there only certain cases where it helps to execute this script ?

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.

Thanks
AM

 
 
 

What is Analyze ?

Post by Defaul » Sun, 10 Jun 2001 07:06:41



> There is a popular SQL script called analyze.sql

> 1. What does this script do to the database ?

> 2. Is it advisable to execute this script on all databases, or are
> there only certain cases where it helps to execute this script ?

> 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.

> Thanks
> AM

I have not a clue in the world whether the .SQL script you have with the
name analyze.sql is the same one that I have ... or than anyone else has.
You make an assumption lacking in foundation.

That said the many variations on analyze are the proper method way to
populate the data dictionary views. If it takes too long then do it less
frequently or use a faster variation of the statement.

For more information on analyze go to technet.

Daniel A. Morgan

 
 
 

What is Analyze ?

Post by Niall Litchfiel » Sun, 10 Jun 2001 18:57:31



Quote:> 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.

Quote:

> 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*.

Quote:

> 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