Spotlight on Oracle 10.9 - Getting Started Guide

Optimize Necessary Table Scans

In many cases, you should aim to avoid a full table scan by using the best of all possible indexes. Often though, a full table scan cannot be avoided. In these situations, consider some of the following techniques to improve table scan performance:

Reduce the size of the table

The performance of a full table scan is generally proportional to the size of the table to be scanned. There are ways of reducing the size of the table quite substantially, thereby improving full table scan performance. These include:

• Reduce PCTFREE. The PCTFREE table setting reserves a certain percentage of space in each block to allow for updates that increase the length of a row. By default, PCTFREE is set to 10%. If your table is rarely updated, or if the updates rarely increase the length of the row, you can reduce PCTFREE and, hence, reduce the overall size of the table.
• Increase PCTUSED. The PCTUSED table setting determines at what point blocks that have previously hit PCTFREE become eligible for inserts again. The default value is 40%. This means that after hitting PCTFREE, the block only becomes eligible for new rows when deletes reduce the amount of used space to 40%. If you increase PCTFREE, rows are inserted into the table at an earlier time, blocks are fuller on average, and the table is smaller. There may be a negative effect on INSERT performance. You must assess the trade off between scan and insert performance.
• Relocate LONG columns. If you have LONG (or big VARCHAR2 ) columns in the table that are not frequently accessed, and never accessed via a full table scan (perhaps a bitmap image or embedded document), you should consider relocating these to a separate table. By relocating these columns, you can reduce the table’s size substantially and hence improve full table scan performance.

Use the Parallel Query option

Oracle’s Parallel Query Option is the most effective (and most resource-intensive) way of improving the performance of full table scans. Parallel Query allocates multiple processes to a SQL statement that is based (at least partially) on a full table scan. The table is partitioned into distinct sets of blocks, and each process works on a different set of data. Further processes may be allocated, or the original processes recycled, to perform joins, sorts, and other operations.

The approach of allocating multiple processes to the table scan can reduce execution time dramatically if the hardware and database layout is suitable. In particular, the host computer should have multiple CPUs, or the database should be spread across more than one disk device.

You can enable the Parallel Query option with a PARALLEL hint, or make it the default for a table with the PARALLEL table clause.

Use the Parallel Query option (Oracle 11g Release 2)

A new Parallel Query parameter was introduced in Oracle 11gR2. You can enable Automatic Degree of Parallelism by setting PARALLEL_DEGREE_POLICY to AUTO. This results in the following new behaviours:

• Oracle decides the degree of parallelism for each statement automatically.
• Oracle defers statement execution rather than downgrading or serializing the SQL when parallel servers are busy.
• Oracle Parallel slaves may use buffered I/O rather than direct I/O.

Setting PARALLEL_DEGREE_POLICY to MANUAL results in Oracle 11gR1 behaviour.

There are two new wait events in Oracle 11gR2 to help identify when a statement has been queued due to the parallel slaves being busy.

• PX Queuing: statement queue - The amount of time spent waiting for an executing parallel SQL to relinquish its PQ slaves. This is time spent waiting as the first statement in the parallel execution queue.
• enq: JX - SQL statement queue - The amount of time spent waiting for other statements to be queued for execution. This is the time spent waiting to get to the front of the parallel execution queue.

Use the CACHE hint

Normally, rows retrieved by most full table scans are flushed almost immediately from Oracle’s cache. This is sensible; otherwise, full table scans could saturate the cache completely, and push out rows retrieved from index retrievals. However, this does mean that subsequent table scans of the same table are unlikely to find a match in the cache, and therefore incur a high physical I/O rate.

You can encourage Oracle to keep these rows within the cache by using the CACHE hint or the CACHE table setting. Oracle then places the rows retrieved at the Least Recently Used end of the LRU chain, and they persist in the cache for a much longer period of time.

Use Partitioning

If the number of rows you want to retrieve from a table is greater than an index lookup could retrieve effectively, but still only a fraction of the table itself (say between 10% and 40% of total), you could consider partitioning the table.

For instance, suppose that a SALES table contains all sales records for the past 4 years and that you need to scan all sales records for the current financial year frequently in order to calculate year-to-date totals. The proportion or rows scanned is far greater than an index lookup would support comfortably, but is still only a fraction of the total table.

If you partition the table by financial year, you can restrict processing to only those records that match the appropriate financial year. Potentially, this could reduce scan time by 75% or more.

Use The Fast Full Index Scan

If a query needs to access all or most of the rows in a table, but only a subset of the columns, you can consider using a fast full index scan to retrieve the rows. To do this, you need to create an index that contains all the columns included in the SELECT and WHERE clauses of the query. If these columns comprise only a small subset of the entire table, the index is substantially smaller. Oracle is able to scan the index more quickly than it could scan the table. There is an overhead involved in maintaining the index that affects the performance of INSERT, UPDATE, and DELETE statements.

Using an index to perform the equivalent of a full table scan is possible in earlier versions of Oracle, but only since Oracle 7.3 is the index scan able to use multi-block read and parallel query capabilities.

Related Topics

Isolate and Tune Problem SQL

Tune Your Oracle Database