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
http://www.free-online-exams.com
Most I/O wait on indexes is due to fetching index pages from the disk, reflecting changes made by the DML statement. The indexes do not appear in the execution plan.
| Heavy Index Overhead |
| Description | |
| What to do next | Perform one of the following options: n In the Objects tab, observe the I/O breakdown between the different indexes. Focus on the heaviest index and launch to the Tune Object workspace. n Check if this index is being used in other access types other than Index Overhead. If the only access type is Index Overhead, this may indicate that the index is not being used to filter table rows. n In the Statements tab, examine the different statements using the index, to learn about index usage patterns. n In the Read/Write Operations tab, in the In Oracle graph, try to identify index update patterns (such as, day or night). |
| Advice | Perform one of the following options: n When inserts are part of a 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. Note: 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. |
No comments:
Post a Comment