Re: Queries running slow during a batch process

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