Higher Read response in 19C
Date: Sat, 17 Sep 2022 15:31:00 +0530
Message-ID: <CAKna9VbzX2NOzq956BJSU-MdF_uu+JQuMZEoweWFsVYY4+wASg_at_mail.gmail.com>
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.
Attached are the Key instance activity, top foreground waitevent and instance efficiency , sections from AWR from a similar load for 11g vs 19c.Its X8M half RAC exadata machine with 19.3.7 image. And in this migration no storage cell level changes were done; it was only the RDBMS migration from version 11.2.0.4 to 19.15. Looking into the AWR for the similar load period from 11g to 19c, I am seeing below..
1)In the "Instance Efficiency Percentages" section it shows the "Flash Cache Hit %" for the similar load period was 25% on 11g vs ~47% on 19C.
2)The "Top 10 Foreground Events by Total Wait Time" section showing there were ~21million "cell single block physical read" and avg wait time was ~621 micro sec on 11g. and for "direct path read temp" total waits were ~3.5million with ~1.68milli sec avg wait time.
But on 19c awr its showing four different stats for "cell single block physical read" i.e.
"cell single block physical read: flash cache" showing ~2.5million waits
"cell single block physical read: pmem cache" showing ~10million waits and
and ~6.1ms avg wait time.
~1.16ms avg wait time.
"cell single block physical read" showing 104K waits and ~47.37ms avg wait
time .
"cell single block physical read: RDMA" showing 33million waits and
69.56micro sec avg wait time.
"direct path read temp" showing 373K waits and 36.04 milli sec avg wait
time.
3)"IOStat by Filetype summary" on 11g was showing ~1.3TB data read with
"small read" response ~1.13ms and "large read" 43.33ms. But on 19c the read
is ~190.3GB with "small read" response ~26.84ms and "large read" 39.44ms.
It does point to the slow temp file read.
4) We do have the cell storage flash cache mode "write back" mode but i am seeing "PMEMCACHE- writethrough" so not sure if its something adding to this issue?
5) In the "cache sizes" section I am seeing an additional NUMA Pool Size. Not sure if it's related.
Thanks and Regards
Lok
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 17 2022 - 12:01:00 CEST
- application/vnd.openxmlformats-officedocument.spreadsheetml.sheet attachment: list_stats.xlsx