Re: different physical access method because of disabling Automated Memory Management?
Date: Mon, 5 Jan 2009 11:55:47 +0100
Message-ID: <cd8f74560901050255l6369a92fka70022ac45916604_at_mail.gmail.com>
Hi Mark,
thank you for your reply!
I followed your suggestion and created a pfile from the manual spfile.
*** without any changes => same result.
*** after setting all *__* (double underbar) parameters as the default, I
got the desired effect ('*db file sequential read*')!
This brought me a big step forward.
As now only the result is correct, but I still does not know the parameter
which causes the effect, I started manipulationg only one parameter at each
time:
- *_small_table_threshold*:
I changed this value from 489 down to 100, 90, 89 and 10 - without any effect! (let me point to the fact the table was created with STORAGE (BUFFER_POOL KEEP), so I'm testing the KEEP buffer!)
=> Now I'm pretty sure, _small_table_threshold has only limited effect on
the keep cache (*see later findings*) - *all other parameters without effects*:
streams_pool_size= 0 / unset => no change pga_aggregate_target= 188743680 / unset => no change large_pool_size= 4194304 / unset => no change java_pool_size= 4194304 / unset => no change shared_pool_size= 130023424(__) / 536870912(manual) => no change - *db_cache_size*:
197132288 (__) => '*db file sequential read*' 12582912 (manual) => '*direct path read*'
=> so the size of *db_cache_size* has an direct effect on segments going
into KEEP buffer pool!
Some more Tests & findings:
I did some tests and calculations (*without* implicize * _small_table_threshold* set in pfile):
db_keep_cache_size = 12582912
db_cache_size = 197132288
=> _small_table_threshold = 489
db_keep_cache_size = 197132288
db_cache_size = 12582912
=> _small_table_threshold = 498
If I sum both caches, I get 209715200, with a block-size of 8k 2% are 512, which is close enough to 489/498 for me, assuming there is some overhead for memory-structures (x$bh etc).
last testcase for now:
db_keep_cache_size = 12582912
db_cache_size = 12582912
=> _small_table_threshold = 59
==> expected 'direct path read'.
but when I change alter session set "*_small_table_threshold*"=700; (just a
value really big enough) - I get '*db file sequential read*'.
(this is more or less as expected)
*But the other way round:*
db_cache_size = 197132288
db_keep_cache_size = 197132288
and implicite *_small_table_threshold* = *10*
gives me also '*db file sequential read*' !?!?
*Summary*:
- with multiple Caches, *_small_table_threshold* is calculated from the SUM of them (at least for *default *and *keep*, not tested for *recycle* and blocksize specific).
- *_small_table_threshold* does not always influence the physical access as expected.
I'm not sure if all questions are answered, but at leasts some points are clearer now. I will continue with some more tests, maybe I can draw a more consistent picture in the future.
cheers
Martin
> > If I understand it correctly, the __ (double underbar) parameters at the > very beginning are for "auto" to remember where it was last time it shut > down, while "manual" will ignore these values (but also apparently not > change them). > > > > So to eliminate all differences between "auto" and "manual" that you can, I > would create a pfile from your existing spfile and use the values from the > __ values to set all the corresponding "regular" init parameters. > > > > Then, using the pfile for startup, repeat your manual test. If that has the > desired effect, then presumably the rest of the differentiating tests can be > done using pfiles so the earth cannot move out from underneath you with > varying __ values possible each shutdown. We'll also know that pfile versus > spfile for startup is not making the difference, so we can focus on > parameter values for isolating it to which parameter(s) are key to the > change. By the way, I **predict** this should generate derived parameters > for the "manual" case that match the "auto" case, presumably including the > difference in *_small_table_treshold *shown in your tests following up on > Tanel's suggestion. So avoid patching up the normally derived parameters and > let them be derived for this test. If it all comes out as it seems would > make sense, then you **could** winnow it down to which parameter(s) affect > the plan choice and where the breakpoints are in values. If it turns out to > be a single parameter you can use a binary halving pattern on the parameter > value to discover the breakpoint as narrowly as you desire. Sometimes a > useful number pops out from such a test, such as (made up example, not real > case) use direct read unless the existing cache has at least 2 times the > available space free as the size of the object to be scanned. If the result > is related to multiple different parameters, the multivariant search for > breakpoints becomes increasingly tedious. > > > > If using the "starting values" does not have the desired effect with a > pfile, preserve your existing spfile (because we might want to go back to it > as a fixed point startup value) as another name and create an spfile from > the modified pfile and test again starting up from the new spfile. If that > has the desired effect, then you can proceed as in the first case, only > using a new spfile for each test. > > > > If the derived values in either test are still different for 'auto' than > for 'manual' using the "starting values" from the __ parameters, then I > think we have ruled out reported changes. That would mean either that the > effect of some _ (single underbar) that is not reported in traces is > dominating the result or that simply being auto versus manual affects the > plan algorithm. That would indeed be most fortunate and something worth > asking Oracle if it is possible to change so the behavior matches. > > > > Good luck. And thanks for the completeness of the information in your > posting. > > > > mwf >
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 05 2009 - 04:55:47 CST