Buffer Cache Size and CBO Calculations?

From: "Larry G. Elkins" <elkinsl@xxxxxxxxx>
To: "'Oracle-L'" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 16 Apr 2009 18:53:00 -0500
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@xxxxxxxxx


--
//www.freelists.org/webpage/oracle-l
Buffer Cache Size and CBO Calculations?

Other related posts:
