Page History

Perform one of the following options:

• 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.

• 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.

• Try to identify index update patterns (such as, day or night) in the In Oracle graph, in the

• Read/Write Operations tab.

Perform one of the following options:

• When inserts are part of load, batch, or night activity, consider dropping the index before performing the activity, and recreating it afterwards.

• 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 tab 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.

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.

Perform the following options:

• Examine associated statements in the Statements tab. Focus on Full Table Scan access types in the Access Types table.

• Examine column usage for each statement in the Columns table.

• Get Index Recommendations for the object’s statements.

Perform one of the following options:

• 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.

• 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.

Perform the following options:

• Examine associated statements in the Statements tab. Focus on Full Table Scan access types in the Access Types table.

• Examine column usage for each statement in the Columns table.

Perform one or more of the following options:

• Eliminate full table scan by:
  • Creating an index matching the statements predicates.
  • Partitioning the table according to the best predicates existing in the statements.

• 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.

• 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.

Perform the following options:

• Examine associated statements in the Statements tab. Focus on Index Range Scan access types in the Access Types table.

• Examine column usage for each statement in the Columns table.

Perform one of the following options:

• Enhance filtering of the table data blocks by adding columns to the index or ensure that index only accesses top statements.

• 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.