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:
§
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.
§
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.
§
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
