Page History
...
- Heavy Index Overhead
- Extensive Full Table Scan Access
- Full Scan Reading Deleted Blocks
- Index Clustering Factor Very High
- Buffer Wait Contention
- Object or Row Lock Contention
- Bottleneck in RAC Wait
- Many Chained Rows
- Statistics Not Updated on Object
- Changes Detected in Object Structure
- Table Grew Considerable
- Partition Is Accessed Extensively
- Segment Hit Ratio Very Low
- Extensive Activity on Non-explained Statements
- Extensive Index Range Scan Access
- Extensive Full Index Scan Access
- Extensive Fast Full Index Scan Access
- Extensive Index Skip Scan Access
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
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.
| Anchor | ||||
|---|---|---|---|---|
|
Table extensively accessed using full table scans.
...
Increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter.
| Anchor | ||||
|---|---|---|---|---|
|
Table containing many deleted blocks is extensively accessed using full table scans.
...
Increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter.
| Anchor | ||||
|---|---|---|---|---|
|
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).
...
Data B ock Data B ock Data B ock Data B ock Data B ock Data B ock
| Anchor | ||||
|---|---|---|---|---|
|
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:
...
■ If the problem is a global instance problem, increase the number of DBWR processes or I/O
slaves.
| Anchor | ||||
|---|---|---|---|---|
|
Much of the objects (table and indexes) I/O time is spent waiting for a lock on the object specified in the Object column.
...
■ Try to identify the locking statement in the Activity tab 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
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.
| Anchor | ||||
|---|---|---|---|---|
|
Access to table deteriorated as a result of chained rows.
...
■ Move table to tablespace with higher block size.
| Anchor | ||||
|---|---|---|---|---|
|
A significant block change occurred since the last time the object was analyzed, for at least one of the objects related to table.
...
Advice To reduce potential access type problems resulting from statistics that are not up-to-date, consider analyzing the table and checking it periodically.
| Anchor | ||||
|---|---|---|---|---|
|
Changes were made to the table or index structure. Possible changes include: Index was added or dropped.
...
■ Consider adjusting index structure and execution plans.
| Anchor | ||||
|---|---|---|---|---|
|
The table is considerably larger than it was at the start of the time frame.
...
Advice Verify 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.
| Anchor | ||||
|---|---|---|---|---|
|
A large percentage of In Oracle time for the object is spent accessing one partition.
...
■ Add more columns to the index to improve filtering.
| Anchor | ||||
|---|---|---|---|---|
|
The hit ratio, for at least one of the objects related to the table, is very low.
...
Advice If there are no outstanding contentions on the buffer cache consider moving the object into Keep or Recycle buffer cache pools.
| Anchor | ||||
|---|---|---|---|---|
|
Extensive activity on statements that were not explained.
...
Advice Perform an explain on the non-explained statements.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
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.
| Anchor | ||||
|---|---|---|---|---|
|
Index is extensively accessed using 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.
...
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.
| Anchor | ||||
|---|---|---|---|---|
|
Index is extensively accessed using fast full index scans.
...
Increasing the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter.
| Anchor | ||||
|---|---|---|---|---|
|
Index extensively accessed using 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
...