10gR2 db_file_multiblock_read_count autotuning
- From: "kyle Hailey" <kylelf@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 5 Feb 2008 13:27:15 -0800
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
Other related posts:
