Tuning SQL Table Access Oracle Tips by Burleson Consulting

This chapter deals with the low-level table access methods that result from running the execution plan for the SQL statement. As you know from Chapter 3, any SQL statement may have several execution plans depending on the CBO statistics, the presence and types of indexes, and the optimizer_mode. At a lower level, each execution plan translates into table access methods. These low-level table access methods determine the overall response time for the SQL statement, so it is imperative that you understand the table access methods and how you can improve the throughput for table access.

When discussing Oracle table access methods, we must always remember that Oracle has only a few ways to access any Oracle table, and that the physical access is always performed by reading a database block. There are several ways to access Oracle data blocks from SQL:

• Full-table scan This can invoke multi-block reads and parallelism to improve access speed.

• Index access   When using an index to retrieve a row, the index provides Oracle with the ROWID, which results in a request for a specific Oracle data block. We also see index range scans, where a range of values is retrieved from a table, and these accesses are based on a range of ROWIDs that are collected while reading the index nodes.

• Fast full-index scan This is a form of index where Oracle reads all of the data blocks within an index using multiblock reads and parallelism. It is the index equivalent of a full-table scan.

To fully understand Oracle table access methods, you must begin by carefully examining full-table scans. The SQL optimizer manages index access within Oracle automatically, but we will examine tricks for improving SQL index access in Chapter 20. Within an individual table, we can perform numerous administrative functions to improve the speed of SQL, including row resequencing and changing the table storage parameters. This chapter will cover the following table access issues:

• SQL tuning and full-table scans

• Table access via indexes

• Changing table access methods

• Resequencing table rows for reducing disk I/O

• Oracle storage parameters and table access performance

• Freelist management and table access performance

When you look at tuning any Oracle SQL statement, you must first understand the ways that an Oracle table can be accessed, and the internal block access techniques. Let’s begin by examining the full-table scan.

SQL Tuning and Full-Table Scans

There are some important rules for when a SQL query should perform a full-table scan. Unnecessary large-table full-table scans cause a huge amount of unnecessary I/O and can drag down an entire database. The tuning expert first evaluates the SQL in terms of the number of rows returned by the query. If the query returns less than 40 percent of the table rows in an ordered table, or 7 percent of the rows in an unordered table, the query can be tuned to use an index in lieu of the full-table scan. The most common tuning for unnecessary full-table scans is adding indexes. Standard B-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans. The decision about removing a full-table scan should be based on a careful examination of the I/O costs of the index scan versus the costs of the full-table scan, factoring in the multi-block reads and possible parallel execution. In some cases an unnecessary full-table scan can be forced to use an index by adding an index hint to the SQL statement.

In cases where a full-table scan is the fastest access method, the tuning professional should ensure that a dedicated data buffer is available for the rows. In Oracle7, you can issue alter table xxx cache. In Oracle8 and beyond, the small table can be cached by forcing the table into the KEEP pool.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.