Page History

Perform one of the following options:

• Click the Locate icon to find the relevant step in the execution plan.
• Select the findings type to investigate the objects used and their structure in the Objects tab.
• Select the Activity tab, locate the statement associated with objects, and drill to the index consumption for the statement in the Activity tab.

To reduce the index I/O consumption, consider the following solutions:

• If the index is not used to prevent a sort operation, consider adding the INDEX_FFS hint and change DB_FILE_MULTIBLOCK_READ_COUNT parameter to a higher value.
• Switch to another index or add a new index.
• Use a parallel query option.
• Move the index to another tablespace with a higher block size.

Findings refer to the whole statement - not to a specific execution plan. If a step does not exist in the selected execution plan (unless this is due to an index overhead), switch to another plan and locate the relevant step.

Perform one of the following options:

• Click the Locate icon to see if the index is used in the execution plan.
• Select the findings type to investigate the objects used and their structure in the Objects tab.
• Select the Activity tab, locate the statement associated with objects, and drill to the index overtime consumption for the statement in the Activity tab.

If this is the result of a range scan, consider one of the following solutions:

• If the index is not used to prevent a sort operation, consider adding the INDEX_FFS hint and change DB_FILE_MULTIBLOCK_READ_COUNT (now standing on X) to a higher value.
• Switch to another index, add a new index, or change the index structure.
• Add columns to the index to enable index only access.
• Switch to a full table scan
• Partition the table or the index
• Create a cluster with just one table in it and a cluster key used as the index key.

If this is due to an index overhead, consider reducing the index overhead by deleting unused indices, or unused columns in used indices.

Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.

Perform one of the following options:

• Click the Locate icon to find the relevant step in the execution plan.
• Select the findings type to investigate the objects used and their structure in the Objects tab.
• Select the Activity tab, locate the statement associated with objects, and drill to the table consumption for the statement in the Activity tab

To reduce the I/O consumption for the table, consider the following solutions:

• Create an index matching the statement's predicates.
• Change the DB_FILE_MULTIBLOCK_READ_COUNT parameter in INIT.ORA to a higher value.
• Partition the table according to the best predicates existing in the statement.
• Use a parallel query option.
• Move the table to another tablespace with a higher block size.

Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.

Perform one of the following options:

• Click the Locate icon to find the relevant step in the execution plan.
• Select the findings type to investigate the objects used and their structure in the Objects tab.
• Select the Activity tab, locate the statement associated with objects, and drill to the table consumption for the statement in the Activity tab.

To reduce the I/O consumption for the table, consider the following solutions:

• Change the index structure by adding a better filtering column, or change the column sequence to improve the matching and screening performed in the index tree.
• Add columns to the index to enable index only access.
• Switch to a full table scan.
• Partition the table or the index.
• Create a cluster with just one table in it and a cluster key used as the index key.

Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.

Perform one of the following options:

• Click the Locate icon to find the relevant step in the execution plan.
• Select the findings type to investigate the objects used and their structure in the Objects tab.
• Select the Activity tab, locate the statement associated with objects, and drill to the object (table/index) consumption for the statement in the Activity tab.

To reduce the I/O consumption for the object, consider the following solutions:

• If this is the result of an SQL loader utility in a direct path, verify that the temporary segments for the index updates are on a fast device.
• Use the SINGLEROW option.

Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.

Perform one of the following options:

• Click the Locate icon to find the relevant step in the execution plan.
• Select the findings type to investigate the objects used and their structure in the Objects tab.
• Select the Activity tab, locate the statement associated with objects, and drill to the object (table/index) consumption for the statement in the Activity tab.

Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.

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

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

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.

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.

To reduce the I/O consumption for the sort operation, consider the following solutions:

• Change the SORT_AREA_SIZE to a higher value.
  If you are using PGA settings, you can change the PGA_Aggregated_Target, so as to avoid sort and hash area size problems. You can either change the values for a specific session using the Alter Session command, or for the entire instance.
• Try to solve the problem at the statement level by one of the following options: Add an index to prevent a sort.
  If your statement has an Order by clause that has columns for a single table, check whether you can add an index. In some cases, you may get an index recommendation that prevents a sort. If you are considering adding an index, you must check the effect of that index.
  To analyze that effect, launch to the What-If tab. Identify the heavy sort or hash consumer step.
  Run a statement with statistics_level=all. Click the Run & Compare tab. Examine the LAST_TEMPSEG_SIZE and MAX_TEMPSEG_SIZE in the extended