This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jan 15, 2013 1:19 AM by Jonathan Lewis Go to original post RSS
  • 15. Re: db file sequential read and direct path read
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks everyone for sharing knowledge.

    Rgds,
    Vijay
  • 21. Re: db file sequential read and direct path read
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points