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 64142the 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 1So 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 19725So also in this case 64143 survived, whereas the other 2 were fighting for the same blocks in DEFAULT buffer cache.
Keine Kommentare:
Kommentar veröffentlichen