Lesson 5 | Detecting 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.
- Once the SQL has been parsed, Oracle checks the library cache
- Since there is not an index, Oracle creates an execution plan
- 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:
- Scans against small tables with fewer than 300 rows
- 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.)
- 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: