Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
  Phone
  800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

    Privacy Policy

 

 
 

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


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

   

 Copyright Š 1996 -2011 by Burleson. All rights reserved.


OracleŽ is the registered trademark of Oracle Corporation. SQL ServerŽ is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks