Re: why did query behavior change ?
- From: "Daniel Fink" <daniel.fink@xxxxxxxxxxxxxx>
- To: eglewis71@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 10 Dec 2010 10:10:59 -0500
Without having a baseline, it is very difficult to determine what changed and
thus what was the possible cause. It would be good to gather some sort of
baseline metrics/execution profiles on the key statements while they are
performing well. This will help you diagnose if this issue arises again.
Are you licensed for ASH/AWR? If so, you can use the awrsqrpt and data to see what changed.
------- Original Message -------
On 12/10/2010 1:46 PM ed lewis wrote:
Daniel thanks for the response.
see below.
----- Original Message -----
From: "Daniel Fink" <daniel.fink@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, December 08, 2010 5:48 PM
Subject: RE: why did query behavior change ?
Did this impact *every* query accessing this table? Yes
What were the differences in the execution plans? None
What were the differences in the execution profiles (changes in physical
i/o, consistent gets, cpu time, wait time)?
Don't have that info.
When were statistics last gathered on the table? within the last week
Were there any systemic changes? None that I am aware of.
------- Original Message -------
On 12/8/2010 10:22 PM ed lewis wrote:
Hi,
You just recently experienced a performance issue with queries
accessing a particular table. Response time went from 15-20 seconds,
to 2-3 minutes.The table has around 500,000 rows, and is a "core" table
in the app.
I did run 1 problem query interactively to get the explain plan. The
query did a table lookup
via an index, and it returned 165 rows. The "consistent gets" though were
around 1 million.
This is a 3rd party app, so what we can do with the query itself is
limited.
The temporary workaround was to cache the table. This brought
the response time back to normal. By caching the table, I assume the
table will be
handled differently in cache.The better results show that.
But why did the query behavior worsen ? I am trying to track down the
root cause.
Any ideas are welcome.
thanks ed
solaris 10
oracle 10.2.0.4 - 2 node cluster-
asm 10.2.0.4
clusterware 10.2.0.4 --
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
