...
Table 12-60 The Average Execution Uses CPU Heavily
...
...
| next | Run the statement with statistics_level=ALL. |
| Advice |
...
Perform one of the following: |
...
- Examine run results in the Extended Statistics tab. Analyze problematic data (such as many logical reads, table scans and more).
|
...
- Explain the statement and use the SQL tab tools to improve performance.
|
| Anchor |
|---|
| HeavyIndexOverhead |
|---|
| HeavyIndexOverhead |
|---|
|
Heavy Index OverheadMost 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.
Table 12-61 Heavy Index Overhead
...
...
| next | Perform one of the following options: |
...
- In the Objects tab, observe the I/O breakdown between the different indexes. Focus on the heaviest index and launch to the Tune Object tab.
|
...
- 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.
|
...
- In the Statements tab, examine the different statements using the index, to learn about index usage patterns.
|
...
- 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: |
...
- When inserts are part of a 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.
|
...
| Info |
|---|
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. |
| Anchor |
|---|
| PreferablePlanDetectedbyOracle |
|---|
| PreferablePlanDetectedbyOracle |
|---|
|
Preferable Plan Detected by OracleThis finding can only appear after running the "Get Best Plan" command in the Bind Variables tab.
...
Table 12-62 Preferable Plan Detected by Oracle
...
...
| next | Perform one of the following options: |
...
- Check the PLAN_HASH_VALUE field that appears in the left pane in the Bind Variables tab and examine the difference between plans of different bind sets.
|
...
- Consider running the statement using a different bind set leading to a different plan, and compare their run time.
|
...
| Advice | If different execution plans result in a fluctuation in run time consider the following: |
...
- Fixing an optimal plan using outlines or profiles.
|
...
- Disabling bind peeking by setting the "_optim_peek_user_binds" parameter to FALSE
|
...
- Consider using literals instead of bind variables.
|
...
Setting "_optim_peek_user_binds" to FALSE will influence all statements running in the selected instance. |
|
| Anchor |
|---|
| LowEndofFetchCount |
|---|
| LowEndofFetchCount |
|---|
|
Low End of Fetch CountThe query activator doesn't fully read the query result set.
...
Verify that the application is not performing a huge data scan for no reason. Unnecessarily large scans are expensive (in terms of CPU and I/O time).
...
Table 12-63 Low End of Fetch Count
...
...
| next | - Examine the statement's execution plan and verify that unnecessary scans are not being performed.
|
...
- Observe which program is calling the statement in the Activity Tab, and check your Application to verify that there is a true need to scan all data and fetch only part of it.
|
...
| Advice | - Consider tuning the statement so that it scans fewer rows, by adding a "FIRST_ROWS" hint.
Compare the result execution plan to the original, and run both statements to evaluate the influence of the change.
|
...
- Consider changing the application’s logic to improve filtering. This will lead to smaller scans.
|
| Anchor |
|---|
| MajorDifferenceBetweenPlans |
|---|
| MajorDifferenceBetweenPlans |
|---|
|
Major Difference Between PlansThis finding may appear when the difference between the best and worse plans, within a selected time frame, is significant in terms of in Oracle time. This indicates that some plans were used, and the best plan consumed significantly less resources of Oracle than the worse plan.
Table 12-64 Significant Differentiation between Best and Worse Plans
...
...
| next | Perform one of the following options: |
...
- Compare the execution plan of the best plan with the execution plan of the worse plan (also includes a comparison of costs). In the Bind variables tab, check if the difference can be attributed to using a different bind set. If not, check if the significant difference can be derived from a change of one of the objects.
|
...
- If you find that the difference between the best and worse plans is derived from a different bind set, consider running the statement using a different bind set and compare their run times.
|
| Advice |
...
If the significant difference between the best and worse plans was derived from using a different bind set consider: |
...
- Fixing an optimal plan using outlines or profiles.
|
...
- Disabling bind peeking by setting the "_optim_peek_user_binds" parameter to FALSE.
|
...
- Using literals instead of bind variables.
If the significant difference between the best and worse plans were derived from a change of one of the objects within the plan:
|
...
- Explore the objects that are being accessed inefficiently in the worst plan, in the Objects tab.
Check if the table grew significantly or if one of the indexes was dropped and can be rebuilt.
|
...
- Consider using outlines or profiles for checking improvements to the worst plan.
|
...
Notice that setting the "_optim_peek_user_binds" parameter to FALSE will influence all statements running in this instance. |
|
| Anchor |
|---|
| NewerExecutionPlanExists |
|---|
| NewerExecutionPlanExists |
|---|
|
Newer Execution Plan ExistsA newer execution plan than the one you are viewing was collected for the statement during the selected time frame, or during a later time frame.
...