Hello, I've been plugging a lot of the well-known DB2 performance formulas into a spreadsheet to analyze performance, and I'm a little unsure of the relevance of the async read percentage (ARP) formula. As I understand it, the formula is (POOL_ASYNC_DATA_READS + POOL_ASYNC_INDEX_READS) / (POOL_DATA_P_READS + POOL_INDEX_P_READS), and a high percentage is supposed to denote an object that sees a lot of sequential scans. Articles I've read warn that in an OLTP environment, a high ARP is a potential indicator of queries that scan a lot, either because of inefficient SQL or poor indexing. If that is true, I can't say I completely agree, especially on a database like mine, which is used for both reporting and OLTP. Here are some relevent numbers that I think will assist in my explanation: POOL_ASYNC_DATA_READS=104109 POOL_ASYNC_INDEX_READS=13187 POOL_INDEX_P_READS=148458 POOL_INDEX_L_READS=27483831 POOL_DATA_P_READS=171903 POOL_DATA_L_READS=5195306 To me, these numbers indicate an object that sees a ton of synchronous buffer pool hits, since logical reads exceed physical reads by as many as two orders of magnitude in some areas. However, ARP calculates out to 36% for the above numbers, which initially led me to believe I supposed to Do Something to reduce ARP. In other cases, ARP was as high as 50%, so you might understand how relying on ARP alone could cause me to worry. Nowadays, I don't look at ARP by itself anymore, but instead view it in the context of logical reads as well. The numbers above show extremely high buffer pool hit ratio, and in the very rare event that disk access is necessary, it's only caused by prefetching about a third of the time. Does everyone agree? Please let me know if I'm missing something important. Fred Sobotka IBM Certified Database Administrator - DB2 UDB V8.1 for Linux, Unix, and Windows CollegeNET, Inc. - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc
