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 .158558559The scripts functionality explained:
- a Cursor c_tab provides all Tables of some quality (e.g. not owned by SYS)
- for every entry in this cursor, all rowids are collected into a TABLE OF ROWID
- every rowid is checked if it's block exists in x$bh, if not, it's directly selected and therefore bushed into bufer cache.
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 :-)
