Oracle Scans Test
Full table scans on a database instance can degrade the performance of the database. This test monitors the extent of full table scans happening on the database.
Note:
This test will not report metrics for an Oracle 12c CDB server.
Target of the test : An Oracle server
Agent deploying the test : An internal agent
Outputs of the test : One set of results for every SID monitored.
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
Percent long table scans: |
The percentage of long table scans happening in the database |
Percent |
Ideally, this value should be lower than 10%. If more than 20% of scans are happening on long tables, the database/accesses to the database may need to be tuned. Full table scans may happen due to several reasons. For instance, the indexes of a table may not be used properly in queries. By tuning the queries, the full table scans can be reduced and the database performance significantly improved. |
Long table scans: |
The number of long table scans that happened on the database instance |
Number |
|
Short table scans: |
The number of short table scans that happened on the database instance during the last measurement period |
Number |
|
Full table scans:
|
The number of full table scans that happened on the database instance during the last measurement period.
|
Number
|
This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. There are two types of full-table scans, those against small tables STR-FTS and large-tables LT-FTS. The rule for evaluative and tuning LT-FTS is simple. We evaluate the query and see if index access would result in less physical reads than the existing full-table scan. This usually involves timing the execution speed for the query (with the set timing on command in SQL*Plus) and timing the query with different index access plans:
Once the fastest execution plan is derived, the tuning professional will enforce the execution plan by creating schema statistics to ensure that the CBO will always use the best index access. The problem with ST-FTS occurs when a popular table is referenced. Because the FTS data blocks are not touched (pinged to the MRU end of the buffer), ST-FTS rows age quickly from the buffer, requiring Oracle to re-read them, over and over again. In Oracle9i and beyond hidden parameter called _adaptive_direct_read that ensures that small table scans are cached. However, it is still a good idea to identify these small tables yourself and cache them in your KEEP pool. The KEEP pool is a wonderful resource for ensuring that an object always resides in the data buffer RAM, and this is one of the few ways to guarantee 10% caching. Now that we see the benefit of caching frequently-referenced table and indexes, we see how the KEEP pool is most important to small objects that are read into the data buffers via full-table scans. Also, remember that frequently-referenced data blocks accessed via an index will tend to remain in the data buffer without using the KEEP pool because they are pinged to the MRU end of the buffer every time they are referenced. |