Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Tuticorin, India

    Question Unanswered: Sync Vs Async Read

    Which is more important for best performance in an OLTP and DW system ?

    Few blog suggests that Synchronous I/O is best since it scans only the relevant page based on in
    DBI :: Brother-Eagle™ Advice :: DB2 Sync Read Percent

    While few other blog suggests that Async I/O is better as in
    Asynchronous Read activity | db2ude

    Any thoughts based on your experience in prod systems ?

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    If you read the DBI link closely, high Sync is good for OLTP, where high async is good for DW.


  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Quote Originally Posted by Brother-Eagle™ Advice

    When DB2 has to scan many pages to find result sets, usually because indexes are missing or sub-optimally defined, DB2 uses prefetch I/O which is performed asynchronously. When the percentage of asynchronous I/O is high, then synchronous I/O is low.

    When the Synchronous Read Percentage is low, DB2 is doing a lot of scanning to find result sets. Scans occur when indexes are missing or are sub-optimally defined.
    I think the statement above is over-simplified to the point where it becomes incorrect. In my view asynchronous I/O is good, unless the "time waited for prefetch" and "unread prefetch pages" are high compared with the read time and the total number of prefetched pages respectively. The ratio of rows selected to rows read is often a better indicator of index access (in)efficiency.
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts