Page History

To prevent this problem, consider one of the following solutions:

• Increase the MAX_PARALLEL_SERVERS parameter in INIT.ORA while closely monitoring the effect on wait for memory. This will lower the chances for a serial execution, but may cause a high memory consumption.

• Set PARALLEL_AUTOMATIC_TUNING parameter in INIT.ORA to TRUE. This will make Oracle use more sophisticated algorithms in determining the number of PQ processes for each session.

Perform one of the following options:

• Select the findings type to see the SQL text sent to the remote database on the Statistics tab, Other column.

• Examine DBLink relevant statistics in the Statistics tab.

To reduce remote access wait time, consider the following options:

• Launch Precise for Oracle on the remote instance, locate the remote query, and tune the statement.

• Control the driving instance executing the statement by using the DRIVING_SITE hint.

• Tune SQL*Net throughput by checking SDU and TDU settings.

• Include TCP.NODELAY=yes parameter in the SQLNET.ORA configuration file.

• Specify the parameters TDU and SDU in the connection description on the application client.

For example: the TNSNAMES.ORA configuration file and in the LISTENER.ORA configuration file in the Oracle database server.

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.