Sunday, October 23, 2011

Oracle Instance Related Waits 1/2

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com


Wait for a undo record" & "Wait for stopper event to be increased



Sometimes Parallel Rollback of Large Transaction may become very slow. After killing a large running transaction (either by killing the shadow process or aborting the database) then database seems to hang, or SMON and parallel query servers taking all the available CPU. 


http://3.bp.blogspot.com/-EUK27oWwRq4/TfcDS6fp_MI/AAAAAAAAAB4/xdo51Gdp6h0/s320/1.bmp

http://1.bp.blogspot.com/-kvpD0ZWuv5Q/TfcDjx3YiqI/AAAAAAAAAB8/nlUaImNRSfg/s320/2.bmp



http://2.bp.blogspot.com/-W1DmSwdUSw8/TfcDyTAt3eI/AAAAAAAAACA/faHkCtr3rWQ/s320/3.bmp

In fast-start parallel rollback, the background process SMON acts as a coordinator and rolls back a set of transactions in parallel using multiple server processes.

Fast start parallel rollback is mainly useful when a system has transactions that run a long time before a commit, especially parallel Inserts, Updates, Deletes operations. When SMON discovers that the amount of recovery work is above a certain threshold, it automatically begins parallel rollback by dispersing the work among several parallel processes.

There are cases where parallel transaction recovery is not as fast as serial transaction recovery, because the PQ slaves are interfering with each other. It looks like the changes made by this transaction cannot be recovered in parallel without causing a performance problem. The parallel rollback slave processes are most likely contending for the same resource, which results in even worse rollback performance compared to a serial rollback. 



Solution

======

To disable the parallel rollback by setting the following parameter

fast_start_parallel_rollback = false


Refer to Oracle Note ID 464246.1



High CPU Wait



Your instance has spent much of its In Oracle time waiting for CPU.


High CPU Wait findings


Oracle Wait Event Tuning: Optimization with Oracle Wait Interface and Wait Event Analysis






Description
What to do next
Perform one of the following options:

n   Examine high wait for CPU statements in the Activity workspace.

n  Examine high

n   CPU statements usage in the Activity workspace.

n   Examine CPU utilization in the Statistics workspace.

n   Try to identify the system processes consuming CPU resources using the Insight Savvy for OS tool.
Advice
Perform one of the following options:

n   Identify other processes in the system.

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

n   Identify heavy statements using CPU or Waiting for CPU and try to tune them.



High Other Host Wait



Your instance has spent much of its In Oracle time in Other Host Wait.


High Other Host Wait findings




Description
What to do next
Perform one of the following options:

n   Examine heavy wait for Other Host Wait statements in the Activity workspace.

n   Try to identify the system processes consuming OS resources using the Insight Savvy for OS tool.
Advice
Other Host Wait can result from any of the following causes: asynchronous I/O, gateways, or the use of NFS and TP monitors. Check the statements and programs suffering from this state and check whether the above resources are being utilized efficiently.






High Memory Wait



Your instance has spent much of its In Oracle time waiting for memory.


High Memory Wait findings




Description
What to do next
Perform one of the following options:

n   Examine high Memory Wait statements in the Activity workspace.

n   Try to identify the system processes consuming memory using the Insight Savvy for OS tool.
Advice
Perform one of the following options:

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

n   Identify heavy statements using High Memory Wait and try to tune them.



High Shared Pool Wait



Your instance has spent much of its In Oracle time waiting for the group event Shared Pool Wait.


High Shared Pool Wait findings




Description
What to do next
Perform one of the following options:

n   Examine the Oracle events that are grouped into the Wait for Shared Pool in the Statistics workspace. Determine the dominant Oracle event and follow the tuning scenario set by this event.

n   Examine high Shared Pool Wait statements in the Activity workspace.
Advice
Common scenarios for this wait occur when the shared pool is either too small or too big. Make sure your shared pool is sized according to the type of application being used (cursor sharing, literals usage, and so on.)



High Rollback Segment Wait



Your instance has spent much of its In Oracle time waiting for the group event Rollback Segment Wait.


High Rollback Segment Wait findings




Description
What to do next
Perform one of the following options:

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

n   Examine the statements or objects with the highest values of Rollback Segment Wait and determine which applications are creating this wait.
Advice
To reduce contention on the rollback segment, consider one of the following solutions:

n   Add rollback segments, moving them into a less busy tablespace.

n   Change the application flow or change the rollback policy (using no logging on specific objects).



High Redo Log Buffer Wait



Your instance has spent much of its In Oracle time waiting for the Redo Log.


High Redo Log Buffer Wait findings




Description
What to do next
Perform one of the following options:

n   Examine the related Oracle events (lower area), Redo Activity (upper area), to determine the problem type in the Statistics workspace.

n   Examine high Redo Log Buffer Wait statements in the Activity workspace.
Advice
Use any one of the typical problem scenarios described below.

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

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

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

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



High Log Switch and Clear Wait



Your instance has spent much of its In Oracle time waiting for the group event Log Switch and Clear.


High Log Switch and Clear Wait findings




Description
What to do next
Perform one of the following options:

n   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 workspace.

n   Examine the statements with the highest values for this wait and determine which applications are creating this wait in the Activity workspace.
Advice
If the related Oracle events show too many log switches, try and reduce them by one of the following options:

n   Increase the Redo log size (when the Log File Switch (checkpoint incomplete) and/or Log File Switch Completion event is dominant).

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



High RAC/OPS Wait



Your instance has spent much of its In Oracle time waiting for RAC or OPS.


High RAC/OPS Wait findings




Description
What to do next
Perform one of the following options:

n   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 workspace.

n   Examine Load balancing between RAC instances in the Activity workspace.

n   Examine Heavy objects suffering from RAC Waits in the Activity workspace.
Advice
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:

n   If there is a balancing instances issue, launch to the Activity workspace and identify the root cause for this unbalanced issue.

n   If there is a RAC Wait event, compare them to other events in the database in the Statistics workspace.

n   If there are Objects suffering from RAC Waits, launch to the Activity workspace and identify their use across Database instances.




High Other Lock Wait



Your instance has spent much of its In Oracle time waiting for a latch.


High Other Lock Wait findings




Description
What to do next
Perform one of the following options:

n   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 workspace.

n   Examine high Other Lock Wait statements in the Activity workspace.
Advice
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.


Get Oracle Certifications for all Exams
Free Online Exams.com

No comments: