Page History

Perform one of the following options:

• Expand the time frame you are viewing, using the time frame list box, to identify the new execution plan. Expand the time frame until it includes the current time so that the newer execution plan is loaded.

• Examine the different execution plans that were captured, and their real resource consumption.

Try to identify the source of the different execution plans. To resolve the multiple plans, consider the following solutions:

• In the All Changes section view, observe whether any changes took place (such as schema or statistics changes).

• In the Activity tab, observe whether the execution plan is being run by different programs.

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

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 CPU 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:

• 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 does not 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 CPU 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 does not 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 CPU 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 does not 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 focus on the relevant step in the execution plan.

• Get Index Recommendations for the statement.

• Select the finding type to investigate the objects used and their structure in the Objects tab.

• Select the finding type to investigate the step statistics, step resource consumption and step vs. plan over time.

To reduce the Full Scan resource consumption consider:

• Creating an index matching the statement's predicates.

• If full scan is using I/O heavily consider changing the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter 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.

Perform one of the following options:

• Click the Locate icon to focus on the relevant step in the execution plan.

• Select the finding type to investigate step statistics and resource consumption in the Statistics tab.

Perform one of the following options:

• Click the Locate icon to focus on the relevant step in the execution plan.

• Select the finding type to investigate step statistics and resource consumption in the Statistics tab.

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.

• If the sort involves I/O on the temporary tablespace, you can reduce the I/O consumption for the sort operation by changing 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 area size problems. You can either change the values for a specific session using the Alter Session command, or for the entire instance.

Perform one of the following options:

• Click the Locate icon to focus on the relevant step in the execution plan.

• Select the finding type to investigate step statistics and resource consumption in the Statistics tab.

Try to solve the problem at the statement level by one of the following options:

• If the hash operation involves I/O against temporary tablespace, you can reduce the I/O

• consumption for the hash operation by changing the HASH_AREA_SIZE to a higher value.
  If you are using PGA settings, you can change the PGA_Aggregated_Target, so as to avoid hash area size problems. You can either change the values for a specific session using the Alter Session command, or for the entire instance.

Perform one of the following options:

• Click the Locate icon to focus on the relevant step in the execution plan.

• Select the finding type to investigate step statistics and resource consumption in the Statistics tab.

Try to solve the problem at the statement level by one of the following options:

• If the hash operation involves I/O against temporary tablespace, you can reduce the I/O

• consumption for the hash operation by changing 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 area size problems. You can either change the values for a specific session using the Alter Session command, or for the entire instance.