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 disk I/O:

* 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 disk I/O.

* 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 key order.

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 I/O

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

3. Copy the table with CTAS (using order by or an index hint)

4. Reenable all referential integrity constraints.

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 reorganizations

Staring in Oracle8i, new alter table syntax was introduced to allow you to easily reorganize a table.  The syntax for the new command is:

alter table
    xxx
move online tablespace
   yyy;

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.

SQL> alter 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 clause.

* 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 differences.

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:

 create table new_customer
   tablespace customer_flip
      storage (initial              500m
               next                  50m
               maxextents           unlimited)
   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 8-3.

Figure 3: Using CTAS with an index hint

The syntax for CTAS with an index hint appears below:

create table new_customer
   tablespace customer_flip
      storage  (initial             500m
                 next               50m
                 maxextents         unlimited)
   as select /*+ index(customer customer_primary_key_idx) */  *
   from customer;

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 book "Oracle 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.