Montag, 5. Januar 2009

KEEP and DEFAULT buffer really seperated (at least for LRU lists)

I had to answer a really simple question:
If I manage to put a table totally into KEEP buffer (and it's the only table which will ever go there), will it be affected by other operations which are going into DEFAULT buffer cache?
At least if you disable Automated Memory Management the answer is yes (even if some points has to be considered, but that's not part of the primary question).
Here my little testcase:
I have a table t_keep which should go and stay in keep buffer cache (and fits into it).
I have 2 other tables, t and t2, where t is mich bigger than the default pool, t2 also fits into default-pool but together they kick each other out (at least partially).
And now for the tests:
(I had to make _small_table_threshold big enough to load all tables in buffer cache and avoiding direct path reads)

about the tables:
select table_name, buffer_pool, blocks from dba_tables where owner ='TEST';TABLE_NAME                     BUFFER_     BLOCKS
------------------------------ ------- ----------
T2                             DEFAULT        401
T_KEEP                         KEEP           496
T                              DEFAULT      26289

select OBJECT_NAME, DATA_OBJECT_ID 
from dba_objects
 where owner ='TEST';
OBJ_NAME DATA_OBJECT_ID
-------- --------------
T2                64351
T_KEEP            64143
T                 64142
the test itselve:
SELECT ROWID FROM TEST.T_KEEP;

... ROWIDs ...

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj;

OBJ   COUNT(*)
---------- ----------
64143        452

SELECT ROWID FROM TEST.T;

... ROWIDs ...

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj;

OBJ   COUNT(*)
---------- ----------
64143        452
64142       1080

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj; 

OBJ   COUNT(*)
---------- ----------
64351        386
64143        452
64142          1 
So ObjectID:64143 == T_KEEP is still in KEEP buffer cache.

Now the same test with Automated Memory Management:
(only the results)
select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj;
       OBJ   COUNT(*)
---------- ----------
     64143        452

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj;
       OBJ   COUNT(*)
---------- ----------
     64143        452
     64142      20111

select obj, count(*) from x$bh where obj in (64142, 64143,64351) group by obj;
       OBJ   COUNT(*)
---------- ----------
     64351        386
     64143        452
     64142      19725
So also in this case 64143 survived, whereas the other 2 were fighting for the same blocks in DEFAULT buffer cache.
Kommentar veröffentlichen