RelationalDBDesign RelationalDBDesign

EXPLAIN PLAN   «Prev  Next»
Lesson 5Detecting full-table scans with EXPLAIN PLAN
Objective Identify a full-table scan.

Detecting Full-table Scans with EXPLAIN PLAN

Oracle slow-downs

As mentioned in prior lessons, unnecessary full-table scans are those scans that search full tables when the information resides within a small percentage of rows, are a common cause of Oracle slow-downs. In a full-table scan, the whole table may not be required to service the query, but Oracle cannot see any indexes that can be used to avoid a full-table scan.
Rather, Oracle reads up to the high water mark for the table. The high water mark indicates the last block in the table that has ever had a row placed on the block. Because of the I/O intensive nature of reading a whole table, Oracle uses multi-block I/O to read the table blocks from disk. This multi-block I/O is governed by the init.ora parameter called db_multiblock_read_count.
The most common cause of an unnecessary full-table scan is the lack of an index. The following SlideShow will help clarify how unnecessary full-table scans occur.

  1. Once the SQL has been parsed, Oracle checks the library cache
  2. Since there is not an index, Oracle creates an execution plan
  3. Oracle now proceeds to read every row of the population table

Oracle Checks Library Cache

Appropriate full-table scans

There are cases, however, where full-table scans are appropriate. These cases include:
  1. Scans against small tables with fewer than 300 rows
  2. Queries that will access more than half of the table rows (These may run faster with a full-table scan than by accessing the rows via the index.)
  3. Queries that compute the minimum (MIN), maximum (MAX) or average (AVG) of a table column value, where most of the table rows are required

The goal of detecting full-table scans is to determine if the creation of an index will replace the full-table scan with an index range scan. The most common approach to look for full-table scans is to use a tool such as Oracle’s SQL*Analyze to grab SQL from the library cache, which you practiced using a different scenario in an earlier lesson. Let us try this with the following simulation:

Detecting Full Table Scans using Explain Plan
The next lesson will help you identify nested access paths.