Buffer Cache Size and CBO Calculations?

From: Larry G. Elkins <elkinsl_at_flash.net>
Date: Thu, 16 Apr 2009 18:53:00 -0500
Message-ID: <000f01c9beee$79ef7580$6dce6080$_at_net>

9.2.0.6 EE 64bit AIX 5.3

Folks,

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 9.2.0.6 EE 64 bit on AIX 5.3

Larry G. Elkins
elkinsl_at_flash.net

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 16 2009 - 18:53:00 CDT