1 2 Previous Next 21 Replies Latest reply: Jan 15, 2013 3:19 AM by Jonathan Lewis Go to original post RSS
      • 15. Re: db file sequential read and direct path read
        Jonathan Lewis
        Mohamed Houri wrote:

        That's very nice. I learned something new today: undo blocks can also be read via db file sequential read.

        And does the db file sequential read on undo blocks mean an access via index too? If yes then what kind of indexes are they????
        Mohamed,

        When a session needs to read an undo block it's (usually) searching for an undo record that has been identified by the "undo record address" in the ITL entry. This address gives the file number, block number and record within block, so Oracle can go directly to the correct block by block address.

        The other common reasons for reading undo blocks (rollback, transaction table rollback) also specify undo record addresses; and if you have flashback enabled and your session needs to read an undo block to put it in the flashback log before "new"ing it, it's got the block address from information in the undo segment header.

        Regards
        Jonathan Lewis
        • 16. Re: db file sequential read and direct path read
          914789
          Hi Alexander and Everyone,

          Thank you for giving insights into oracle internals and on diagnosing the tkprof.

          The application team is complaining that query is taking 0.18 seconds in test where as 4.88 seconds in prod.

          Though I now know that:
          1- In test database it were direct path reads that made query faster.
          2- In prod database there were more db file sequential reads that made query slower.

          The current issue of db file sequential read is because of row-chaining.
          But let say if the query goes for a db file sequential read, then can i make below statement:
          Time taken by query = ( no. of disk reads performed ) * ( average wait time taken for 1 disk read ) + ( misc time e.g. cpu,etc)
          
          where,
          the average wait time for 1 disk read =  total waited / times waited = 3.71/2425  = 1.5 milliseconds 
          ==>  and is this 1.5 milliseconds the latency of storage ? 
          
          Event waited on                             Times   Max. Wait  Total Waited
            ----------------------------------------   Waited  ----------  ------------
          db file sequential read                      2425        0.05          3.71
          Using above formula, for 3 different runs of same query at different timings i found the latency to be 0.3, 1.5 and 3.5 milliseconds.


          If this is the case then time taken by query could only be reduced if application tries to minimize the disk reads.
          Or if storage team tries to reduce the latency.



          Rgds,
          Vijay
          • 17. Re: db file sequential read and direct path read
            914789
            Hi Jonathan,

            Used below method to find the chained row for this table in production.
            SQL> select count(*) from smprd.probsummarym1;
            
              COUNT(*)
            ----------
                 63308
            
            SQL>  analyze table smprd.probsummarym1  list chained rows;
            
            Table analyzed.
            
            SQL> select count(*) from chained_rows;
            
              COUNT(*)
            ----------
                  4777
            Below is the result in test database.
            SQL> select count(*) from smprd.probsummarym1;
            
              COUNT(*)
            ----------
                 62343
            
            SQL> analyze table smprd.probsummarym1  list chained rows;
            
            Table analyzed.
            
            SQL > select count(*) from chained_rows;
            
              COUNT(*)
            ----------
                   368
            Rgds,
            Vijay
            • 18. Re: db file sequential read and direct path read
              jgarry
              Just to be complete, check your instances for setting of serialdirect_read or event 10949. Google for those, some people set them to change the direct read effect for some older software or testing.
              • 19. Re: db file sequential read and direct path read
                914789
                Hi Jgarry,

                The parameter serialdirect_read is not set.

                Thanks All, once again for sharing knowledge.

                Rgds,
                Vijay
                • 20. Re: db file sequential read and direct path read
                  914789
                  Thanks everyone for sharing knowledge.

                  Rgds,
                  Vijay
                  • 21. Re: db file sequential read and direct path read
                    Jonathan Lewis
                    911786 wrote:
                    Hi Jonathan,

                    Used below method to find the chained row for this table in production.
                    4777
                    Below is the result in test database.
                    368
                    It's not proof that chained rows are the problem, of course, but it fits the hypothesis.
                    You could create a copy of the table (with the correct indexes) to see if the act of rebuilding the table gets rid of the chained rows and changes the performance of the query.
                    If so you then need to figure out why those rows have become changed (perhaps all you need is a more appropriate setting for pctfree) and see if you can implement a one-off change to address the problem.

                    Regards
                    Jonathan Lewis
                    1 2 Previous Next