Oracle Tuning Factors
that Influence Disk I/O
Oracle Tips by Burleson Consulting
As you know, one of the primary goals of all
Oracle tuning activities is to reduce disk I/O. We will be discussing
these techniques in Chapter 9, but we need to mention them here so you
will understand how the instance parameters can affect disk I/O. There
are three areas where the settings for Oracle have a direct impact on
the amount of disk I/O. The settings for the Oracle instance (init.ora)
impact disk I/O, the settings for Oracle objects (tables and indexes)
affect disk I/O, and the execution plans for Oracle SQL also have a
direct impact on disk I/O.
Oracle instance There
are several database instance parameters that have a direct impact on
lowering physical disk I/O:
Large db_block_size The block
size of the database has a dramatic effect on the amount of disk
I/O. As a general rule, the larger the block size, the less the disk
Large db_cache_size The greater
the number of data buffers, the smaller the chance that Oracle will
need to perform disk I/O.
Utilize multiple blocksizes The largest
supported blocksizes in Oracle9i for your platform should be
reserved for index tablespaces, and smaller blocksizes help the
speed of bitmap index DML.
Multiple database writers (DBWR)
processes Multiple database writer background processes allow
for more efficient writing to the datafiles.
Large sort_area_size The greater
the sort_area_size in RAM, the less disk sorting will take
place in the TEMP tablespace.
Large online redo logs The larger the
online redo logs, the less frequent the log switches.
Oracle objects Inside
the database, settings for tables and indexes can reduce physical disk
pctused The smaller the value of pctused, the less
I/O will occur on subsequent SQL inserts.
Low pctfree If pctfree is
set to allow all rows to expand without fragmenting, the less disk
I/O will occur on subsequent SQL selects.
Reorganizing tables to cluster rows
with indexes If tables are placed in the same physical order as
the most frequently used index, disk I/O will drop dramatically.
This can be done with single table clusters or by re-sequencing the
table rows with CTAS.
Oracle SQL Within
SQL statements, there are many techniques to reduce physical disk I/O:
unnecessary full table scans using indexes or hints This is the
most important way to reduce disk I/O because many SQL queries can
use indexes to reduce disk I/O.
Using bitmapped indexes The use of
bitmapped indexes will reduce full table scans on tables with
low-cardinality columns, thereby reducing disk I/O. For
highly-updated bitmap index columns, using smaller blocksizes with
bitmaps reduces the costs for DML.
Applying SQL hints Many hints make SQL
run faster and with less disk I/O. For example, the USE_HASH hint
will reduce disk I/O by performing joins within SGA memory, reducing
calls for database blocks.
Now that we have reviewed some of the things that we can
do within Oracle to reduce disk I/O, let's take a close look at the
nature of disk I/O and examine the internal workings of the disk I/O
Oracle Internals and Disk
From an Oracle perspective, most databases can
be characterized as either online transaction processing (OLTP)
systems or decision support (DSS) systems. The patterns of I/O vary
greatly between a data warehouse and decision support type of
application and one that processes online transactions. While OLTP may
appear random, upon closer inspection, we will see clear areas of
impact to the Oracle database, and understand methods to alleviate I/O
Oracle File Organization Techniques
whether or not you use RAID, it is very important for the Oracle Remote DBA
to identify all high-volume and high-activity tables and move them
into isolated tablespaces. By keeping the high-volume tables in a
separate tablespace, the Oracle administrator can manipulate the
datafiles in the tablespace to minimize I/O contention on the disk, as
shown in Figure 8-1.
Figure 8-39: Segregating Oracle tables into
Without segregation, some tablespaces may have
hundreds of tables and indexes, and it is impossible to tell which
objects are experiencing the high I/O. The stats$filestatxs table will
provide details about read and write I/O at the file level, but it is
often difficult to tell which tables are causing the high I/O because
a file may contain many objects.
With segregation, the Remote DBA can generate
STATSPACK file I/O reports from the stats$filestatxs table that show
all read and write I/O for the datafile. If the Oracle datafile maps
to only one table, we can easily see the total I/O for the table.
Later in this chapter we will see a STATSPACK script called
rpt_io_pct.sql that serves this purpose.
Because of the high-speed transaction-oriented
nature of most Oracle applications, we generally see high activity in
specific areas within each tablespace. While this chapter describes
the basics of I/O load balancing for Oracle datafiles and tablespaces,
the settings for the individual tables will also have a profound
influence on the performance of the entire database. We will look at
disk I/O within Oracle and examine several areas:
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.