Re: shared pool size and wait event 'direct path read temp'
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 2 Aug 2023 19:49:35 -0400
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
Other related posts:
