Oracle findings

Perform one of the following options:

• Examine high wait for CPU statements in the Activity tab.
• Examine high CPU statements usage in the Activity tab.
• Examine CPU utilization in the Statistics tab.
• Try to identify the system processes consuming CPU resources using the Insight Savvy for OS tool.

Perform one of the following options:

• Identify other processes in the system.
• The instance has spent much of its In Oracle time waiting for CPU; every process running in your system affects the available CPU resources.
  Effort spent tuning non-Oracle factors can improve Oracle performance.
• Identify heavy statements using CPU or Waiting for CPU and try to tune them.

Perform one of the following options:

• Examine heavy wait for Other Host Wait statements in the Activity tab.
• Try to identify the system processes consuming OS resources using the Insight Savvy for OS tool.

Perform one of the following options:

• Examine high Memory Wait statements in the Activity tab.
• Try to identify the system processes consuming memory using the Insight Savvy for OS tool.

Perform one of the following options:

• Identify other processes in the system.
  The instance has spent much of its In Oracle time waiting for memory; every process running in your system affects the available memory.
  Effort spent tuning non-Oracle factors can improve Oracle performance.
  For example: the result of setting a high number of MAX_PARALLEL_SERVERS when using a parallel query option.
• Identify heavy statements using High Memory Wait and try to tune them.

Perform one of the following options:

• Examine the Oracle events that are grouped into the Wait for Shared Pool in the Statistics tab.
  Determine the dominant Oracle event and follow the tuning scenario set by this event.
• Examine high Shared Pool Wait statements in the Activity tab.

Perform one of the following options:

• Examine the Oracle events that are grouped into the Wait for Rollback segment. Determine the dominant Oracle event and follow the tuning scenario set by this event.
• Examine the statements or objects with the highest values of Rollback Segment Wait and determine which applications are creating this wait.

To reduce contention on the rollback segment, consider one of the following solutions:

• Add rollback segments, moving them into a less busy tablespace.
• Change the application flow or change the rollback policy (using no logging on specific objects).

Perform one of the following options:

• Examine the related Oracle events (lower area), Redo Activity (upper area), to determine the problem type in the Statistics tab.
• Examine high Redo Log Buffer Wait statements in the Activity tab.

Use any one of the typical problem scenarios described below.

• If the log buffer size is too small, this usually results in long waits for the Log Buffer Space event.
  Consider increasing the Log_buffer parameter.
• If the log buffer size is too big, this usually results in a low number of user commits, high redo wastage statistics, and long waits for the Log File Sync event.
  Consider decreasing the Log_buffer parameter and/or the hidden LOG_I/O_SIZE parameter.
• If there are too many commits, this usually results in long waits for the Log File Sync event and the number of user commits is very high.
  Consider changing the application flow and logic (by decreasing the commit frequency or using bulk commits [resulting in larger transactions]).
• If the LGWR is too slow, check whether Log File Sync is still the dominant event. This may be due to high values for Log File Parallel Write, or because there are not many commits. This may mean that the LGWR is underperforming.
  Consider moving the log file to a faster, dedicated device.

Whenever the Log Buffer Space and Log File Sync events occur together, consider changing the hidden LOG_I/O_SIZE parameter.

Perform one of the following options:

• Examine the Oracle events that are grouped into the Wait for Log Switch and Clear. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics tab.
• Examine the statements with the highest values for this wait and determine which applications are creating this wait in the Activity tab.

If the related Oracle events show too many log switches, try and reduce them by one of the following options:

• Increase the Redo log size (when the Log File Switch (checkpoint incomplete) and/or Log File Switch Completion event is dominant).
• Change the application flow or logging policy (by changing the commit frequency or using No Logging on specific objects).

There can be other reasons for a high Log Switch and Clear wait, such as an LGWR delay where the files cannot be switched until ARCH archiving is completed. This is usually caused by the Log File Switch (archiving needed) event.

Perform one of the following options:

• Examine the Oracle events that are grouped in the Oracle RAC/OPS Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics tab.
• Examine Load balancing between RAC instances in the Activity tab.
• Examine Heavy objects suffering from RAC Waits in the Activity tab.

There are two typical scenarios relevant to RAC Waits. Launch to the Dashboard RAC Database view. This view compares the selected instance with other instances in the same database. From the RAC Database view, examine each of the following issues:

• If there is a balancing instances issue, launch to the Activity tab and identify the root cause for this unbalanced issue.
• If there is a RAC Wait event, compare them to other events in the database in the Statistics tab.
• If there are Objects suffering from RAC Waits, launch to the Activity tab and identify their use across Database instances.

Perform one of the following options:

• Examine Latching view overtime and the Oracle latches that are grouped in the Other Lock Wait. Then determine the dominant Oracle latch or enqueue and follow the tuning scenario set by this latch in the Statistics tab.
• Examine high Other Lock Wait statements in the Activity tab.

Examine the Oracle latches that are grouped into the Other Lock Wait. Determine the dominant Oracle latch or enqueue and follow the tuning scenario set by this event.

Perform one of the following options:

• Examine the Oracle events that are grouped in the Background Process Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics tab.
• Examine high Background Process Wait statements in the Activity tab.

Perform one of the following options:

• Examine the Oracle events that are grouped in the Parallel Query Server Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics tab.
• Examine high Parallel Query Server Wait statements in the Activity tab.

Perform one of the following options:

• Examine the Oracle events that are grouped as Other Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics tab.
• Examine high Other Wait statements in the Activity tab.

Perform one of the following options:

• Examine the Oracle events that are grouped in the Buffer Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics tab.
• If the dominant Oracle event is buffer busy, launch into the Activity tab and locate the objects with the highest buffer wait.

There are two typical scenarios relevant to buffer wait that are determined by the dominant Oracle event:

• Buffer Busy wait event
  There is high contention on specific table blocks. To reduce contention, increase the Freelists or the Pctfree for the table.
• Free Buffer Wait event
  There are no available buffers in the buffer cache. This is usually an I/O wait related problem. Do the following:
  • Try and tune the heaviest statements or objects.
  • If this event persists after statement tuning, try and increase DBWR throughput by adding more DBWR processes or DBWR_I/O_SLAVES. Increase the buffer cache size.

Perform one of the following solutions:

• Examine DBLink relevant statistics in the Statistics tab.
• Try to identify the statement suffering from a high wait for Oracle Comm Wait in the Activity 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: in the TNSNAMES.ORA configuration file and in the LISTENER.ORA configuration file on the Oracle database server.

Perform one of the following options:

• Examine SQL*Net relevant statistics in the Statistics tab.
• Identify top statements that suffer from the high Oracle Client Comm Wait in the Activity tab.

To reduce the amount of data transferred using SQL*Net, consider the following solutions:

• Change the SQL statements so that only needed data is transferred to the client.
  For example, select only columns that are actually used in the client application and only retrieve required rows.
• Check that you are not suffering implicit delays in your TCP/IP network by specifying
  TCP.NODELAY=yes in the SQLNET.ORA files for the Oracle listener and for the application client.
• Specify the parameters TDU and SDU in the connection description on the application client.
  For example: in the TNSNAMES.ORA configuration file and in the LISTENER.ORA configuration file on the Oracle database server.

Perform one of the following options:

• Examine the top statements that suffer from high Resource Manager Wait, and the influence of the wait on the instance in the Activity tab.
• Try to identify the most dominant events related to Resource Manager Wait in the Statistics tab.

To reduce the high Resource Manager Wait, consider the following solutions:

• Examine the Overtime graph for the instance, to measure the severity of the Resource Manager Wait. Also check the top statements that suffer from Resource Manager Wait. Checking the statements can reveal whether the problem relates to a specific statement.
• In the Statistics tab, examine the Oracle events that are grouped to Resource Manager Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event.

Perform one of the following options:

• Examine high MTS wait by programs in the Activity tab.
• Try to identify the most dominant events related to MTS Wait in the Statistics tab.

To reduce high MTS wait, consider the following solutions:

• Examine the Overtime graph for the instance, to measure the severity of the MTS Wait. Look for top programs suffering from MTS Wait.
• Examine the memory usage of MTS connections by querying v$sesstat and v$sessions.
  Measure the maximum amount of UGA memory used at any given moment and divide that amount by the number of current user sessions. This determines the average amount of memory each connection allocates.
• If your application is not suited to MTS, use "dedicated" connections which create a separate server process for each user connection.

For resource consumption, these are the possible scenarios:

• Object wait on statement objects.
  Use SQL findings to identify the heaviest waiting object.
• High CPU consumption without object wait.
  Check the number of executions (to identify possible infinite loops) or intensive statements (with a low average time but a high aggregated CPU time).
• Instance-related wait (such as: internal lock wait, shared pool wait, and redo log wait).
  In this case, switch to the Statistics tab and examine the breakdown of this state in Oracle events.
• A change in the execution plan.
  If this is responsible for the statement's performance degradation, check the History tab and the
  All Plans view to identify the actual change that caused the slowdown.
• Check the Binds tab for possible offensive values resulting in differing execution plans and performance.

Examine the Activity tab for statement exaggerated usage patterns. Try tuning scenarios for resource consumption from the following list:

• Object wait on the statement objects.
  Use SQL findings to identify the heaviest waiting object.
• High CPU consumption without object wait.
  Check the number of executions (to identify possible infinite loops) or intensive statements (with a low average time but a high aggregated CPU time).
• Instance related wait (such as: Internal lock wait, Shared pool wait, Redo log wait).
  In this case, switch to the Statistics tab and examine the breakdown of this state in Oracle events.
• A change in the execution plan.
  This is responsible for the statements performance degradation. Use history and all plans to identify the actual change that caused the slowdown.
• Check the binds tab for possible offensive values resulting in differing execution plans and performance.

Perform one of the following options:

• To tune the object, go to the Tune Object tab.
• Examine the waits for the object in the Activity tab.

To reduce the Waits for the object, follow the findings instructions in the Object tab. In the Object tab, you can examine the following object data:

• Access path pattern to the object (full scans, range scans, and so on)
• Read-Write access patterns
• Partition usage (such as whether there are extreme cases in use)
• Object changes versus performance changes

Perform one of the following options:

• To extensively tune the object, go to the Tune Object tab.
• Examine the Lock for the statement in the Activity tab.

To reduce the lock wait for the table, consider the following solutions:

• Check if the lock appears in the Current tab. If so, examine the lock chain to discover which statement is holding the lock.
• Try to identify the locking statement in the Activity tab, using narrow time frames that match the lock periods. Focus on the locked table and its associated statements. The immediate suspect is the DML statements (and update queries) that are not waiting for locks.

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 identify statement-originated sorts.
  If there are many sorts located in a few statements, try to solve the problem at the statement level by doing one of the following:
  • 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, check the effect of adding that index in the What-If tab.
  • Identify the heavy sort or hash consumer step.
    Run a statement with statistics_level=all. Click the Run and Compare tab. Examine LAST_TEMPSEG_SIZE and MAX_TEMPSEG_SIZE in the extended section of the run results. Change the SORT_AREA_SIZE to a higher value.

To reduce the lock wait for the table, consider the following solutions:

• Check if the lock appears in the Current tab. If so, examine the lock chain to discover the statement holding the lock.
• Try to identify the locking statement in the Activity tab, using narrow time frames that match the lock periods. Focus on the locked table and associated statements. The immediate suspect is the DML statements (and update queries) that are not waiting for locks.

• Examine the device activity over time and database files contention.
• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).
• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.
• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.
• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.
• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.
• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).
• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.
• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.
• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.
• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.
• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).
• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.
• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.
• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.
• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.
• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).
• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.
• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.
• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.
• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.
• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• Examine the device activity over time and database files contention.
• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• Examine the device activity over time and database files contention.
• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• Compare the storage devices activity over time.
• Examine the storage devices statistics.

• In the Activity tab, check which database files are the most I/O consuming and spread them evenly across the storage devices.
• Consult with the storage administrator and check for other applications using the same storage devices or their underlying physical disks.
• Consult with the storage administrator about the RAID policy. A different striping may spread the I/O load across the storage devices.

• Try to determine what is causing the J2EE Caller Service’s high resource consumption.
• In the Activity tab, examine its findings, relevant statements, objects, and the Oracle resources

For resource consumption, these are the possible scenarios:

• Object bottleneck. Examine the resource consumption of objects in context of this Caller Service in the Activity tab.
• Statement bottleneck. Examine the resource consumption of SQL statements in context of this Caller Service in the Activity tab.
• Instance-related wait (such as: internal lock wait, shared pool wait, and redo log wait). In this case, switch to the Statistics tab and examine the breakdown of this state in Oracle events.

• Try to determine what is causing the J2EE Caller Service’s high resource consumption and high number of executions.
• In the J2EE application Expert view examine the entry point logic to detect redundant or infinite loop executions.
• In the Activity tab, examine its findings, relevant statements, objects, and the Oracle resources.

For resource consumption, these are the possible scenarios:

• Object bottleneck. Examine the resource consumption of objects in context of this Caller Service in the Activity tab.
• Statement bottleneck. Examine the resource consumption of SQL statements in context of this Caller Service in the Activity tab.
• Instance-related wait (such as: internal lock wait, shared pool wait, and redo log wait). In this case, switch to the Statistics tab and examine the breakdown of this state in Oracle events.

For resource consumption, these are the possible scenarios:

• Object wait on statement objects.
  Use SQL findings to identify the heaviest waiting object.
• High CPU consumption without object wait.
  Check the number of executions (to identify possible infinite loops) or intensive statements (with a low average time but a high aggregated CPU time).
• Instance-related wait (such as: internal lock wait, shared pool wait, and redo log wait).
  In this case, switch to the Statistics tab and examine the breakdown of this state in Oracle events.
• A change in the execution plan.
  If this is responsible for the statement's performance degradation, check the History tab and the
  All Plans view to identify the actual change that caused the slowdown.
• Check the Binds tab for possible offensive values resulting in differing execution plans and performance.

Try to determine what is causing the J2EE Caller Service to spent a high amount of its resources on performing disk related full scans on the referenced objects:

• Get index recommendations for the SQL statements of the Caller Service. When activating the Recommend, Precise generates a filtered statements workload in a context of the time frame and caller service/Web transaction. Oracle advisor provides enhanced recommendations based on the provided workload. The recommendation can be evaluated by the What-if analysis.
• Examine the Scattered and Direct I/O, translated into full scan access, over time and related objects.
• Examine the Scattered and Direct I/O, translated into full scan access, over time and related devices.

To reduce the I/O consumption for the J2EE Caller Service, 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 objects according to the best predicates existing in the J2EE Caller Service's statements.
• Use a parallel query option.
• Move the objects to another tablespace with a higher block size.

Perform one of the following options:

• Examine the top statements waiting for the Redo Log Buffer events.
• Examine the Redo Log Buffer events for the entire instance.

Use any one of the typical problem scenarios described below.

• If the log buffer size is too small, this usually results in long waits for the Log Buffer Space event.
  Consider increasing the Log_buffer parameter.
• If the log buffer size is too big, this usually results in a low number of user commits, high redo wastage statistics, and long waits for the Log File Sync event.
  Consider decreasing the Log_buffer parameter and/or the hidden LOG_I/O_SIZE parameter.
• If there are too many commits, this usually results in long waits for the Log File Sync event and the number of user commits is very high.
  Consider changing the application flow and logic (by decreasing the commit frequency or using bulk commits [resulting in larger transactions]).
• If the LGWR is too slow, check whether Log File Sync is still the dominant event. This may be due to high values for Log File Parallel Write, or because there are not many commits. This may mean that the LGWR is underperforming.
  Consider moving the log file to a faster, dedicated device.

Whenever the Log Buffer Space and Log File Sync events occur together, consider changing the hidden LOG_I/O_SIZE parameter.

Perform one of the following options:

• Examine the top objects waiting for the Buffer events.
• Examine the Buffer events for the entire instance.

There are two typical scenarios relevant to buffer wait that are determined by the dominant Oracle event:

• Buffer Busy wait event
  There is high contention on specific table blocks. To reduce contention, increase the Freelists or the Pctfree for the table.
• Free Buffer Wait event
  There are no available buffers in the buffer cache. This is usually an I/O wait related problem.
  Do the following:
  • Try and tune the heaviest statements or objects.
  • If this event persists after statement tuning, try and increase DBWR throughput by adding more DBWR processes or DBWR_I/O_SLAVES.
  • Increase the buffer cache size.

Perform the following:

• Examine the temporary I/O activity over time and related statements

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 identify statement-originated sorts.
  If there are many sorts located in a few statements, try to solve the problem at the statement level by doing one of the following:
  • 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, check the effect of adding that index in the What-If tab.
  • Identify the heavy sort or hash consumer step.
    Run a statement with statistics_level=all. Click the Run and Compare tab. Examine LAST_TEMPSEG_SIZE and MAX_TEMPSEG_SIZE in the extended section of the run results. Change the SORT_AREA_SIZE to a higher value.

Perform one of the following options:

• Tune the statement using SQL Tune methodology
• Examine the statement activity over time
• Tune the caller service using J2EE application expert views

For resource consumption, these are the possible scenarios:

• Object wait on statement objects.
  Use SQL findings to identify the heaviest waiting object.
• High CPU consumption without object wait.
  Check the number of executions (to identify possible infinite loops) or intensive statements (with a low average time but a high aggregated CPU time).
• Instance-related wait (such as: internal lock wait, shared pool wait, and redo log wait).
  In this case, switch to the Statistics tab and examine the breakdown of this state in Oracle events.
• A change in the execution plan.
  If this is responsible for the statement's performance degradation, check the History tab and the All Plans view to identify the actual change that caused the slowdown.
• Check the Binds tab for possible offensive values resulting in differing execution plans and performance.

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 identify statement originated sorts.
  If there are many sorts located in a few statements, 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 section of the run results, and set parameters according to the first advice.

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.

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.

To reduce the lock wait for the table, consider the following solutions:

• Check to see if the lock appears in the Current tab. If so, examine the lock chain to identify the statement holding the lock.
• Try to identify the locking statement in the Activity tab using the narrow time frames matching the lock periods. Now focus on the locked table and associated statements. The DML statement (and update queries) that are not waiting for locks are the immediate suspects.

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:

• Select the findings type to identify the tables suffering from Buffer Wait in the Objects tab, or
• Examine Buffer Wait sub-state events in the Statistics tab.

When Buffer Busy Wait is the more dominant Oracle event, consider the following options:

• Increase the free lists for the table to reduce the chances for contention.
• Increase the PCTFREE parameter or decrease the block size for the table to spread the data among many blocks and reduce the chances for contention.

When Free Buffer Wait is the more dominant Oracle event, consider the following options:

• Tune the object's access, so as to reduce the number of blocks fetched.
• If this is a global instance problem, increase the number of DBWR processes or I/O slaves.

Perform one of the following options:

• Examine Redo Log Wait overtime I/O consumption for the statement in the Activity tab, or
• Examine Redo Log Wait relevant statistics in the Statistics tab.

To reduce Redo Log Wait, consider the following solution:

• Check Dashboard findings and overtime graphs to see the overall Redo Log activity for the instance.

Perform one of the following options:

• Examine Undo Wait overtime I/O consumption for the statement in the Activity tab, or
• Examine Undo Wait relevant statistics in the Statistics tab.

To reduce Undo Wait time, consider the following solutions:

• Check Dashboard findings and overtime graphs to see the overall undo activity for the instance.

Perform one of the following options:

• Select the findings type to examine the bind values in the Bind Variables tab.
• Use the Estimate Cost button to check whether the Oracle optimizer identified a better execution plan for a provided set of binds.

Try to identify the source for the different execution plans.

To resolve the multiple plans, consider the following solutions:

• Go to the History tab to see whether there were any changes (such as schema or statistics changes), or
• Go to the Activity tab to see whether it is being run by different programs.

Try to identify the source for the different execution plan costs.

Go to the History tab to check whether there were any changes (such as schema or statistics changes).

Perform one of the following options:

• Launch to the Activity tab to examine the scalability of the statement.
• Examine the activity of the program enabling the statement.

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.

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

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.

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.

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

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.

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 the objects used and their structure in the Objects tab.
• Select the finding type to investigate step statistics and resource consumption in the Statistics tab.

To reduce the Full Scan resource consumption consider:

• Creating an index matching the statement’s predicates.
• If the full scan is using I/O heavily and the step name is a fast full scan, 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 the objects used and their structure in the Objects tab.
• Select the finding type to investigate step statistics and resource consumption in the Statistics tab.

To reduce the Range Scan resource consumption consider:

• Checking the matching level of the Index to discover if there is a mismatch between existing Where predicates and operators with column order in the Index.
• Changing column order in the Index for better matching level or adding a new index. Check the global effect of this change by launching to WhatIF 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 the objects used and their structure in the Objects tab.
• Select the finding type to investigate step statistics and resource consumption in the Statistics tab.

To reduce the Range Skip Scan resource consumption consider:

• Changing column order in the Index for better matching level or adding a new index. Check the global effect of this change by launching to WhatIF 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.

Perform one of the following options:

• Examine the DML statements causing index updates in the Statements tab (see example below). Focus on Index Update access types in the Access Types table.
• Check if the index is being used in execution plans, in the Access Types table, in the Statements tab. If the only access type is Index Update, this may indicate that the index is not being used.
• Try to identify index update patterns (such as, day or night) in the In Oracle graph, in the Read/Write Operations tab.

Perform one of the following options:

• When inserts are part of 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.

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.

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.

Perform the following options:

• Examine associated statements in the Statements tab. Focus on Full Table Scan access types in the Access Types table.
• Examine column usage for each statement in the Columns table.
• Get Index Recommendations for the object’s statements.

Perform one of the following options:

• Eliminate Full Table Scan by:
  • Trying to identify common high selectivity columns for the top statements. Create an index matching the statements predicates.
  • Partitioning the table according to the best predicates existing in the statements.
• Improve Full Table Scan by:
  • Moving the table to another tablespace with a higher block size.
  • Increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter.

Perform the following options:

• Examine associated statements in the Statements tab. Focus on Full Table Scan access types in the Access Types table.
• Examine column usage for each statement in the Columns table.

Perform one or more of the following options:

• Eliminate full table scan by:
  • Creating an index matching the statements predicates.
  • Partitioning the table according to the best predicates existing in the statements.
• Treat deleted blocks:
  • Consider exporting the table data, truncating the table, and then reloading the data.
  • If the dirty blocks problem is repeated, the reorganization solution may be less relevant and the user can use an index using range scan or even full index scan to reduce access to the table.
• Improve full table scan by:
  • Moving the table to another tablespace with a higher block size.
  • Increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter.

Perform the following options:

• Examine associated statements in the Statements tab. Focus on Index Range Scan access types in the Access Types table.
• Examine column usage for each statement in the Columns table.

Perform one of the following options:

• Enhance filtering of the table data blocks by adding columns to the index or ensure that index only accesses top statements.
• If no primary key exists and the index is the most used or essential index for the table, consider sorting the table data according to the index key.

Perform the following options:

• Examine buffer wait over time, in the Activity Tab.
• Examine buffer wait substate events in the Statistics tab.

Perform one of the following options:

• If Buffer Busy wait is the more dominant Oracle event, consider doing the following:
  • Increase the free lists for the table to reduce the chances of contention.3
  • Increase the PCTFREE parameter or decrease the block size for the table, to distribute data among many blocks and reduce the chances for contention.
• If Free Buffer wait is the more dominant Oracle event, consider doing the following:
  • Tune access to the object, to reduce the number of blocks fetched.
  • If the problem is a global instance problem, increase the number of DBWR processes or I/O slaves.

To reduce the lock wait for the object, perform one of the following:

• Check to see if the lock appears in the Current tab. If so, examine the lock chain to identify which statement is holding the lock.
• Try to identify the locking statement in the Activity tab using smaller time frames that match the lock periods. Focus on the locked table and associated statements. The DML statements (and update queries) that are NOT waiting for locks should be the immediate suspects.

The object is suffering because it is accessing an object that suffers from chained rows. Chained rows are typically caused by the Insert operation. To solve the problem, perform one of the following:

• Increase the PCTFREE parameter (can be by alter or move table).
• Reorganize table (export/import or manually reorganize table).
• Move table to tablespace with higher block size.

Perform one of the following options:

• Try to determine whether the changes in object structure and changes in performance are related by comparing the In Oracle graph and the Changes graph in the Read/Write Operations tab. If it seems that performance deteriorated as a result of the change in table or index structure, consider rolling back the change.
• Consider adjusting index structure and execution plans.

Examine if there is a correlation between table growth and performance degradation in the Read/Write Operations tab.

Perform the following options:

• Examine the In Oracle activity of the partition, in the Partitions tab. Check if the massive activity spent accessing the partition is abnormal.
• Examine the activity of statements accessing the partition in the Activity tab.

Perform one of the following options:

• If the partition is a table partition:
  • Create local or global indexes for statements that access the partition.
  • Subpartition the partitions.
  • If the partitions are not balanced well, consider building the partitioned table with new partition keys.
• If the partition is an index partition:
  • Subpartition the partitions.
  • Add more columns to the index to improve filtering.

Perform the following options:

• Examine associated statements in the Statements tab. Focus on the All access type in the Access Types table.
• Examine buffer cache usage in the Statistics tab. Check if there is an overall wait on the Free Buffer event.

If the index structure does not fit the Where predicates of the top statements consider doing one of the following:

• Add columns to the index in the right sequence (columns with high selectivity and equal predicates should be first) to improve the matching level. This leads to a better filtering of leaf pages.
• Change the sequence of columns in the index to the optimal sequence (columns with high selectivity and equal predicates should be first) to improve the matching level. This leads to a better filtering of leaf pages.

Table: TAB1 (C1 number, C2 number, C3 number, C10 Date)

Index: IX1 (C1,C2,C5)

Statement: select * from TAB1 where C1=:h and C5=10; Execution plan uses IX1 in Index Range Scan

In this statement the matching level of the index is 1. This means that Oracle uses only C1 to filter index leaf pages, it cant match C5=10 against the index tree because of the absence of a C2= predicate. Because C1 is not selective, many irrelevant index leaf pages can be read. Oracle will apply the C5=10 predicate on the index keys to screen irrelevant table ROWIDs. An index on C1 followed by C5 would be more efficient for the query.

Perform the following:

• Examine associated statements in the Statements tab.
• Focus on the Full Index Scan access type in the Access Types table.
• Examine column usage, for each statement in the Columns table.

Perform one of the following options:

• Full index scan, can be eliminated by:
  • Identifying common high selectivity columns for the top statements. Create an index matching the statements predicates.
  • Partitioning the table according to the best predicates existing in the statements.
• Full index scan, can be improved by switching to Fast Full Index Scan when the index is not used to save sorts (a fast full scan retrieves the rows according to the index key). Perform one of the following options to enable this access path:
  • Ensure that the Fast_Full_Scan_Enabled parameter = yes.
  • Use the "Index_ffs" hint for major statements that use the full index scan, to check if the fast full scan improves performance. When the full index scan doesn't save sort results, performance may improve considerably.

Table: TAB1 (C1 number, C2 number, C3 number, C10 Date)

Index: IX1 (C1)

Statement: select C1 from TAB1 Order by C1; Execution plan uses IX1 in Full Index Scan

In this case a full index scan is the best option. Because there are no filtering predicates, there is no need to access the table blocks and the sort operation is avoided.

Perform the following:

• Examine associated statements in the Statements tab.
• Focus on the Fast Full Index Scan access type in the Access Types table.
• Examine column usage for each statement in the Columns table.

Perform one of the following options:

• Fast Full index scan can be eliminated by:
  • Identifying common high selectivity columns for the top statements. Create an index matching the statements predicates.
  • Partitioning the table according to the best predicates existing in the statements.
• Fast Full Index scan can be improved by:
  • Moving the index to another space with a higher block size.
  • Increasing the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter.

Perform the following:

• Examine associated statements in the Statements tab.
• Focus on the Index Skip Scan access type in the Access Types table.
• Examine column usage for each statement in the Columns table.

Perform one of the following options:

• Change the columns sequence within the index that use the index skip scan. Check the effect of this change in the What-If tab.
• Identify common high selectivity columns for top statements. Create an index matching the statements predicates.
• If you cannot create a new index, use hints, such as, "full" and "index_ffs" to determine if by using these access types you can achieve better performance results.

Table: TAB1 (C1 number, C2 number, C3 number, C10 Date)

Index: IX1 (C1,C2) (C1 has two distinct values Yes and No)

Statement: select * from TAB1 where C2=10;

Execution plan uses Index Skip Scan on IX1.

In this case Oracle has to perform two range scans on the index—one with a key of (Yes,10) and another with a key of (No,10)and then unite the results. The more distinct values defined for C1, the more index scanning required.

Defining a new index on (C2), or changing the column sequence in IX1 to be (C2,C1), enables a more efficient access path for the Index Range Scan.