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
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

 

 
 

2008 Note:  For the latest consensus on using multiple blocksizes in Oracle, see The 2008 consensus on multiple blocksizes.) Also note that empirical evidence suggests that you can use the large (16-32K) blocksize and separate data caches to improve response time under certain conditions.

Oracle Blocksize and Disk I/O

By now, the importance of multiple block sizes and multiple RAM caches should be clear.  Understanding the salient issues associated with block sizes enables the DBA to intelligently assign block sizes to tables and indexes.

 

The DBA should also realize that tuning changes are never permanent, and he or she can experiment with different block sizes and with moving tables from one tablespace to another.  For example, if the I/O increases after a table is moved into a 2K tablespace, it can simply be moved into a larger sized tablespace.  In the final analysis, minimizing I/O by adjusting block sizes is a long, iterative process.

 

The list below is a summary of rules for sizing objects into tablespaces of multiple block sizes:

§       Some index access likes large block sizes: B-tree indexes with frequent index range scans perform best in the largest supported block size.  This facilitates retrieval of as many index nodes as possible with a single I/O, especially for SQL during index range scans.  Some indexes do not perform range scans, so the DBA should make sure to identify the right indexes.

§       Use average row length: A tablespace should always have a larger block size than the average row length of the tables that reside in the tablespace as noted by the avg_row_len column in the dba_tables view.  Excessive I/O is incurred when the block size is smaller than the average row length due to row chaining.

§       Use large blocks for data sorting: In some cases, the TEMP tablespace will also benefit from the largest supported block size.  Large blocks allow disk sorting with a minimum of disk I/O.

The blocksize is especially important for Oracle indexes because the blocksize affects the b-tree structure and the amount of physical I/O required to fetch a rowid.

Oracle Blocksize & Index I/O

Within the Oracle index, each data block serves as a node in the index tree, with the bottom nodes or leaf blocks containing pairs of symbolic keys and rowid values. Oracle controls the allocation of pointers within each data block to properly manage the blocks. As an Oracle tree grows by inserting rows into the table, Oracle fills the block.  When the block is full, it splits, creating new index nodes or data blocks to manage the symbolic keys within the index.  Therefore, an Oracle index block may contain two types of pointers:

§       rowid pointers to specific table rows

§       Pointers to other index nodes

The freelist relink threshold for indexes, the pctused value, cannot be specified because Oracle manages the allocation of pointers within index blocks.  By studying an index block structure, it is possible to see that the number of entries within each index node is a function of two values:

§       The blocksize for the index tablespace

§       The length of the symbolic key

The blocksize affects the number of keys within each index block, hence the blocksize will have an effect on the structure of the index tree.  All else being equal, large 32K blocksizes will have more index keys resulting in a flatter index structure.

 

In any event, there appears to be evidence that block size affects the tree structure, which supports the argument that the size of the data blocks affects the structure of the Oracle index tree.

 

Do large insex blocks actually help performance?  A small but enlightening test can reveal the answer to that question.  The following query for the test will be used against a 9i database that has a database block size of 8K, but also has the 16K cache enabled along with a 16K tablespace:

 

select

      count(*)

from 

      scott.hospital

where

      patient_id between 1 and 40000;

 

The SCOTT.HOSPITAL table has 150,000 rows and has an index built on the patient_id column.  An EXPLAIN of the query shows it uses an index fast full scan to manifest the desired end result:

 

Execution Plan

----------------------------------------------------------

0    SELECT STATEMENT Optimizer=CHOOSE

1    (Cost=41 Card=1 Bytes=4)

   1    0   SORT (AGGREGATE)

   2    1     INDEX (FAST FULL SCAN) OF 'HOSPITAL_PATIENT_ID'

              (NON-UNIQUE) (Cost=41 Card=120002 Bytes=480008)

 

Executing the query twice, to eliminate parse activity and to cache any data with the index residing in a standard 8K tablespace produces these runtime statistics:

 

Statistics

---------------------------------------------------

          0  recursive calls

          0  db block gets

        421  consistent gets

          0  physical reads

          0  redo size

        371  bytes sent via SQL*Net to client

        430  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

To quiz the competency of the new 16K cache and 16K tablespace, the index used by the query will be rebuilt into the 16K tablespace, which has the exact same aspects as the original 8K tablespace, except for the larger blocksize:

 

alter index

      scott.hospital_patient_id

      rebuild nologging noreverse tablespace indx_16k;

 

When the index is lodged firmly into the 16K tablespace, the query is re-executed, again twice, with the following runtime statistics being produced:

 

Statistics

---------------------------------------------------

          0  recursive calls

          0  db block gets

        211  consistent gets

          0  physical reads

          0  redo size

        371  bytes sent via SQL*Net to client

        430  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

Simply by using the new 16K tablespace and accompanying 16K data cache, the amount of logical reads has been reduced by half.  Most assuredly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle 9i and later, are worth examination and trials in a database.

Next, let’s examine how we can get the best disk I/O performance by enabling direct I/O.

 

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 -2017 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