Buffer Cache Size and CBO Calculations?
Date: Thu, 16 Apr 2009 18:53:00 -0500
220.127.116.11 EE 64bit AIX 5.3
Under what circumstances can the buffer cache size come into play with CBO calculations? I don't recall seeing any reference to the *size* of the cache playing a role in determining the plan other than a reference in one of Wolfgang's papers (OICA and OIC paper) where, in conjunction with the use of OIC (optimizer index caching), "there is a cap, or rather barrier, below which the costs do not fall. This barrier only comes into view when the indexes, #LB, exceeds the buffer pool size". It looks like I may have run into this.
I was asked to look at a UAT database being setup to mimic production, with a copy of PROD data, CPU costing *not* used, stats copied over using DBMS_STATS, and all CBO parameters, as reported by a 10053 trace, having the same value. They have OIC set to 90. But, I discovered PROD has a 5 gig buffer cache, UAT 5 MB. The same query run against PROD and UAT to sanity check the UAT environment gave a different plan. 10053 trace shows the following in a snippet for a nested loops join (and we see similar all throughout the trace for that query). Note 5 lines down, the difference in RSC_IO, and then eventually the Best NL cost value:
PROD - 5 Gig Cache UAT - 5 MB Cache OPTIMIZER PERCENT INDEX CACHING = 90 OPTIMIZER PERCENT INDEX CACHING = 90 Access path: index (index-only) Access path: index (index-only) Index: TAB1_IDX Index: TAB1_IDX TABLE: TAB1 TABLE: TAB1 RSC_CPU: 0 RSC_IO: 15 RSC_CPU: 0 RSC_IO: 145
<<<< Note RSC_IO
IX_SEL: 2.8011e-03 TB_SEL: 2.8011e-03 IX_SEL: 2.8011e-03 TB_SEL:
2.8011e-03 Join: resc: 5 resp: 5 Join: resc: 37 resp: 37 Best NL cost: 5 resp: 5 Best NL cost: 38 resp: 37
<<<< And the result on cost
Note that when UAT was increased to a 5 gig buffer, the difference went away. In fact, doing a diff on the 10053 portions from the two environments, they were exactly the same after the increase in buffer cache in the UAT environment.
So this raises the question of under what other circumstances can the cache size impact the *calculations*, and thus the plan. Maybe I've just totally missed such discussions/papers where this is noted. Note this is 18.104.22.168 EE 64 bit on AIX 5.3
Larry G. Elkins