Re: Query Plan Problem

Hi.

 Try following :

1. Analyze TEMP table when it is full of data ( 400K rows ).
2. Remove cron job.
3. Tune your query ( Try using /*+ FIRST_ROWS */ hint ).

 HTH. Michael.

 P.S. If you are using 8i - look QUERY OUTLINE feature.

In article <80f1if$oio3_at_imsp212.netvigator.com>,   Lynux <lynux_at_netvigator.com> wrote:
> We have some problem on running query on ANALYZED tables that joins
with
> a temp table. Because of the temporary usage our temp table,
> we does not do any analyze on such temp table within our program but
set
> it as a cron job running on every hour.
> However, this results in variation on the corresponding query plan
that
> we run the query in our program at different time. Frankly speaking,
our
> program results in running very fast at one time and very slow at
> another time that depends on whether the statistics of the temp table
is
> up-to-date.
> For example, at the first minute of an hour, the query seems running
very
> fast by joining the temp table with another already ANALYZED tables.
> But, if data is filled into the temp table at 15 minutes later and
query
> seems running very slow then because of the incorrect statistics of
the
> temp table represented.
>
> For more information, our ANALYZED table contains about 10-20
millions of
> records and the temp table records size may vary from 0 to 400
thousand.
> And the fast query may need about 3 minutes to complete but the slow
> query may need 30 hours to complete.
>
> As a supporting DBA, I am looking for any better/stable solution for
it.
> Would any person suggest what we can do to solve this problem?
> Thanks in advance!
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 11 1999 - 15:13:26 CST