Oracle Administration Reducing
disk I/O in UNIX
Oracle UNIX/Linux Tips by Burleson Consulting
Reducing disk I/O in UNIX
When using multiple blocksizes, the DBA is
given additional control over the UNIX disk I/O sub-system. The
Oracle DBA understands the basic truths about UNIX disk I/O:
* Disk I/O is the largest component of
Oracle response time. A reduction in disk I/O will always result in
faster performance for that task.
* Creating larger data blocks allows Oracle
to access more row data in a single I/O. Oracle9i and beyond
supports multiple block sizes, and the Oracle DBA can move tables easily from one
blocksize to a tablespace with another blocksize, thereby
load-balancing disk I/O.
* The Oracle DBA has tools (in-place table
reorgs, CTAS with order by) to allow easy table reorganization, and
the DBA can use these tools to re-sequence table rows in the same
order as the primary index to reduce disk I/O on index range scans.
For more information, see Turning the Tables on Disk I/O, January
2000, at Oracle Magazine online (www.oramag.com).
So, how do we reduce disk I/O in UNIX?
There are three generally accepted techniques for the DBA to reduce
* Tune SQL statements to retrieve data with
a minimum of disk I/O - This is generally performed by finding
large-table full-table scans and replacing the full-table scan with
an index scan.
* Change the Oracle SGA - When we
increase the shared_pool_size, large_pool_size, or db_cache_size, the
resulting performance improvement is related to the reduction in
* Reorganize tables to reduce disk I/O ?
This is done by selectively moving tables to tablespaces with a
different blocksize, and re-sequencing table rows into the primary
Let?s take a closer look at re-sequencing
table rows to reduce disk I/O, and see how this can help a
UNIX-based Oracle system.
Re-sequencing table rows to reduce disk
Basically, the create table as select (CTAS)
statement copies the selected portion of the table into a new table.
If you select the entire table with an order by clause or an index
hint, it will copy the rows in the same order as the primary index.
In addition to re-sequencing the rows of the new table, the CTAS
statement coalesces free space and chained rows and resets
freelists, thereby providing additional performance benefits. You
can also alter table parameters, such as initial extents and the
number of freelists, as you create the new table. The steps in a
CTAS reorganization include:
1. Define a separate tablespace (maybe with
a different blocksize) to hold the reorganized table.
2. Disable all referential integrity
3. Copy the table with CTAS (using order by
or an index hint)
4. Reenable all referential integrity
5. Rebuild all indexes on the new table.
The main benefit of CTAS over the other
methods is speed. It is far faster to use CTAS to copy the table
into a new tablespace (and then re-create all RI and indexes) than
it is to use the export/import method. Using CTAS also has the added
benefit of allowing the rows to be resequenced into the same order
as the primary index, thereby greatly reducing I/O. Within CTAS,
there are two general reorganization methods.
Using Oracle9i for in-place table
Staring in Oracle8i, new alter table syntax
was introduced to allow you to easily reorganize a table. The
syntax for the new command is:
move online tablespace
Remember that the alter table move command
requires double the disk space. Because Oracle keeps the old
copy of the table until the new table is copied, you must have at
least double the space in the tablespace.
While the in-place table reorg is great for
coalescing freelists, removing chained rows and changing the block
size for a table, the great downside of in-place table
reorganization is the inability to re-sequence rows.
table emp move order by ename tablespace tools;
alter table emp move order by ename tablespace tools
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
It is hoped that Oracle9i will be enhanced
to allow for row re-sequencing with the alter table syntax, but
until that time, the savvy DBA will continue to use the CTAS syntax
to reorganize tables.
Two Alternatives for Using CTAS
It is always recommended that you resequence
the table rows when performing a table reorganization with CTAS
because of the huge I/O benefits. You can use the CTAS statement in
one of two ways. Each of these achieves the same result, but they do
it in very different ways:
* Use CTAS in conjunction with the order by
* Use CTAS in conjunction with a ?hint? that
identifies the index to use.
The approach you choose depends on the size
of the table involved, the overall processing power of your
environment, and how quickly you must complete the reorganization.
The details of each CTAS approach are
discussed more fully below, but in either case, when you create the
new table, you can speed the process by using the Oracle nologging
option (this was called unrecoverable in Oracle7). This skips the
added overhead of writing to the redo log file. Of course, you
cannot use the redo logs to roll forward through a nologging
operation, and most DBAs take a full backup prior to using CTAS with
nologging. Let?s examine the two methods and see their respective
Using CTAS with the order by Clause
When using CTAS with the order by clause,
you are directing Oracle to perform the following operations, as
shown in Figure 8-2.
Figure 2: Using CTAS with order by
As we can see, the full table scan can be
used with Parallel Query to speed the execution, but we still have a
large disk sort following the collection of the rows. Because of the
size of most tables, this sort will be done in the TEMP tablespace.
Here is an example of the SQL syntax to
perform a CTAS with order by:
parallel (degree 11)
as select * from customer
order by customer_number;
Using CTAS with order by can be very slow
without the parallel clause. A parallel full table scan reads the
original table quickly (in non-index order).
As we know from Oracle Parallel Query, the
CTAS operation will cause Oracle to spawn to multiple background
processes to service the full table scan. This often makes the order
by approach faster than using the index-hint approach to CTAS. The
choice to use parallel depends on the database server. If your
hardware has multiple CPUs and many (perhaps hundreds of) processes,
using parallel is likely to be significantly faster. However, if
your hardware configuration has a relatively modest number of
processes (such as the four specified in the example), the
index-hint approach is likely to be faster.
Using CTAS with an Index Hint
The CTAS with an index hint executes quite
differently than CTAS with order by. When using an index hint, the
CTAS begins by retrieving the table rows from the original table
using the existing index. Since the rows are initially retrieved in
the proper order, there is no need to sort the result set, and the
data is used immediately to create the new table, as shown in Figure
Figure 3: Using CTAS with an index hint
The syntax for CTAS with an index hint
as select /*+ index(customer customer_primary_key_idx) */ *
When this statement executes, the database
traverses the existing primary-key index to access the rows for the
new table, bypassing the sorting operation. Most Oracle DBAs choose
this method over the order by approach because the runtime
performance of traversing an index is generally faster than using
the PARALLEL clause and then sorting the entire result set.
Now that we see how CTAS works for table
reorganizations, let?s explore why the database blocksize can be an
important factor in reducing disk I/O.
If you like Oracle tuning, see the
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.