Have you checked what is it reading? Index blocks, table blocks or undo?
Were there any DDL or huge DML operations? Have you checked v$bh? What
about memory settings? AMM/ASMM/manual? Have you checked v$sga_resize_ops?
On Fri, Mar 15, 2019 at 12:22 AM Redacted sender Jay.Miller for DMARC <
Odd issue here. One of our apps reported slightly increased latency on a
heavily used database which started Monday evening and has been consistent
since. This is not large from a database perspective but the increase of
average response time from 1 to 3 microseconds has had a noticeable impact
on their performance.
No execution plan changes, a slightly heavier load at peak times (up about
10% from last week) but nothing that I would expect to have such an impact.
We still see the increased latency when the server is 90% idle and the load
average is 5 (32 cpus, 16 cores).
In doing an AWR report comparison for comparable times one major
difference I saw was that 2 frequently run queries were suddenly doing a
lot of physical i/o. For a comparable 2 hour period they went from 1.5
million to 1.8 million executions but physical reads increased from 0 to
1.2 million. I sampled a few other random times and this was consistent.
The queries are both doing index access. One is an index range scan and the
other a unique scan against the primary key.
I checked with the app group and they have no explanation for why the app
might suddenly be querying blocks that aren't in cache whereas they weren't
Sr. Oracle DBA