analyze for all tables and there is hung in select

[NOVA]

Dear all,

I have question about analyze.

I have daily batch sequence in procedures that started after finish
the analyze for all tables

But within the sequence in procedures, there is hung in select
statement from one of the tables that already analyzed.

I try to find the solution but I found one only. I just make analyze
for the table (already analyzed before) one more time then it work
successfully and very fast.

Why that happen. It should be first analyze is enough.

thx.

[NOVA]

I found this.

first analyze is compute
secound analyze estimate with sample 33 percent

it should be compute is better am i right?
but it not work with compute analyze, it work with estimate sample 33
percent.

is that can be happen?

[Mark D Powell]

> is that can be happen?- Hide quoted text -
>
> - Show quoted text -

No one can really answer why without significantly more information
such as the full Oracle version, the actual commands used to perform
the analyze, and probably snapshots of the actual resulting statistics
from both commands. Even then some additional data generated by
querying the actual table will probable be necessary.

I can think an estimate being better than a full compute where the
compute finds the data is skewed and generates a plan that uses a full
scan for best overall performance but the estimate misses the skew and
uses an index. As long as the actual queries are on the non-skewed
data the result will be very good but if the skewed value is queried
the run time may go down the tubes.

But on a modern version of Oracle you should be using dbms_stats and
not analyze to generate statistics. Starting with 10g Oracle
configures the system to automatically collect optimizer statistics on
your tables. Doing it yourself and having Oracle perform the task can
result in some query performance issues as one set of statistics
replaces the other.

HTH -- Mark D Powell --