Re: 10gR2 db_file_multiblock_read_count autotuning
- From: "조동욱" <ukja.dion@xxxxxxxxx>
- To: kylelf@xxxxxxxxx
- Date: Wed, 6 Feb 2008 10:03:52 +0900
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@xxxxxxxxx>:
>
> 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.
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
================================
EXEM. The Performance Artist Group
DB기술본부/ 교육컨텐츠팀 / 수석 조 동욱
서울시 강남구 역삼동 아주빌딩 902호
Tel: 02-6203-6300
ukja@xxxxxxxxx, ukja.dion@xxxxxxxxx
Blog: http://ukja.tistory.com
Wiki: http://wiki.ex-em.com
================================
Other related posts:
