Wednesday, December 23, 2009

Setting db_file_multiblock_read_count

A very nice procedure to see the effects on a full table scan query and to estimate the value for db_file_multiblock_read_count.

DECLARE
l_count PLS_INTEGER;
l_time PLS_INTEGER;
l_starting_time PLS_INTEGER;
l_ending_time PLS_INTEGER;
BEGIN
dbms_output.put_line('dbfmbrc seconds');
FOR l_dbfmbrc IN 1..32
LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count='||l_dbfmbrc;
l_starting_time := dbms_utility.get_time();
SELECT /*+ full(t) */ count(*) INTO l_count FROM big_table t;
l_ending_time := dbms_utility.get_time();
l_time := round((l_ending_time-l_starting_time)/100);
dbms_output.put_line(l_dbfmbrc||' '||l_time);
END LOOP;
END;
/

Table must be enough big in order not to be cached !!!

Taken by an excellent book (Apress:Troubleshooting Oracle Performance)

No comments:

Post a Comment