RE: Keep buffer cache question
- From: "Jeffrey Beckstrom" <jbeckstrom@xxxxxxxxx>
- To: "'dmarc-noreply@xxxxxxxxxxxxx'" <dmarc-noreply@xxxxxxxxxxxxx>, <xt.and.r@xxxxxxxxx>
- Date: Tue, 19 Mar 2019 11:56:47 -0400
I will second this. We had some cases where suddenly a given program's
performance became worse. We do stats weekly. In our case, the execution
plan had changed. The only thing different was row volume - no program
changes. We ran the tuning the advisor and created a profile since this
was an Oracle supplied program we could not change. In another case, we
baselined a working plan in one system and transferred it to the "bad"
system.
Jeffrey Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
"Reen, Elizabeth " (Redacted sender"elizabeth.reen" for DMARC)
<dmarc-noreply@xxxxxxxxxxxxx> 3/19/19 11:38 AM >>>
There is an invisible line at which
performance changes. I have found that it only takes a couple more
records to go over the line and have been stuck many times explaining
exactly what you are seeing. A few dumb questions;
How current are your stats?
Does the table have a lot of deletes? If so rebuilding the indexes
might help.
Is everything in the same tablespace? Separating indexes from tables
occasionally helps.
What is the sql doing? I cured one issue by having them rewrite the
sql. They were doing a max on a date column where date is less than
sysdate. Of course the table was not archived or partitioned at the
time. So they were doing a full table scan on a 200 gig table.
The idea is to bring back the least amount
of data possible. It may be time to add another index or to change one.
Look at the disks not memory, reducing time spent there is always
good.
Elizabeth Reen
From: [freelists.org] oracle-l-bounce@xxxxxxxxxxxxx
<oracle-l-bounce@xxxxxxxxxxxxx>On Behalf Of [freelists.org]
dmarc-noreply@xxxxxxxxxxxxx
Sent: Friday, March 15, 2019 11:02 AM
To: xt.and.r@xxxxxxxxx; dmarc-noreply@xxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Keep buffer cache question
Yes, index and table blocks. No DDL for quite a while and no large DML
that I’ve been able to uncover.
No memory pool resizing since January 9, 2019.
Jay Miller
Sr. Oracle DBA
201.369.8355
From:oracle-l-bounce@xxxxxxxxxxxxx
[
mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sayan Malakshinov
Sent: Thursday, March 14, 2019 6:06 PM
To: dmarc-noreply@xxxxxxxxxxxxx
Cc: ORACLE-L
Subject: 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@xxxxxxxxxxxxx> 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
(
https://urldefense.proofpoint.com/v2/url?u=http-3A__orasql.org&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=y-UF_JpJGTABLm5TmUHndm9ytZLICmx2FNZGBXJNiWY&s=rGHmLD6iEx3nrKyoWoywLbo35y00PQ7_jfalmKciyNE&e=)
Other related posts:
