Re: shared pool size and wait event 'direct path read temp'

On 8/2/23 14:25, Flora Deng wrote:
> Hi,
> Sorry to hijack this thread for a another wait event.
> We recently lowered a 19c CDB 'shared_pool_size' to counter effect ORA-4031 errors which was caused by SPM related activities.
> Now we see a couple of big queries that use parallel process with full table scans run very slow.
> And the top wait event associated with these full tablescan queries is 'direct path read temp' event.
> I suspect this have something to do with the reduction 'shared_pool_size', but how?
> Appreciate your thoughts.
> Flora

That's simple. Sort area is a part of the shared pool. When you decreased the shared pool, you've also decreased the sort area. So Oracle is spilling the intermediate sorts into the TEMP tablespace. This is the first time I hear that someone has shrunk the shared pool to avoid ORA-04031. My reaction would be to increase it. BTW, you should start your own thread. Hijacking other threads is generally frowned upon.

You can also go with the advice from Matt Stone and Trey Parker and blame Canada.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l