1 2 Previous Next 18 Replies Latest reply: Apr 24, 2012 3:58 AM by 932646 RSS

    db file sequential read

    609621
      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
        • 1. Re: db file sequential read
          Aman....
          Kais,

          You didn't format your code. Anyways,
          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.
          How much time period gap is there in this event waiting that you have mentioned. The waits are high on DBFSR but the there is not a long wait over here. Normally , DBFSQ comes up when there is an excessive user IO that is happening. But this wait event is going to be there on a well tuned system also. I would suggest to post the offending query's plan and statement over here( don't forget to use code tag) as jus these stats won't tell much.

          HTH
          Aman....

          Edited by: Aman.... on Jun 18, 2009 9:06 AM
          added comment.
          • 2. Re: db file sequential read
            sb92075
            Please let me know how to troubleshoot this issue
            Based upon what you posted, there is nothing to fix or troubleshoot.
            • 3. Re: db file sequential read
              26741
              You have to relate the 'db file sequential read' waits count 27,142 to the number of 'consistent gets' and the wait time of 7.17 seconds to the elapsed time of the operation/SQL (both ofwhich should also be in tkprof you generated).
              • 4. Re: db file sequential read
                105417
                As per first step towards optimization please rebuild indexes and also check table level fragmentation
                • 5. Re: db file sequential read
                  Aman....
                  vishwassamant wrote:
                  As per first step towards optimization please rebuild indexes and also check table level fragmentation
                  Care to explain that how come first step towards optimization would be "rebuilding of indexes" and what does rebuilding of index do to help in the DBSR wait event?

                  HTH
                  Aman.....
                  • 6. Re: db file sequential read
                    Randolf Geist
                    KaiS wrote:
                    48656 user SQL statements in session.
                    481 internal SQL statements in session.
                    49137 SQL statements in session.
                    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/
                    • 7. Re: db file sequential read
                      26741
                      A t ypical "pick and choose what I want to present" extract from the tkprof.

                      The waits are for the last SQL (hopefully it is a meaningful) with waits but no SQL statistics, the summary listing the number of SQL but not the total time are for the whole trace.

                      No way to correlate information and offer an opinion.
                      • 8. Re: db file sequential read
                        105417
                        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!
                        • 9. Re: db file sequential read
                          26741
                          defragmenting the table and index isn't always the solution.

                          Say you have a server with 8GB RAM and db_cache_size of 4GB.

                          Say you have 4 tables of 4GB each, each table having 3 indexes of 1.5GB each.

                          Say you have a 4 queries that join 2 to 3 tables and fetch at 40% of the rows from one table and 1% to 10% from the other tables.

                          Say you have 40 concurrent users running at least 10 such queries at any instant (or at least at the time the OP had "performance issues").

                          Say that you are working with a Real World Database. And then extrapolate the sizes and counts by 1x to 100x.
                          • 10. Re: db file sequential read
                            MarcinP
                            KaiS wrote:
                            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 :
                            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/
                            • 11. Re: db file sequential read
                              Aman....
                              vishwassamant wrote:
                              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!
                              So you mean to say that root cause of this event is due to fragmentation is it? Did you happen to see this link,
                              http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref720

                              If you have a doc link which does say the same that you have mentioned, I would be happy to see it.

                              HTH
                              Aman....
                              • 12. Re: db file sequential read
                                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.
                                • 13. Re: db file sequential read
                                  26741
                                  vishwas,

                                  don't take offence at our responses. There are situations where rebuilding indexes makes sense. Richard Foote and Jonathan Lewis have documented such cases (I've also put some on my blog http://hemantoracledba.blogspot.com ) However, in this instance here, that is absolutely the wrong way to begin. You are jumping to a conclusion with very scant information.
                                  • 14. Re: db file sequential read
                                    Jonathan Lewis
                                    vishwassamant wrote:
                                    As per first step towards optimization please rebuild indexes and also check table level fragmentation
                                    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).

                                    Please search the root clauses of db seq. scan and then comment. Fragmented table and indexes are the most freq. reason for this
                                    Wrong - bad execution plans, bad index design, and very large data sets are far more likely causes of "excessive" db file sequential reads.
                                    you have to do optimization for DB file sequential reads as per explain plan.
                                    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 {noformat}
                                    {noformat} (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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                    1 2 Previous Next