Overall Read Milliseconds (ORMS)

ORMS = (Total buffer pool read time (milliseconds) / (Buffer pool data physical reads + Buffer pool index physical reads + Buffer pool temporary data physical reads + Buffer pool temporary index physical reads))

ORMS tells us the average time for DB2 to complete a physical read. It should be computed for the database (use 'dbsnap2.txt') and for each tablespace (use 'tssnap2.txt'). The Performance Hero DBA should compare the ORMS for the database against the ORMS for each tablespace. If any tablespaces have read times significantly higher than the average for the database, then it is important to determine why and attempt to improve the performance of the slowest tablespaces.

Why might a tablespace be significantly slower than the database overall? Glad you asked. Make sure the database tablespace definitions and containers adhere to tablespace best practices:

• If possible, tablespaces should use multiple containers on different devices.
  
• Containers should have equal sizes
  
• The PREFETCHSIZE for the tablespace should be 3-4 times the EXTENTSIZE
  
• Containers should not be placed on devices containing OS paging space or other extremely busy devices
  
• DMS tablespaces tend to perform 5-10% faster than SMS tablespaces, but DMS is more difficult to administrate unless you are taking advantage of Automatic Storage
  
• Use Automatic Storage with multiple data paths
  

If you are using DB2 9 (if not, why haven't you upgraded yet?), computing ORMS is easy using SQL snapshots...

To compute ORMS for the Database:

• db2 “select (POOL_READ_TIME / (POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + 1) as ORMS from sysibmadm.snapdb where db_name = ‘DBNAME’”
  

To compute ORMS for each tablespace and find the top 10 slowest tablespaces, use this handy query:

• db2 “select tbsp_name, (POOL_READ_TIME / (POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + 1) as TSORMS from sysibmadm.snaptbsp order by TSORMS desc fetch first 10 rows only”
  

While ORMS tells us about average read times, it is also valuable to know how much time, on average, each transaction spends on bufferpool read time. For this, we need to look at BPRIOTX (Bufferpool Read I/O per Transaction).

Bufferpool Read I/O ms per Transaction (BPRIOTX)

BPRIOTX = (Total buffer pool read time (milliseconds) / (Commit statements attempted + Rollback statements attempted))

Bufferpool read time is just one important component of understanding where transaction time goes. In future blog posts, we'll also look at bufferpool write times, direct I/O times, lock times, sort times, and CPU times. Once we know where time is spent inside the database, then we can focus on the resource that is the greatest bottleneck to optimized performance. We'll also look at determining average transaction times, and how much time, and what percent of time, is spent inside the database and out.

The Shameless Marketing Moment

Have you seen the movie Evan Almighty yet? I thought it was very good. How do you change the world? One Act of Random Kindness at a time... For our loyal blog readers, DBI is offering a FREE Webinar on Recursive SQL on November 27th - learn how to do magical things with recursive SQL and achieve SQL miracles at optimum performance. DBI's Alexander Kopac will be your host. Alexander is also sharing SQL tips in his new blog at http://www.Database-Brothers.com/blog/AlexanderKopac.php -- please visit DBI's website to register for the Recursive SQL Webinar.

Just for Fun

Have you pre-ordered Phil Gunning's newest book "DB2 9 For Developers" yet? You can save 37% off the cover price by pre-ordering and save over $24!

Until next time,
Cheers,
Scott

Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.Database-Performance.info
www.Database-Auditing.info