Page History

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.