|
|
Oracle db_file_multiblock_read_count
Don Burleson
|
10gr2 Note: Starting in
Oracle 10g release 2, Oracle recommends not setting the
db_file_multiblock_read_count parameter, allowing Oracle to
empirically determine the optimal setting. For more details,
see my notes on
10gR2 automatically tuned multi-block reads.
When you implement multiple
blocksizes you should set your db_block_size based on the
size of the tablespace where your large-object full-scans will be
occurring. The Oracle 10g release 2 Performance Tuning Guide page
14.4 notes:
"In release 10.2, the optimizer uses the
value of mbrc when performing full table scans (FTS). The value
of db_file_multiblock_read_count is set to the maximum allowed
by the operating system by default. However, the optimizer uses
mbrc=8 for costing.
The "real" mbrc is actually somewhere in
between since serial multiblock read requests are processed by
the buffer cache and split in two or more requests if some
blocks are already pinned in the buffer cache, or when the
segment size is smaller than the read size.
The mbrc value
gathered as part of workload statistics is thus useful for FTS
estimation."
Remember, the parameter db_file_multiblock_read_count
is only applicable for tables/indexes that are full scanned, but it
also effects the SQL optimizer in its calculation of the cost of a
full-table scan.
According to Oracle, this is the formula for
setting db_file_multiblock_read_count:
max I/O chunk size db_file_multiblock_read_count = ------------------- db_block_size
But how do we know the value of the max I/O chunk
size?
The maximum effective setting for
db_file_multiblock_read_count is OS and disk dependant. Steve
Adams, an independent Oracle performance consultant (see
www.ixora.com.au ), has
published a helpful script to assist you in setting an appropriate
level. This script conducts a test and sample actual I/O chunk
sizes on your server to aid you in setting
db_file_multiblock_read_count:
-------------------------------------------------------------------------------
--
-- Script: multiblock_read_test.sql
-- Purpose: find largest actual multiblock read
size --
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-- Description: This script prompts the user to enter the name of a
table to -- scan, and
then does so with a large multiblock read count, and
-- with event 10046 enabled at level 8.
-- The trace file is then examined to find the
largest multiblock --
read actually performed.
--
-------------------------------------------------------------------------------
@save_sqlplus_settings
alter session set
db_file_multiblock_read_count = 32768; /
column value heading "Maximum possible multiblock
read count" select
value
from
sys.v_$parameter
where
name = 'db_file_multiblock_read_count'
/
prompt
@accept Table "Table to scan" SYS.SOURCE$
prompt Scanning ...
set termout off
alter session set events '10046 trace name
context forever, level 8' /
select /*+ full(t) noparallel(t) nocache(t) */
count(*) from &Table t /
alter session set events '10046 trace name
context off' /
set termout on
@trace_file_name
prompt
prompt Maximum effective multiblock read count
prompt ----------------------------------------
host sed -n '/scattered/s/.*p3=//p'
&Trace_Name | sort -n | tail -1
@restore_sqlplus_settings
|
|