GET PAGES/SYNCH I/O RATIO

cass cheng

GET PAGES/SYNCH I/O RATIO

Dear Listers, I seen in the recent days about death by random I/O.  While co realting the same with my problem I had seen that one query was behaving very badly and after tuning it worked okay.  Here is the instances for this. Before tuning the ratio of SYNC read I/O to Number of GETPAGES was high. After tuning the query the ratio decreased to one 10th of the earlier value. Now I ask you all to answer and favor me. Was this the cause that WAIT time in DB2 was high.  I mean the Ratio of GETPAGES/SYNC I/O is more means more DB2 I/O wait. If this is true what has caused this.

Second question.  I had seen that the SYNC READ WAIT and ASYNCH read I/O wait was more for the query. Out of these which causes more Db2 I/O wait. Cost wise which is cheaper whether SYNC READ I/O wait or ASYNCH read I/O wait.

I would wait for your reponses.

With thanks

 

 

 

--

___________________________________________________________
Sign-up for Ads Free at Mail.com
http://www.mail.com/?sr=signup

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Venkat Srinivasan

Re: GET PAGES/SYNCH I/O RATIO
(in response to cass cheng)
The cause is usually bad access path / pool placement.
Poor cluster ratio, low cardinality on matching columns, improper join
sequence, access by a fat index etc are usual candidates.

Sync read is bad because you are reading page by page. When the thread is
waiting too much for I/O to complete, it could exacerbate into other
problems in a busy system. I/O may have been signaled complete. The thread
is dispatched again. The page should be in the buffer. It may have been
stolen already. You end up doing I/O again.

Sync wait time usually will be excessive than the Async wait time. Sync
wait time may also be an indirect indicator for DASD performance.

It is almost impossible to say which is cheaper. Obviously no wait is the
cheapest. It depends on the application and the nature of SQL and the
extent of query optimization.

Async read wait if it exists, it may increase the number of sync reads.
Dynamic prefetch, while reading ahead the pages that the app does not yet
want will increase the async wait time.

Due to the nature of async processing (You expect all pages to be used in
the application during a typical async operation), a high wait is one of
the first indications of improper access path.

A few sync reads may just be usual and normal but Async wait is not.
The remedy is a SQL tuning followed by proper pool placement.

!!!!A good I/O is no I/O!!!!!

Regards,
Venkat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm