Re: Performance issue - Not sure if its disk or some thing - How can I narrow down this
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- To: contactarul@xxxxxxxxx
- Date: Tue, 4 Dec 2007 15:52:09 -0800
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 **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
