Re: Performance issue - Not sure if its disk or some thing - How can I narrow down this

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