Re: Query Plan Problem

The only sensible solution is issue a dbms_ddl.analyze_object of the affected tables immediately before the query. You will obviously never get the timing right.

Hth,

--
Sybrand Bakker, Oracle DBA
Lynux <lynux_at_netvigator.com> wrote in message news:80f1if$oio3_at_imsp212.netvigator.com...
> 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!
Received on Thu Nov 11 1999 - 12:45:30 CST