Re: Higher Read response in 19C
Date: Sat, 17 Sep 2022 11:09:46 -0400
Message-ID: <f0fe6566-d025-569f-7118-aded8bfa441d_at_gmail.com>
On 9/17/22 06:01, Lok P wrote:
> Hello Listers, Post 19C migration we are seeing the response time of
> 'direct path read temp' has been bumped up from ~1.6ms to ~36ms. So we
> have many queries doing temp read while performing hash join
> performing slow. Also we have observed the amount of tempspill remains
> the same as it was before for those queries. We had a PGA size 40GB of
> PGA on 11g , on 19c we made it 60GB. Wanted to understand the cause of
> this high temp read response and how to fix.
Have you considered doing strace and "perf top"? That will show you what calls are you waiting for. The difference is too large for a minor algorithm change. You may be paging and allocating extra memory which may be problematic. Hopefully, you're not using automatic memory on Exadata?
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 17 2022 - 17:09:46 CEST