Heavy Index Overhead
Most of the I/O wait on indexes is due to the fetching of index pages from disk that reflect changes made by INSERT, DELETE, UPDATE, or MERGE statements. The index does not appear in the execution plan.
Heavy Index Overhead findings |
| Description |
| What to do next | Perform one of the following options: n Examine the DML statements causing index updates in the Statements tab (see example below). Focus on Index Update access types in the Access Types table. n Check if the index is being used in execution plans, in the Access Types table, in the Statements tab. If the only access type is Index Update, this may indicate that the index is not being used. n Try to identify index update patterns (such as, day or night) in the In Oracle graph, in the Read/Write Operations tab. |
| Advice | Perform one of the following options: n When inserts are part of load, batch, or night activity, consider dropping the index before performing the activity, and recreating it afterwards. n If the index is not used in execution plans consider dropping the index or unused columns from the index to reduce index overhead. If the index is used in execution plans, launch to the What-If workspace to see which statements may be effected by this change. The Statements tab shows the activity and execution plans that were detected during the selected time frame and may not reflect the activity of all the statements and execution plans executed during this time frame. Proceed with caution when determining whether to drop an index or delete a column from an index. |
| Example | Table: INSERTED_TABLE (C1 number,C2 date, C3 varchar2(128), C4 number) Indexes on table: IX1 (C1,C2) IX2(C4,C3) Statement: Insert into INSERTED_TABLE values (:h1,:h2,:h3,:h4) In this example, Oracle fetches the relevant index blocks of the two indexes, for the new rows, even though the indexes do not appear in the execution plan. The I/O wait accumulated while fetching these index blocks is considered to be an index update. |
Extensive Full Table Scan Access
Table extensively accessed via Full Table Scans.
Extensive Full Table Scan Access findings |
| Description |
| What to do next | Perform the following options: n Examine associated statements in the Statements tab. Focus on Full Table Scan access types in the Access Types table. n Examine column usage for each statement in the Columns table. |
| Advice | Perform one of the following options: n Eliminate Full Table Scan by: Trying to identify common high selectivity columns for the top statements. Create an index matching the statements predicates. Partitioning the table according to the best predicates existing in the statements. n Improve Full Table Scan by: Moving the table to another tablespace with a higher block size. Increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter. |
Full Scan Reading Deleted Blocks
Table containing many deleted blocks is extensively accessed via full table scans.
Full Scan Reading Deleted Blocks findings |
| Description |
| What to do next | Perform the following options: n Examine associated statements in the Statements tab. Focus on Full Table Scan access types in the Access Types table. n Examine column usage for each statement in the Columns table. |
| Advice | Perform one or more of the following options: n Eliminate full table scan by: Creating an index matching the statements predicates. Partitioning the table according to the best predicates existing in the statements. n Treat deleted blocks: Consider exporting the table data, truncating the table, and then reloading the data. If the dirty blocks problem is repeated, the reorganization solution may be less relevant and the user can use an index using range scan or even full index scan to reduce access to the table. n Improve full table scan by: Moving the table to another tablespace with a higher block size. Increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter. |
Index Clustering Factor Very High
Intensive I/O wait activity on table due to a range scan carried out by an index with a bad clustering factor (mismatch between physical order of rows in table and order of ROWIDs from the index range scan leads to re-reading of table blocks).
Index Clustering Factor Very High findings |
| Description |
| What to do next | Perform the following options: n Examine associated statements in the Statements tab. Focus on Index Range Scan access types in the Access Types table. n Examine column usage for each statement in the Columns table. |
| Advice | Perform one of the following options: n Enhance filtering of the table data blocks by adding columns to the index or ensure that index only accesses top statements. n If no primary key exists and the index is the most used or essential index for the table, consider sorting the table data according to the index key. |
The following example shows the effect that a bad clustered index can have on performance when an index is scanned:
The figure below shows an example of an index with a good clustering factor. In this example, the root is read first, followed by the first leaf page. Then the first data block that serves the first three keys matching the three rows in the data block is fetched. In this way the keys and data blocks that follow are read. The I/O operations required by this scan include five index blocks and six data blocks, which is the equivalence of 11 I/O operations.
Index with good clustering factor (low = number of table blocks)
The following figure shows an example of an index with a bad clustering factor.
The index with the bad clustering factor starts in the same way as the index with the good clustering factor. However, when the second key in the index is read, the row for the second key in the first data block has not yet been fetched, so another block must be fetched. By the time Oracle accesses the index key matching the second row in the first table block, it has already been swapped out of memory and needs to be re-read. In the worse case scenario, I/O for the table blocks will be required for every index key. The I/O operations required by this scan include five index blocks and 16 table blocks, which is equivalence of 21 I/O operations. When the difference between the number of blocks and number of rows is great, performance can be greatly impacted.
Index with bad clustering factor (high = number of rows)
Buffer Wait Contention
Object (table and indexes) spent much of its In Oracle time on Buffer wait. This usually occurs as a result of one of the following:
Contention on a table or index buffer in Insert statements (Buffer Busy wait).
Lack of free buffer space when trying to load blocks from a disk (Free Buffer wait).
Buffer Wait Contention findings |
| Description |
| What to do next | Perform the following options: n Examine buffer wait over time, in the Activity Workspace. n Examine buffer wait substate events in the Statistics workspace. |
| Advice | Perform one of the following options: If Buffer Busy wait is the more dominant Oracle event, consider doing the following: n Increase the free lists for the table to reduce the chances of contention. n Increase the PCTFREE parameter or decrease the block size for the table, to distribute data among many blocks and reduce the chances for contention. If Free Buffer wait is the more dominant Oracle event, consider doing the following: n Tune access to the object, in order to reduce the number of blocks fetched. n If the problem is a global instance problem, increase the number of DBWR processes or I/O slaves. |
Object Or Row Lock Contention
Much of the objects (table and indexes) I/O time is spent waiting for a lock on the object specified in the Object column.
Object Or Row Lock Contention findings |
| Description |
| What to do next | Examine the statement in the Activity workspace. |
| Advice | To reduce the lock wait for the object, perform one of the following: n Check to see if the lock appears in the Current workspace. If so, examine the lock chain to identify which statement is holding the lock. n Try to identify the locking statement in the Activity workspace using smaller time frames that match the lock periods. Focus on the locked table and associated statements. The DML statements (and update queries) that are NOT waiting for locks should be the immediate suspects. |
Bottleneck in RAC Wait
The object (table and indexes) spent much of its In Oracle time waiting for a RAC activity to complete on the object specified in the Object column.
Bottleneck in RAC Wait findings |
| Description |
| What to do next | Examine the statement in the Activity workspace |
| Advice | The object is suffering RAC wait because several instances are using it simultaneously. To solve this problem, identify all programs currently accessing the object and try to avoid accessing it concurrently. |
Many Chained Rows
Access to table deteriorated as a result of chained rows.
Many Chained Rows findings |
| Description |
| What to do next | Examine table dictionary information. |
| Advice | The object is suffering because it is accessing an object that suffers from chained rows. Chained rows are typically caused by the Insert operation. To solve the problem, perform one of the following: n Increase the PCTFREE parameter (can be by alter or move table). n Reorganize table (export/import or manually reorganize table). n Move table to tablespace with higher block size. |
Statistics Not Updated On Object
A significant block change occurred since the last time the object was analyzed, for at least one of the objects related to table.
Statistics Not Updated On Object findings |
| Description |
| What to do next | Examine the dictionary details of the object in the Details section. Look at the Read/Write Operation Tab to quantify the magnitude of the block change. |
| Advice | To reduce potential access type problems resulting from statistics that are not up-to-date, consider analyzing the table and checking it periodically. |
Changes Detected In Object Structure
Changes were made to the table or index structure. Possible changes include:
Index was added or dropped.
Partitions or subpartitions were added or dropped.
Table was altered (columns were added).
Changes Detected In Object Structure findings |
| Description |
| What to do next | Examine the changes in the Changes graph in the Read/Write Operations tab. |
| Advice | Perform one of the following options: n Try to determine whether the changes in object structure and changes in performance are related by comparing the In Oracle graph and the Changes graph in the Read/Write Operations tab. If it seems that performance deteriorated as a result of the change in table or index structure, consider rolling back the change. n Consider adjusting index structure and execution plans. |
Table Grew Considerably
The table is considerably larger than it was at the start of the time frame.
Table Grew Considerably findings |
| Description |
| What to do next | Examine if there is a correlation between table growth and performance degradation in the Read/Write Operations tab. |
| Advice | Make sure that full scans are not widely used for the table, that the existing indexes correlate with the table growth, and that no new indexes are required. You can also check Materialized Views usage. |
Partition Is Accessed Extensively
A large percentage of In Oracle time for the object is spent accessing one partition.
Partition Is Accessed Extensively findings |
| Description |
| What to do next | Perform the following options: n Examine the In Oracle activity of the partition, in the Partitions tab. Check if the massive activity spent accessing the partition is abnormal. n Examine the activity of statements accessing the partition in the Activity workspace. |
| Advice | Perform one of the following options: If the partition is a table partition: n Create local or global indexes for statements that access the partition. n Subpartition the partitions. n If the partitions are not balanced well, consider building the partitioned table with new partition keys. If the partition is an index partition: n Subpartition the partitions. n Add more columns to the index to improve filtering. |
Segment Hit Ratio Very Low
The hit ratio, for at least one of the objects related to the table, is very low.
Segment Hit Ratio Very Low findings |
| Description |
| What to do next | Perform the following options: n Examine associated statements in the Statements tab. Focus on the All access type in the Access Types table. n Examine buffer cache usage in the Statistics workspace. Check if there is an overall wait on the Free Buffer event. |
| Advice | If there are no outstanding contentions on the buffer cache consider moving the object into Keep or Recycle buffer cache pools. |
Extensive Activity On Non-explained Statements
Extensive activity on statements that were not explained.
Extensive Activity On Non-explained Statements findings |
| Description |
| What to do next | Examine associated statements in the Statements tab. Focus on non-explained statements in the Access Types table. |
| Advice | Perform an explain on the non-explained statements. |
Extensive "Index Range Scan" Access
Extensive I/O wait was experienced, as a result of range scans on the index. Although this may be normal, it can often indicate a matching level problem, indicating that the structure of the index can be improved.
Extensive "Index Range Scan" Access findings |
| Description |
| What to do next | Examine statements using the index in the Statements tab. Examine column usage, selectivity and matching level (see example) for the top statements, in the Columns table to assess the efficiency of the index. |
| Advice | If the index structure does not fit the Where predicates of the top statements consider doing one of the following: n Add columns to the index in the right sequence (columns with high selectivity and equal predicates should be first) to improve the matching level. This leads to a better filtering of leaf pages. n Change the sequence of columns in the index to the optimal sequence (columns with high selectivity and equal predicates should be first) to improve the matching level. This leads to a better filtering of leaf pages. |
| Example | Table: TAB1 (C1 number, C2 number, C3 number, C10 Date) Index: IX1 (C1,C2,C5) Statement: select * from TAB1 where C1=:h and C5=10; Execution plan uses IX1 in Index Range Scan In this statement the matching level of the index is 1. This means that Oracle uses only C1 to filter index leaf pages, it cant match C5=10 against the index tree because of the absence of a C2= predicate. Because C1 is not selective, many irrelevant index leaf pages can be read. Oracle will apply the C5=10 predicate on the index keys to screen irrelevant table ROWIDs. An index on C1 followed by C5 would be more efficient for the query. |
Extensive “Full Index Scan” Access
Index is extensively accessed via full index scans. This is sometimes done to avoid sorts, when the sort order matches the leading portion of the index key, or to avoid accessing table blocks, when all the columns required by the query exist in the index key.
Extensive “Full Index Scan” Access findings |
| Description |
| What to do next | Perform the following: n Examine associated statements in the Statements tab. n Focus on the Full Index Scan access type in the Access Types table. n Examine column usage, for each statement in the Columns table. |
| Advice | Perform one of the following options: n Full index scan, can be eliminated by: Identifying common high selectivity columns for the top statements. Create an index matching the statements predicates. Partitioning the table according to the best predicates existing in the statements. n Full index scan, can be improved by switching to Fast Full Index Scan when the index is not used to save sorts (a fast full scan retrieves the rows according to the index key). Perform one of the following options to enable this access path: Ensure that the Fast_Full_Scan_Enabled parameter = yes. Use the "Index_ffs" hint for major statements that use the full index scan, to check if the fast full scan improves performance. When the full index scan doesn't save sort results, performance may improve considerably. |
| Example | Table: TAB1 (C1 number, C2 number, C3 number, C10 Date) Index: IX1 (C1) Statement: select C1 from TAB1 Order by C1; Execution plan uses IX1 in Full Index Scan In this case a full index scan is the best option. Because there are no filtering predicates, there is no need to access the table blocks and the sort operation is avoided. |
Extensive "Fast Full Index Scan" Access
Index is extensively accessed via fast full index scans.
Extensive "Fast Full Index Scan" Access findings |
| Description |
| What to do next | Perform the following: n Examine associated statements in the Statements tab. n Focus on the Fast Full Index Scan access type in the Access Types table. n Examine column usage for each statement in the Columns table. |
| Advice | Perform one of the following options: n Fast Full index scan can be eliminated by: Identifying common high selectivity columns for the top statements. Create an index matching the statements predicates. Partitioning the table according to the best predicates existing in the statements. n Fast Full Index scan can be improved by: Moving the index to another space with a higher block size. Increasing the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter. |
Extensive "Index Skip Scan" Access
Index extensively accessed via index skip scans which often means that the index structure does not fit the query in the best possible way, and leads Oracle to perform heavy activity against the index
Extensive "Index Skip Scan" Access findings |
| Description |
| What to do next | Perform the following: n Examine associated statements in the Statements tab. n Focus on the Index Skip Scan access type in the Access Types table. n Examine column usage for each statement in the Columns table. |
| Advice | Perform one of the following options: n Change the columns sequence within the index that use the index skip scan. Check the effect of this change in the What-if workspace. n Identify common high selectivity columns for top statements. Create an index matching the statements predicates. n If you cannot create a new index, use hints, such as, "full" and "index_ffs" to determine if by using these access types you can achieve better performance results. |
| Example | Table: TAB1 (C1 number, C2 number, C3 number, C10 Date) Index: IX1 (C1,C2) (C1 has two distinct values Yes and No) Statement: select * from TAB1 where C2=10; Execution plan uses Index Skip Scan on IX1. In this case Oracle has to perform two range scans on the index—one with a key of (Yes,10) and another with a key of (No,10)and then unite the results. The more distinct values defined for C1, the more index scanning required. Defining a new index on (C2), or changing the column sequence in IX1 to be (C2,C1), enables a more efficient access path for the Index Range Scan. |
