This metric represents the number of long and short table scans per second during the sample period. A table is considered 'long' if the table is not cached and if its high-water mark is greater than 5 blocks.
The rest of the information in this section is only valid for this metric when it appears in either the Enterprise Manager Grid Control or the Enterprise Manager Database Control (if applicable).
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
Target Version |
Evaluation and Collection Frequency |
Upload Frequency |
Operator |
Default Warning Threshold |
Default Critical Threshold |
Consecutive Number of Occurrences Preceding Notification |
Alert Text |
pre-10g |
Every 5 Minutes |
After Every Sample |
> |
Not Defined |
Not Defined |
2 |
Total table scan rate is %value%/sec. |
10.1.0.x; 10.2.0.x; 11.1.0.x; 11.2.0.x |
Not Defined |
After Every Sample |
> |
Not Defined |
Not Defined |
2 |
Not Defined |
(DeltaLongScans + DeltaShortScans) / Seconds where:
DeltaLongScans: difference in 'select value from v$sysstat where name='table scans (long tables)'' between end and start of sample period
DeltaShortScans: difference in 'select value from v$sysstat where name='table scans (short tables)'' between end and start of sample period
DeltaShortScans: difference in 'select value from v$sysstat where name='table scans (short tables)'' between end and start of sample period
DBA_index_fast_full_scans_full
Seconds: number of seconds in sample period
A table scan indicates that the entire table is being scanned record-by-record in order to satisfy the query. For small tables that can easily be read into and kept in the buffer cache, this may be advantageous. But larger tables will force many physical reads and potentially push other required buffers out of the cache. SQL statements with large physical read and logical read counts are candidates for table scans. They can be identified through two different methods. The Top Sessions page sorted by Physical Reads displays sessions that are responsible for the current I/O activity. The Top SQL page sorted by Physical Reads lists the SQL statements in the cache by the amount of I/O they have performed. Some of these SQL statements may have high I/O numbers but they may not be attributing to the current I/O load.
Related Topics
About Alerts
About the Metric Detail Page
Editing Thresholds
Understanding Line Charts
Copyright © 1996, 2009, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.