Re: why did query behavior change ?
- From: David Roberts <big.dave.roberts@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 12 Dec 2010 23:13:53 +0000
This may fall perilously close to a guess, but if we accept that the explain
plan is unchanged, then the number of explanations is more limited.
Also if by caching the table, performance returns to 'normal' then there is
a possibility that the table could have simply grown to exceed the small
table threshold which is normally 2% of the size of the buffer cache. One of
the difference between Oracles behaviour between 'small' and 'big' tables is
that Full table scans of small tables place the blocks at the head of the
buffer cache MRU list rather than at the end as is the case with a full
table scan of a large table.
Thus a small table that experiences full table scans is likely to keep a far
higher proportion of it's blocks in cache than a big table and a small table
that grows enough to become a big table could exhibit a similar change in
behaviour to the one you described and if caching was switched on on the
newly big table the performance would likely revert back to it's previous
behaviour.
Check to see if the size of the table could fit this scenario or if the size
of the buffer cache may have changed during the period in question.
Dave
On Fri, Dec 10, 2010 at 3:10 PM, Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>wrote:
> 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:
