Re: Keep buffer cache question

Hi Jay,

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 < dmarc-noreply_at_freelists.org> wrote:

> 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
> last week.
>
>
>
> TIA!
>
>
>
> Jay Miller
>
> Sr. Oracle DBA
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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