Re: cache control?

Simon,

thanks for the time to give this further thought.


Simon Riggs wrote:
> If we know ahead of time that a large scan is going to have this effect,
> why wait for the ARC to play its course, why not take exactly the same
> action?
> Have large scans call StrategyHint also. (Maybe rename it...?)...of
> course, some extra code to establish it IS a large scan...
> ...large table lookup should wait until a shared catalog cache is
> implemented

The problem with this is a) how to detect that something will be a large 
scan, and b) how to decide what is a large scan in the first place.

Large sequential scans in warehousing are often part of more complex 
join operations. And just because something returns a large number of 
result rows doesn't mean that the input data was that much.

As for the definition of "large" itself, this depends on the size of the 
buffer cache and the access pattern of the application. As you surely 
have noticed, the usual sizes of B1+T1 = T2+B2 = C in the algorithm. 
Buffers evicted from T1 are remembered in B1, and because of that even 
repeated sequential scans of the same large relation will only cycle 
through T1 blocks, never cause any turbulence in T2 or B2.

The only thing that will affect T2 and B2 dramatically by adjusting the 
cache split point is multiple scanning of more than one significantly 
large but smaller than C table. Scanning the same large but smaller C 
table over and over will have it after the second scan in T2, where it 
belongs. But having two tables A and B that are both just smaller C and 
having an access pattern like A, A, B, B, A, A, ... will cause many B1 
hits and thereby increase the target T1 size. And it must be exactly 
that access pattern, because A, A, A, B, B, B, A, A, A, ... produces a 
complete MISS on the first, a B1 hit on the second and a B2 hit on the 
third scan, so it will up and down the split point evenly.

Honestly, I don't even know what type of application could possibly 
produce such a screwed access pattern. And I am absolutely confident one 
can find corner cases to wring down Oracles complicated configuration 
harness more easily.

>  
> Anyway, this idea can wait at least until we have extensive performance
> tuning on DBT-3 with 7.5. Thanks again for adding the new algorithm.

Everyone is always welcome to try and show that something can be 
improved. And we are in the middle of the 7.5 development cycle, so feel 
free to hack around.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #