Dear all,

Db : 11.1.0.6.0 on solaris sparc

We have a workflow process which is very slow for the past 1 week..when I ran trace for the WF session in the DB..I found the below :

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited

---

Waited

---

---

db file sequential read 27142 0.06 7.17
latch free 15 0.00 0.00
latch: session allocation 2 0.00 0.00
latch: cache buffers chains 17 0.00 0.00
gc cr grant 2-way 847 0.00 0.14
gc current grant busy 1 0.00 0.00
library cache lock 81 0.00 0.02
library cache pin 81 0.00 0.01
row cache lock 92 0.00 0.03
gc cr block 2-way 81 0.00 0.01
gc current block 2-way 10 0.00 0.00
db file scattered read 1 0.00 0.00
gc cr block busy 1 0.00 0.00

48656 user SQL statements in session.
481 internal SQL statements in session.
49137 SQL statements in session.

Please let me know how to troubleshoot this issue

Thanks in advance

Kai

Based upon what you posted, there is nothing to fix or troubleshoot.

As per first step towards optimization please rebuild indexes and also check table level fragmentation

Kai,

what seems to be odd that TKPROF says that there are 48656 user statements in the session... Why so many? Is it possible that your "workflow" process issues that many statements? That looks like "inefficient" processing if that's the case. Although I would expect to see some evidence of this in the wait events or in the summary showing the CPU/Elapsed time etc. May be you can post that part at the bottom of the TKPROF output starting with "OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS" and "OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS".

May be one of these statements started to use a less efficient execution plan and gets executed many, many times.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

Dear Aman,

Please search the root clauses of db seq. scan and then comment. Fragmented table and indexes are the most freq. reason for this

---

All others,

you have to do optimization for DB file sequential reads as per explain plan.

db file sequential read is a single-block read (i.e. data fetch by ROWID using index) means oracle seek in index and then fetch data from table using rowid. The user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return.

To decrease numbers in this event you have to look into index and table fragmentation. also look at no of rows scan in explain plan. For test case, take stats and then de fragment the Table & index and take stats. compare it!

Hello,

So we have some information about waits and what about CPU ?
Waits itself has to appear always so if ex. you query is executing in 10 min and you have a 1 min of waits its not bad.

Is it possible to start sar/top/other monitoring tool on server it self and measure if there is high CPU and IO load ?

regards,
Marcin Przepiorowski
http://oracleprof.blogspot.com/

Could you please explain what db sequential read has to do with 'table and index fragmentation'?
Could you please explain what 'table and index fragmentation' is in your book?
Could you please explain how often the height of an index is reduced (so the I/O is reduced) by rebuilding it?
Could you please explain why you are posting such unfounded rubbish?

---

Sybrand Bakker
Senior Oracle DBA

Experts: those who did read documentation.

This is a ridiculous suggestion - you have NO information whatsoever to suggest that rebuilding indexes will have any effect, or that there might be any problems with "table fragmentation" (in any of the meanings that people give to that term).

Wrong - bad execution plans, bad index design, and very large data sets are far more likely causes of "excessive" db file sequential reads.


But we haven't seen any execution plans yet, and you're already telling this person to rebuild indexes !

A couple of notes:

As Randolf has pointed out, there are 48,000 SQL statements in this trace file - if these are the wait statistics for the whole trace file and not just one statement, then this averages about half a read per statement, which could be very good when accessing a very large table with a high precision index.

As Hemant points out, we don't know much about the total time - the I/O wait time is only 7.17 seconds, but the OP is worried about performance. Consider, though, (a) 7.17 seconds for 27,000 single block reads is an average of 0.26 milliseconds - which is so fast that the reads are obviously coming out of a cache (probably the local file system). It makes you wonder how much CPU time has gone into trace file, and what the elapsed time is, and where the elapsed time went.

You can occasionally find cases where rebuilding indexes or repacking tables (or changing the data structures completely) is important - but suggesting either action without any evidence is far from sensible.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {code} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.

"Be very, very careful what you put into that head because you will never, ever get it out."
Cardinal Wolsey