Page History

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.