Received: (qmail 13415 invoked from network); 12 Dec 2010 17:15:34 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.intergenia.de with SMTP; 12 Dec 2010 17:15:21 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ACF9BD56D1E; Sun, 12 Dec 2010 18:15:19 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1292195719; bh=xxBlLkl7itCMjmblwAlssiuaTqc7PMrCROgbUfUr 3uY=; h=MIME-Version:In-Reply-To:References:Date:Message-ID:Subject: From:To:Cc:Content-Type:Sender:Reply-To:List-help:List-unsubscribe: List-Id:List-subscribe:List-owner:List-post:List-archive; b=TjLMmy ntLat1UmLqi4jtoQF7ARydz7jBXyudjI5sUoF//lQnV2wMUoKph4BDm7iF5YnZ0INx6 FDBcEVvwY2e3ckDxgRHcLNN8iO00E3l5PJr7NPpD/5mvX+xUisWx91DDgYyORazNfoe Gn0JLPmyihwcJdXriWi9lDBBEqMyv/U= X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id nSozXbxKY5+P; Sun, 12 Dec 2010 18:15:19 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 96ACAD56C33; Sun, 12 Dec 2010 18:14:36 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Sun, 12 Dec 2010 18:13:55 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B891AD5621A for ; Sun, 12 Dec 2010 18:13:54 -0500 (EST) Authentication-Results: turing.freelists.org; dkim=pass (1024-bit key) header.i=@googlemail.com Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id r+0FYo4gOkRB for ; Sun, 12 Dec 2010 18:13:54 -0500 (EST) Received: from mail-iw0-f181.google.com (mail-iw0-f181.google.com [209.85.214.181]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2DBABD55563 for ; Sun, 12 Dec 2010 18:13:53 -0500 (EST) Received: by iwn3 with SMTP id 3so8438262iwn.12 for ; Sun, 12 Dec 2010 15:13:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type; bh=+JnZ7bKXPMkSM2mQCIAnELXEM9MLvWXVOAi//xfdKTQ=; b=t8UysTEGq/cBcDoOKU5GQvN52GZ6iKg5mRUhrH8rberRRNl2wOhcpU5+CCPIJmjbeI 2nwMJ6/0cE/zrzHZvm7NNtOnOOkr/5l3ovG/sNKoEcycTXB94tGn1J5tyXK1RDp/+CBx NkSd1jEek3A0hl3wTaGqgI7thToRRnsnOH+5A= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=f9NB4dZvZVEv0GO9ghJ6SFs2hxSo447cYHjf0gZFnnMHg1EyT+mIodq72XvQRwn91A fHSHy0cMxXeGu5+ho/WHMHJ3UvVp1OAGQbZFQVB/LlC+dJ3tXF83bBPJ9eeowomFVmd7 3gbLYboJvWLhCOdOykQVwC0+JYUtEMmusNhPk= MIME-Version: 1.0 Received: by 10.231.206.80 with SMTP id ft16mr1343863ibb.110.1292195633690; Sun, 12 Dec 2010 15:13:53 -0800 (PST) Received: by 10.231.155.142 with HTTP; Sun, 12 Dec 2010 15:13:53 -0800 (PST) In-Reply-To: <20101210151059.60871.qmail@optimaldba.com> References: <20101210151059.60871.qmail@optimaldba.com> Date: Sun, 12 Dec 2010 23:13:53 +0000 Message-ID: Subject: Re: why did query behavior change ? From: David Roberts To: oracle-l@freelists.org Cc: eglewis71@gmail.com Content-Type: multipart/alternative; boundary=90e6ba53a6a2b9f8a704973ebd87 X-archive-position: 33220 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: big.dave.roberts@googlemail.com Precedence: normal Reply-To: big.dave.roberts@googlemail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --90e6ba53a6a2b9f8a704973ebd87 Content-Type: text/plain; charset=ISO-8859-1 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 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@optimaldba.com> > To: > 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 -- >> http://www.freelists.org/webpage/oracle-l >> >> >> > -- > http://www.freelists.org/webpage/oracle-l > > > -- > http://www.freelists.org/webpage/oracle-l > > > --90e6ba53a6a2b9f8a704973ebd87 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable This may fall=A0perilously=A0close to a guess, but if we accept that the ex= plain 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 g= rown to=A0exceed=A0the small table threshold which is normally 2% of the si= ze of the buffer cache. One of the difference between Oracles behaviour bet= ween 'small' and 'big' tables is that Full table scans of s= mall tables place the blocks at the head of the buffer cache MRU list rathe= r 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= =A0likely=A0to keep a far higher proportion of it's blocks in cache tha= n a big table and a small table that grows enough to become a big table cou= ld exhibit a similar change in behaviour to the one you described and if ca= ching was switched on on the newly big table the performance would=A0likely= =A0revert back to it's previous behaviour.

Check to see if the size of the table could fit this sc= enario or if the size of the buffer cache may have changed during the perio= d in question.

Dave

On Fri, Dec 10, 2010 at 3:10 PM, Daniel Fink <daniel.fink@optimaldba.com> wrote:
Without having a baseline, it is very difficult to determine what changed a= nd thus what was the possible cause. It would be good to gather some sort o= f 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 s= ee 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@optimaldba.= com>
To: <oracle-= l@freelists.org>
Sent: Wednesday, December 08, 2010 5:48 PM
Subject: RE: why did query behavior change ?


Did this impact *every* query accessing this table? =A0Yes
What were the differences in the execution plans? =A0 =A0None
What were the differences in the execution profiles (changes in physical i/= o, consistent gets, cpu time, wait time)?
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Don't have that in= fo.
When were statistics last gathered on the table? =A0within the last week Were there any systemic changes? =A0 =A0 =A0None that I am aware of.

------- Original Message -------
On 12/8/2010 10:22 PM ed lewis wrote:
Hi,
=A0 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.

=A0I did run 1 problem query interactively to get the explain plan. The que= ry did a table lookup
via an index, and it returned 165 rows. =A0The "consistent gets" = though were around 1 million.
This is a 3rd party app, so what we can do with the query itself is limited= .

=A0 The temporary workaround was to cache the table. This brought
the response time back to normal. =A0By caching the table, I assume the tab= le will be
handled differently in cache.The better results show that.

=A0 But why did the query behavior worsen ? =A0 I am trying to track down = the root cause.

=A0 Any ideas are welcome.


=A0 thanks =A0 =A0 =A0 =A0ed

=A0solaris 10
oracle 10.2.0.4 =A0- 2 node cluster-
asm 10.2.0.4
clusterware 10.2.0.4 =A0 =A0--
htt= p://www.freelists.org/webpage/oracle-l



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