Monitoring Table Access Methods (Oracle)
A full table scan occurs when a user process queries data from the database table without the use of an index. The entire table must be read to retrieve the requested information. Sometimes this is desirable, for example if the table is only short. Often, it is more efficient to use an index.
The Database Monitor displays the following information on the table accesses:
Short tables shows the total number of full table scans that were performed on short tables (tables having less than 5 Oracle data blocks). It is generally more efficient to perform full table scans on short tables rather than access the data using indexes.
Long tables shows the total number of full table scans done on long tables (tables containing 5 or more Oracle data blocks). It is usually advantageous to access long tables using indexes.
The sum of the values for Short tables and Long tables gives the total number of full table scans performed since database instance startup.
A high number of full table scans on long tables might be an indication that table indexes are missing or should be created. You can check whether indexes are missing using the functions of the Database Performance: Tables and Indexes screen (Missing Indexes).
Use Explain one SQL request of the SQL trace to examine the optimizer access path of expensive statements (use the SQL Request (Shared SQL Area)). Determine whether adding a new index or reordering an existing one may be beneficial (Table Scans: Problem Analysis (Oracle)).
By rowid in the Table Fetch section shows the number of lines that were accessed either by index lookup or by specifying a distinct line ID (ROWID) in an SQL statement. High values for this entry indicate heavy use of indexes. This is generally a good sign, though you should examine whether non-selective indexes used in range scans substantially add to this number. Indexes should be made as selective as possible. The index fields should always be arranged thus that the most commonly accessed table fields come first. If more than 20% of the lines in a table are output for a selection, it is advisable to perform a full table scan (that is, do not create an index for this query).
Continued Row shows how often the database system has accessed chained data records. Chained data records are lines that are distributed across several data blocks.
Chained data records lead to an increase in search time as the database system has to read several blocks to merge the data record. This means that additional I/O operations are required. For these reasons, data chaining should be avoided whenever possible.
When accessing tables with fields that use the "long" Oracle data type, chaining is often unavoidable since the line may be too long to fit in one data block. If the ratio Table fetch Continued row / Table fetch By rowid is greater than 1:1000, you should perform a more detailed analysis. You might need to eliminate chaining using a reorganization with BRSPACE.
For more information on reorganization, see Reorganizing Tables with BR*Tools.