Heavy Index Overhead Wait

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

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.

Get Oracle Certifications for all Exams
Free Online Exams.com