buffer cache poisioning

Out of some tests and discussions I wanted to create a way to pollute the buffercache with blocks not existing there. As a result the BCHR also goes down, so I might produce a contradiction to Connor McDonalds choose_a_hit_ratio. There might be easier ways doing so, feel free to promote, if you want.

How i test the BCHR:

column name format A32;
SELECT id, name, block_size,
 (1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))) HIT
FROM V$BUFFER_POOL_STATISTICS
ORDER BY NAME, BLOCK_SIZE;

On my test system the script is really slow and I'm not sure about side effects it will have on other systems than the buffer cache.

and my results (before the first run):

ID NAME       BLOCK_SIZE        HIT
-- ---------- ---------- ----------
3 DEFAULT          8192 .920069518
1 KEEP             8192 .158558559

The scripts functionality explained:

1. a Cursor c_tab provides all Tables of some quality (e.g. not owned by SYS)
2. for every entry in this cursor, all rowids are collected into a TABLE OF ROWID
3. every rowid is checked if it's block exists in x$bh, if not, it's directly selected and therefore bushed into bufer cache.

and here the script itselve (a big part just copied with permission from Reinhard Krämer)

DECLARE
counter  number;
obj_id   number;
rel_fno  number;
block_id number;
bh_count number;
bh_stmt  varchar2(200);
rid_stmt varchar2(200);
v_stt    varchar2(50);
v_rid    ROWID;
CURSOR c_tab
IS
  SELECT owner,
         table_name,
            owner
         || '.'
         || table_name owntab
  FROM DBA_tables
  WHERE owner NOT IN ('SYS')
         -- comment next line, if you want to have all tables
         --AND table_name = 'T_KEEP'
         AND BUFFER_POOL NOT IN ('KEEP')
  --       AND owner = UPPER ('TEST')
  ;

g_string VARCHAR2 (4000) := NULL;
-- memory-array
TYPE t_rowid IS TABLE OF ROWID
  INDEX BY PLS_INTEGER;

r_rowid t_rowid;

-- print-procedure (mini-version of MSG)
PROCEDURE PRINT (
  p_text IN VARCHAR2 := NULL
)
IS
BEGIN
-- remove comment for debug
--      DBMS_OUTPUT.put_line (p_text);
 NULL;
END;
BEGIN
-- main
EXECUTE IMMEDIATE 'select b.ksppstvl InstValue
                 from x$ksppi a, x$ksppcv b
                 where a.indx = b.indx
                   and a.ksppinm=''_small_table_threshold''' into v_stt;
EXECUTE IMMEDIATE 'alter session set "_small_table_threshold" = 10';
counter := 0;
bh_stmt := 'select count(*)
          from x$bh
          where obj=:obj_id and dbablk=:block_id and dbarfil=:rel_fno';

FOR crec IN c_tab
LOOP
 PRINT(' tab: ' ||crec.owntab);
  g_string :=    'select rowid the_rowid from '
              || crec.owntab;

  BEGIN
     PRINT (   'using command: '
            || g_string);

     EXECUTE IMMEDIATE (g_string)
     BULK COLLECT INTO r_rowid;
  EXCEPTION
     WHEN OTHERS
     THEN
        PRINT (   SQLERRM
               || ' on '
               || g_string);
  END;

  IF r_rowid.COUNT > 0
  THEN
     PRINT (   'showing rowids for: '
            || crec.owntab);

     FOR i IN r_rowid.FIRST .. r_rowid.LAST
     LOOP
        PRINT (r_rowid (i));
        -- load block if not in X$bh
   
        obj_id   := dbms_rowid.rowid_object(r_rowid(i));
        rel_fno  := dbms_rowid.rowid_relative_fno(r_rowid(i));
        block_id := dbms_rowid.rowid_block_number(r_rowid(i));
        rid_stmt := 'SELECT rowid the_rowid FROM '
              || crec.owntab || ' WHERE rowid = :rid';
        execute immediate bh_stmt into bh_count using obj_id, block_id, rel_fno;
   
        IF bh_count = 0 THEN -- block curently not in buffer
          BEGIN
            execute immediate rid_stmt into v_rid using r_rowid(i);
            counter := counter + 1;
          END;
        END IF;
     END LOOP;
  END IF;
END LOOP;
EXECUTE IMMEDIATE 'alter session set "_small_table_threshold" = :stt' using v_stt;
DBMS_OUTPUT.put_line ('counter: ' || counter);
END;
/

After a 2nd check the BCHR is really lower than before.

ID NAME       BLOCK_SIZE        HIT
-- ---------- ---------- ----------
3 DEFAULT          8192 .856567413
1 KEEP             8192 .158558559

One question might be why I didn't run just a lot of full table scans? I can not be sure how many blocks of this tables exist in buffer cache, and every existing buffer would increse the BCHR. Also in Oracle 11g this might result in direct path reads which will not affect the buffer cache directly (in fact, it will increase the BCHR, as all internal selects in SEG$, TS$ and all the other data dictionary tables needed to access blocks).
One last purpose: If ever anyone argues about the need of ratios to judge the health of a database, just ask if you get additional ressources if the BCHR is above/below any limit - and then grab these resources :-)