440,939 Members | 1,638 Online
Need help? Post your question and get tips & solutions from a community of 440,939 IT Pros & Developers. It's quick & easy.

# buffer pool hit ratio

 P: n/a Hi there, BP hit ratio = 1 - (BP physical reads / BP logical reads). If all the BP physical reads are asynchronous, it should mean that the pages are brought up to the bufferpool before the database manager needs them. But in that case, the BP hit ratio will be 0% (BP physical reads = BP async. physical reads = BP logical reads). Could this formula BP hit ratio = 1 - ((BP physical reads - BP async. physical reads) / BP logical reads) be more accurate or am I missing a point ? Thanks for your help, Eric Nov 12 '05 #1
7 Replies

 P: n/a eric wrote: Hi there, BP hit ratio = 1 - (BP physical reads / BP logical reads). If all the BP physical reads are asynchronous, it should mean that the pages are brought up to the bufferpool before the database manager needs them. But in that case, the BP hit ratio will be 0% (BP physical reads = BP async. physical reads = BP logical reads). Your formula is not correct, and I think that's what's causing your confusion. The total number of reads in the database is the sum of the logical and physical reads. Therefore, BP hit ratio = 100 * logical / (physical + logical). As you can see, if BP physical reads = 0, then the hit ratio is 100%. Good luck, -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- Nov 12 '05 #2

 P: n/a Thanks Ian, Your formula sounds more comprehensible to me. BP hit ratio = 100 * logical / (physical + logical). However, the formula BP hit ratio = (1 - (BP physical reads / BP logical reads))*100 was taken from the DB2 UDB HTML documentation. Can someone explain why this formula and not Ian's one ? Thanks, Eric "Ian" a écrit dans le message de news:3f**********@corp.newsgroups.com... eric wrote: Hi there, BP hit ratio = 1 - (BP physical reads / BP logical reads). > If all the BP physical reads are asynchronous, it should mean that the pages are brought up to the bufferpool before the database manager needs them. But in that case, the BP hit ratio will be 0% (BP physical reads = BP async. physical reads = BP logical reads). Your formula is not correct, and I think that's what's causing your confusion. The total number of reads in the database is the sum of the logical and physical reads. Therefore, BP hit ratio = 100 * logical / (physical + logical). As you can see, if BP physical reads = 0, then the hit ratio is 100%. Good luck, -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- Nov 12 '05 #3

 P: n/a (BP physical reads / BP logical reads) is there to measure the relation between the physical io's (disk access) vs the logical ones (buffered pages/in bufferpool). There are logical and physical io's. Physical io's can be split into sync (direct) and async io's (prefetch, etc.). logical physical sync physical async PM Nov 12 '05 #4

 P: n/a PM, If you take the (1 - (BP physical reads / BP logical reads)) * 100 formula in a DSS context, where let say you have many physical ios and 95% of them are asynchronous, the formula always leads you to a very weak hit ratio, for example: 1 - (500 / 600) = 16.7% [ 500*.95=475 async ios] Now, if you substract the Async IOs: (1 - (500 - 475)/ 600) * 100, your hit ratio seems better, = 95.8% Am I right to change the formula ? It seems to me it's adequate in OLTP environment, not DSS. Any additional thoughts ? Thanks, Eric "PM-pm3iinc-nospam" a écrit dans le message de news:an******************@news20.bellglobal.com... (BP physical reads / BP logical reads) is there to measure the relation between the physical io's (disk access) vs the logical ones (buffered pages/in bufferpool). There are logical and physical io's. Physical io's can be split into sync (direct) and async io's (prefetch, etc.). logical physical sync physical async PM Nov 12 '05 #5