Re: Queries running slow during a batch process

I haven't done any TKPROOF in the queries.

I'll try to do what was suggested and I let you all know about the results.

Thank you very much!

SL wrote:
> Ferrari_NYC a écrit :
>
> > Hello Sebastien
> >
> > It's OPTIMIZER_MODE=CHOOSE and the statistics are properly gathered and
> > up-to-date. The Oracle is the 9.2.0.6.0 and the OS is Solaris 8.
>
> OK.
> I don't know the reason for that but it happens (I experienced that
> myself) that some batch queries appear into the V$SQL view as having an
> optimizer_mode=NONE though having a plan computed as if having the
> CHOOSE option (as far as TKPROF shows).
>
> Did you tkprof your queries ?
> Can you post the plan here ?
>
> As D.A. Morgan posted, the reason why select on empty tables are slow
> is that storage affected to each table segment is huge so many blocks
> are read until Oracle notices that the table is actually empty .
> Depending on how often and how aggressively those tables are inserted
> and fully deleted, you may consider an TRUNCATE TABLE ... DROP STORAGE
> to get rid of the big segment. The drawback is that when the table will
> need storage for bulk INSERTS, reallocating storage can be an overhead.
> --
> SL
Received on Wed Sep 20 2006 - 11:13:55 CDT