Re: 10gR2 db_file_multiblock_read_count autotuning

Your test case has nothing to do with execution plan change, but show that the I/O bandwidth of your system is abround 55 blocks at a time.

I've removed db_file_multiblock_read_count from my init.ora file and check the parameters.

db_file_multiblock_read_count    82
_db_file_exec_read_count    82
_db_file_optimizer_read_count    8

Here you have "two" mbrc.(as of 10gR2)
- Optimizer mbrc: MBRC used for optimizing your SQL statement - Execution mbrc: MBRC used for executing multi block I/O

Above numbers are saying "I would use 8 for calculating cost of full scan, but when the time has come to execute multi block I/O, i would use 82".

These mbrc things in 10gR2 are quite tricky(at least to me.. It seems unnatural and not sophisticated). Here are some rules we must keep in mind. - Once you collect workload system stats, you have mbrc collected by Oracle. This value is used for optimizing your query. Oracle uses db_file_mutliblock_read_count parameter for executing multi block I/O - If you have no workload system stats gathered, you have two mbrc. _db_file_optimizer_read_count and _db_file_exec_read_count. One for optimizer, one for execution of multi block I/O. - If you remove db_file_multiblock_read_count from parameter file, Oracle automatically sets _db_file_exec_read_count to some enough value and sets _db_file_optimizer_read_count to some reasonable value(here 8). This feature seems to be called "autotuned db_file_multiblock_read_count". funny. - When you set db_file_mutliblock_read_count to a specific value, both of _db_file_xxx_read_count is set to same value.

The conclusion is that when you use autotuned(whatever it is called) db_file_multiblock_read_count, Oracle actually uses two separate mbrc for optimization and execution. Your execution plan is not chaned by autotuned db_file_multiblock_read_count but by _db_file_optimizer_read_count.

Another thing to note is that Oracle strongly recommends to collec workload system stats. Once you collect workload system stats, you don't need to worry about the change of execution plan by setting big value of db_file_multiblock_read_count.

2008/2/6, kyle Hailey <kylelf_at_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
>
>
>

-- 
================================
EXEM. The Performance Artist Group
DB±â¼úº»ºÎ/ ±³À°ÄÁÅÙÃ÷ÆÀ / ¼ö¼® Á¶ µ¿¿í
¼­¿ï½Ã °­³²±¸ ¿ª»ïµ¿ ¾ÆÁÖºôµù 902È£
Tel: 02-6203-6300
ukja_at_ex-em.com, ukja.dion_at_gmail.com
Blog: http://ukja.tistory.com
Wiki: http://wiki.ex-em.com
================================
--
http://www.freelists.org/webpage/oracle-l