DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=oDe5rcpW0w0B+hRsGJMX5OpDANxDIbZ6xfxrAAPJlrgR/g/mwLAY9TM4SxJDZK7Igm0WcVPe9CwzmxryEWb/p9FsN4u3imiQZGqLofChQbFrKcIDekorMsLlkzD2U7oM45vUbXo1msm51rzQAOqOuqPDrCfNBmK9AVHHaqISeAo=
Message-ID: <2ead3a60712041552p5a9a47b2pbeb3aa7574b655f5@mail.gmail.com>
Date: Tue, 4 Dec 2007 15:52:09 -0800
From: "John Kanagaraj" <john.kanagaraj@gmail.com>
To: contactarul@gmail.com
Subject: Re: Performance issue - Not sure if its disk or some thing - How can I narrow down this
Cc: bnsarma@gmail.com, oracle-l <oracle-l@freelists.org>
In-Reply-To: <1c1a62990712041050o271ea216jbea4d2742dd360e0@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Disposition: inline
References: <61292a9d0712040913p169e8ecbv8d648840269b7dd9@mail.gmail.com>
	 <1c1a62990712041050o271ea216jbea4d2742dd360e0@mail.gmail.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: john.kanagaraj@gmail.com

BN,

Keep in mind that STATSPACK has a deficiency in the way Top SQL is
collected. The package scans V$SQL using a query that has  a fixed
number of Buffer gets, Disk-reads, etc. that is previously configured.
If you have pinned packages/cursors and have not restarted the Db in a
while, you will collect SQL that is executed a large number of times
for functional 9and valid reasons) that are really NOT the issue. (I
deal with this topic in detail in my papers and in my book)

>  2. Pick the top few sqls and their hash value from statspack based on "Gets
> per Exec" ( you can also compare statspack for a previous day/similar time
> when the system was healthy )

As for your disk issue, I wouldn't really depend on 'sar -d'. On
modern systems, there are just too many layers inbetween Oracle and
the final disk (see previous post). If you want to, I would rather
look at the Tablespace/File level I/O as recorded in STATSPACK and
look for oddities therein.

Finally, of course, you will need to look at Business critical SQL and
trace them with 10046 to really determine the issue.

-- 
John Kanagaraj <><
DB Soft Inc
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
http://www.freelists.org/webpage/oracle-l