10gR2 db_file_multiblock_read_count autotuning

From: kyle Hailey <kylelf_at_gmail.com>
Date: Tue, 5 Feb 2008 13:27:15 -0800
Message-ID: <6f373fd20802051327k5a2e2babp97e88a8e49d28a6a@mail.gmail.com>


10gR2 is suppose to autotune db_file_multiblock_read_count which sounds like an appealing concept.

 http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2006-04-20.2949600305

The idea according the article is that high dmbrc's won't overly bias the executions to fulltable scans.
Has anyone run into experiences where this adversly affected the execution plans?

Running a quick test on my windows vista 10gR2 it does some to pick the better value by default.
I thought setting db_file_multiblock_read_count=0 would make Oracle auto tune, but instead this value cause me to do single block reads. By taking the value out of the init.ora and bouncing the database it comes up with a default value

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 55

SQL> set timing on
SQL> select count(*) from toto;

  COUNT(*)


   1618848

Elapsed: 00:00:04.30
SQL> /   COUNT(*)


   1618848

Elapsed: 00:00:04.32
SQL> /   COUNT(*)


   1618848

Elapsed: 00:00:04.37

select p3, count(*) from v$active_session_history   where event='db file scattered read'

      and sample_time > sysdate - 5/(24*60) group by p3
order by p3;

       P3 COUNT(*)
---------- ----------

         4          1
        55         12

SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

Elapsed: 00:00:00.01
SQL> select count(*) from toto;

  COUNT(*)


   1618848

Elapsed: 00:00:05.12
SQL> /   COUNT(*)


   1618848

Elapsed: 00:00:04.40
SQL> /   COUNT(*)


   1618848

Elapsed: 00:00:04.74
select p3, count(*) from v$active_session_history   where event='db file scattered read'

      and sample_time > sysdate - 5/(24*60) group by p3
order by p3;

        P3 COUNT(*)
---------- ----------

         4          1
        55         12
       128         14


SO the autotuned dmbrc of 55 performs consistently better than the higher value 128 in this simple test.

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 05 2008 - 15:27:15 CST

Original text of this message