Re: analyze for all tables and there is hung in select
Date: Mon, 22 Jun 2009 07:14:10 -0700 (PDT)
Message-ID: <33b26cfc-9524-46f6-9b88-9c82f2391814_at_r3g2000vbp.googlegroups.com>
On Jun 22, 7:27 am, NOVA <nova1..._at_gmail.com> wrote:
> On Jun 22, 11:14 am, NOVA <nova1..._at_gmail.com> wrote:
>
>
>
>
>
> > 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.
>
> 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?- 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 -- Received on Mon Jun 22 2009 - 09:14:10 CDT