Re: Keep buffer cache question

So, what was the reason ? ,
Please clear , I did not get it.

On Fri, Mar 15, 2019, 17:15 Redacted sender Jay.Miller for DMARC < dmarc-noreply_at_freelists.org> wrote:

> Oy, yes. Sorry about that. My brain was fuzzy from spending all day
> looking into logs and AWR reports while having people stop by my desk and
> IM and set up conference calls.
>
>
>
> Jay Miller
>
> Sr. Oracle DBA
>
> 201.369.8355
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *John Thomas
> *Sent:* Thursday, March 14, 2019 5:50 PM
> *To:* dmarc-noreply_at_freelists.org
> *Cc:* ORACLE-L
> *Subject:* Re: Keep buffer cache question
>
>
>
> Jay,
>
>
>
> Can I confirm you meant milliseconds (10^-3) not microseconds (10^-6)?
>
>
> Regards,
>
>
>
> John Thomas
>
> Database Designer and Administrator
>
> https://oracleexpert.net
>
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__oracleexpert.net_&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=2D0jZBl_Tlic3qxfNxpz8NfsE_t8u1Ntx0JC54Q7DNE&s=VQY68E1SAaCbd1_T9d-7ArDSHlv1aH7q_ZbGxY1mqrk&e=>
>
>
>
>
>
> On Thu, 14 Mar 2019 at 21:23, Redacted sender Jay.Miller for DMARC <
> dmarc-noreply_at_freelists.org> wrote:
>
> I should add that there has been no memory pool resizing since over 2
> months ago.
>
>
>
> Jay Miller
>
> Sr. Oracle DBA
>
> *From:* Miller, Jay
> *Sent:* Thursday, March 14, 2019 5:22 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* Keep buffer cache question
>
>
>
> 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.
>
>
>
> I am reluctantly considering adding the tables to the keep cache even
> though they are huge (4-5G_at_). They are frequently accessed so I don't see
> any real downside to this other than the huge amount of memory I would be
> reserving for them.
>
>
>
> We are going to test this in a non-production environment tomorrow but my
> two questions are:
>
>
>
> 1. Is anyone aware of any problems this might cause? We have not
> used the keep cache in the past.
>
>
>
> 2. Is there a way to preload the data once the db_keep_cache_size
> has been set, the sga increased to accommodate it, and the table assigned
> to the keep cache? A full table scan would usually bypass the buffer cache
> so I'm not sure of the best approach here.
>
>
>
> 3. Any other ideas on what might cause this?
>
>
>
>
>
> TIA!
>
>
>
>
>
> Jay Miller
>
> Sr. Oracle DBA
>
>

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