Setting the db_block_size with multiple block sizes
When multiple blocksizes are implemented, the
db_block_size should be set based
on the size of the tablespace where the large object full scans will
be occurring. The
db_file_multiblock_read_count parameter is only applicable for tables/indexes that
are full scanned.
With the implementation of multiple blocksizes, Oracle MetaLink notes that the
db_file_multiblock_read_count
should always be set to a value that sums to the
largest supported blocksize of 32k:
|
db_block_size
|
db_file_multiblock_read_count
|
|
2k |
16 |
|
4k |
8 |
|
8k |
4 |
|
16k |
2 |
Table 13.1:
Oracle block size and
corresponding read count
One issue with Oracle multiple block sizes is the setting for
db_file_multiblock_read_count
. This value influences the SQL optimizer
about the costs of a full table scan.
|
|
Objects that experience full scans and indexes with frequent
range scans might benefit from being placed in a larger
block size, with
db_file_multiblock_read_count set to
the block size of that tablespace. |
According to Oracle, the following formula can be used for setting
db_file_multiblock_read_count
:
max I/O chunk size
db_file_multiblock_read_count = -------------------
db_block_size
But what is the maximum 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 (www.ixora.com.au), has published a helpful script
to assist in setting an appropriate level
<
multiblock_read_test.sql
--------------------------------------------------------------------
--
-- 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
For more details on using multiple blocksizes, see the book
Creating a Self Tuning Oracle Database
(2004, Rampant TechPress). Here is a handy Oracle MetaLink
script to display the data blocks associated with the data buffers,
using the x$ fixed tables:
<
display_multi_buffers.sql
select
decode(
pd.bp_id,
1,'KEEP',
2,'RECYCLE',
3,'DEFAULT',
4,'2K SUBCACHE',
5,'4K SUBCACHE',
6,'8K SUBCACHE',
7,'16K SUBCACHE',
8,'32K SUBCACHE',
'UNKNOWN') subcache,
bh.object_name,
bh.blocks
from
x$kcbwds ds,
x$kcbwbpd pd,
(select /*+ use_hash(x) */
set_ds,
o.name object_name,
count(*) BLOCKS
from
obj$ o,
x$bh x
where
o.dataobj# = x.obj
and
x.state !=0
and
o.owner# !=0
group by
set_ds,o.name) bh
where
ds.set_id >= pd.bp_lo_sid
and
ds.set_id <= pd.bp_hi_sid
and
pd.bp_size != 0 and ds.addr=bh.set_ds;
The following section will introduce how the new high speed RAM disk
(solid-state disk) that helps improve disk I/O throughput.
SEE CODE DEPOT FOR FULL SCRIPTS
