Meaning of online report values IO_WTIME and ORIO_WTIME within Detector
Article ID: 218539
Updated On:
Products
Issue/Introduction
High number of IO_WTIME and ORIO_WTIME seen on the Detector for Db2 for z/OS (PDT) Dynamic SQL Detail.
What do they mean? Class 3 suspension time ?
20.0 ---- DETECTOR Key Pgm Dynamic SQL Detail Display --- mm-dd-yy hh:mm
Command ==> Scroll ==> CSR
DB2 SSID ==> ssid Type ==> PACKAGE Program ==> DSNCLINF
Collid ==> DSNAOCLI
Version ==> UI67953
Total/Avg => A Userid ==> authid
Interval Date => mm-dd-yy Interval Time => hh:mm:ss Elapsed Time => hh:mm
-------------------------------------------------------------------------------
Wait Times And Counts
IO_WTIME -> 00:02.695179 IOWCNT -> 14158.51
LOCK_WTIME -> 00:00.000000 LOCKWCNT -> 0.00
ORIO_WTIME -> 00:01.545885 ORIOWCNT -> 5412.38
OWIO_WTIME -> 00:00.000000 OWIOWCNT -> 0.00
SERV_WTIME -> 00:00.000000 SERVWCNT -> 0.00
ARCH_WTIME -> 00:00.000000 ARCHWCNT -> 0.00
LATCH_WTIME -> 00:00.003422 LATCHWCNT -> 500.24
PLATCH_WTIME -> 00:00.000532 PLATCHWCNT -> 145.35
DRAIN_WTIME -> 00:00.000000 DRAINWCNT -> 0.00
CLAIM_WTIME -> 00:00.000000 CLAIMWCNT -> 0.00
ARCRD_WTIME -> 00:00.000000 ARCRDWCNT -> 0.00
SMSG_WTIME -> 00:00.000000 SMSGWCNT -> 0.00
Cause
The related Db2 Bufferpool may be too small.
Resolution
IO_WTIME and ORIO_WTIME are wait times for physical I/O.
IO_WTIME is the wait time for I/O performed by the thread that is executing the SQL statements. This is synchronous I/O.
A value in IO_WTIME indicates that threads executing the SQL statements had to wait for synchronous I/Os to complete.
ORIO_WTIME is the wait time for I/O performed by another thread on behalf of the thread that is executing the SQL statements. This is asynchronous I/O.
This is I/O that is done in advance (prefetch) of the thread that is executing the SQL statements actually requesting/needing the data.
The intent is to have the data buffered before the SQL statement needs it thus avoiding a delay. A value in ORIO_WTIME indicates that the
prefetching of the data had not completed before the threads executing the SQL statement needed it and it had to wait.
Regarding a class 3 suspension time. The Db2 trace facility has different trace types each of which has different classes. A trace started for
the ACCTG trace type class 3 tells Db2 to collect metrics for “Elapsed wait time in DB2.”, this is suspension time.
Additional Information
See the Db2 START TRACE command in the DB2 Command reference for further details.
The online help for this value:
Field: IO_WTIME
Description: The total or average synchronous read I/O wait time incurred by the plan, program, SQL call, or application while the program or user was executing. Synchronous reads are performed under the thread TCB. As a result, application activity will suspend until the necessary physical I/O is complete. This field requires Accounting Class 3 to be active. If you are viewing the Thread Package/DBRM Display or the Thread Pkge/DBRM Detail Display, this field requires Accounting Class 8 or Monitor Class 8 to be active."
Field: ORIO_WTIME
Description: The total or average other read I/O wait time incurred by the plan, program, SQL call, or application while the program or user was executing. Other read I/O wait time originates from read I/O requests performed by a DB2 read engine on behalf of the thread. Examples include sequential prefetch and list prefetch activity. This value indicates the amount of time the thread spent waiting for asynchronous I/O activity to complete so it could continue necessary thread processing.
Feedback
