Page History
...
- High CPU Wait
- High Other Host Wait
- High Memory Wait
- High Shared Pool Wait
- High Rollback Segment Wait
- High Redo Log Buffer Wait
- High Log Switch and Clear Wait
- High RAC/OPS Wait
- High Other Lock Wait
- High Background Process Wait
- High Parallel Query Server Wait
- High Buffer Wait
- High Other Wait
- High Remote Query Wait
- High Client Communication Wait
- High Resource Manager Wait
- High MTS Wait
- Heavy Statement
- Frequently Executed Statement
- Heavily Accessed Object
- Locked Object
- High Sorts on Disk
- High Undo Activity
- Heavily Accessed Cluster
- Locked Cluster
- Storage Contention on Device (Clariion)
- Storage Contention on Device (Symmetrix Thick)
- Storage Contention on Device (Symmetrix Thin)
- Storage Contention on Device (Symmetrix F.A.S.T. VP)
- Storage Contention on Redo Logs and DB Files
- Storage Contention on Temporary Objects
- Heavy Storage Device Holding Undo Objects
- Unbalanced Storage Devices Activity
- Heavy J2EE Caller Service
- High SQL Executions for J2EE Caller Service
...
| Description | |
|---|---|
| What to do next | Perform one of the following options:
|
| 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. |
| Anchor | ||||
|---|---|---|---|---|
|
Your instance has spent much of its In Oracle time waiting for memory.
Table 12-3 High Memory Wait
...
| Description |
|---|
| What to do |
...
| next | Perform one of the following options: |
...
|
...
|
| Advice |
...
Perform one of the following options: |
...
|
...
|
| Anchor | ||||
|---|---|---|---|---|
|
Your instance has spent much of its In Oracle time waiting for the group event Shared Pool Wait.
Table 12-4 High Shared Pool Wait
...
| Description |
|---|
| What to do |
...
| next | Perform one of the following options: |
...
|
...
|
| Advice |
...
| Common scenarios for this wait occur when the shared pool is either too small or too big. Verify that your shared pool is sized according to the type of application being used (cursor sharing, literals usage, and so on.) |
| Anchor | ||||
|---|---|---|---|---|
|
Your instance has spent much of its In Oracle time waiting for the group event Rollback Segment Wait.
Table 12-5 High Rollback Segment Wait
...
| Description |
|---|
| What to do |
...
| next | Perform one of the following options: |
...
|
...
|
...
| Advice | To reduce contention on the rollback segment, consider one of the following solutions: |
...
|
...
|
| Anchor | ||||
|---|---|---|---|---|
|
Your instance has spent much of its In Oracle time waiting for the Redo Log.
Table 12-6 High Redo Log Buffer Wait
...
| Description |
|---|
| What to do |
...
| next | Perform one of the following options: |
...
|
...
|
| Advice |
...
Use any one of the typical problem scenarios described below. |
...
|
...
|
...
|
...
Whenever the Log Buffer Space and Log File Sync events occur together, consider changing the hidden LOG_I/O_SIZE parameter. |
| Anchor | ||||
|---|---|---|---|---|
|
Your instance has spent much of its In Oracle time waiting for the group event Log Switch and Clear.
Table 12-7 High Log Switch and Clear Wait
...
| Description |
|---|
| What to do |
...
| next | Perform one of the following options: |
...
|
...
|
| Advice |
...
If the related Oracle events show too many log switches, try and reduce them by one of the following options: |
...
|
...
|
...
|
...
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. |
| Anchor | ||||
|---|---|---|---|---|
|
Your instance has spent much of its In Oracle time waiting for RAC or OPS.
Table 12-8 High RAC/OPS Wait
...
| Description |
|---|
| What to do |
...
| next | Perform one of the following options: |
...
|
...
|
...
|
| 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: |
...
|
...
|
...
|
| Anchor | ||||
|---|---|---|---|---|
|
Your instance has spent much of its In Oracle time waiting for a latch.
Table 12-9 High Other Lock Wait
...
| Description |
|---|
| What to do |
...
| next | Perform one of the following options: |
...
|
...
|
| 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. |
| Anchor | ||||
|---|---|---|---|---|
|
Your instance has spent much of its In Oracle time waiting for the group event Background Process Wait.
...
Advice Examine the Oracle events that are grouped into the Background Processes Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event.
High Parallel Query Server Wait
Your instance has spent much of its In Oracle time waiting for the group event Parallel Query Server Wait.
...
Advice Examine the Oracle events that are grouped into the Parallel Query Server Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event.
High Other Wait
Your instance has spent much of its In Oracle time waiting for the group event Other Wait.
...
Advice In the Statistics tab, examine the Oracle events that are grouped as Other Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event.
High Buffer Wait
Your instance has spent much of its In Oracle time waiting for database buffers.
...
DBWR processes or DBWR_I/O_SLAVES. Increase the buffer cache size.
High Remote Query Wait
Your instance has spent much of its In Oracle time waiting for remote queries to complete.
...
For example: in the TNSNAMES.ORA configuration file and in the LISTENER.ORA configuration file on the Oracle database server.
High Client Communication Wait
Your instance has spent much of its In Oracle time waiting for data from the Oracle server.
...
For example: in the TNSNAMES.ORA configuration file and in the LISTENER.ORA configuration file on the Oracle database server.
High Resource Manager Wait
Your instance has spent much of its In Oracle time waiting for the group event Resource Manager Wait.
...
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.
High MTS Wait
Your instance has spent much of its In Oracle time waiting for MTS Wait.
...
■ If your application is not suited to MTS, use "dedicated" connections which create a separate server process for each user connection.
Heavy Statement
The statement is a major consumer of Oracle resources. By tuning the statement, you may free resources needed by other statements and processes.
...
■ Check the Binds tab for possible offensive values resulting in differing execution plans and performance.
Frequently Executed Statement
The statement is a major consumer of Oracle resources. This statement is frequently executed with a low In Oracle time average.
...
■ Check the binds tab for possible offensive values resulting in differing execution plans and performance.
Heavily Accessed Object
Much of the instance In Oracle time was spent on waits (lock, I/O, Buffer, and so on) for the object.
...
■ Object changes versus performance changes
Locked Object
Much of the instance In Oracle time is spent waiting for a lock on the table.
...
■ 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.
High Sorts on Disk
The result table for a sort operation could not be completed in memory and was performed on a temporary tablespace.
...
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.
High Undo Activity
Much of the instance I/O is spent waiting for the Undo object.
...
Advice Examine Undo behavior over time, identify the statement accessing it, and try to tune them.
Heavily Accessed Cluster
Much of the instance I/O is spent waiting for the cluster.
...
Advice Examine Cluster behavior over time, identify the statement accessing it, and try to tune them.
Locked Cluster
Much of the instance time is spent waiting for a lock on the cluster.
...
■ 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.
Storage Contention on Device (Clariion)
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention on Device (Symmetrix Thick)
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention on Device (Symmetrix Thin)
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention on Device (Symmetrix F.A.S.T. VP)
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention on Redo Logs and DB Files
Redo/Transaction Log files are frequently accessed by the database. The majority of the operations performed are writing commands, which cause a heavy load on the underlying disks.
...
Advice It has been detected that the Redo/Transaction Log files share the storage devices (LUNs) with other database files. Consult the storage administrator about provisioning the storage devices (LUNs) better to avoid this.
Storage Contention on Temporary Objects
Temporary tablespace files are frequently accessed by the database. The majority of the operation performed are writing commands, which cause a heavy load on the underlying disks.
...
Advice It has been detected that the temporary tablespace files share the storage devices (LUNs) with other database files. Consult the storage administrator about provisioning the storage devices (LUNs) better to avoid this.
Heavy Storage Device Holding Undo Objects
Undo tablespace files are frequently accessed by the database. The majority of the operation performed are writing commands, which cause a heavy load on the underlying disks.
...
Advice It has been detected that the undo tablespace files share the storage devices (LUNs) with other database files. Consult the storage administrator about provisioning the storage devices (LUNs) better to avoid this.
Unbalanced Storage Devices Activity
There are several storage devices (LUNs) allocated to the instance. However, the I/O activity is not spread evenly across these storage devices. The contention on the heavy storage devices increases the response time for the activities run on them. Such a situation can be caused by imbalanced internal database activity, contention on the storage device by other applications or an inefficient RAID policy.
...
■ Consult with the storage administrator about the RAID policy. A different striping may spread the I/O load across the storage devices.
Heavy J2EE Caller Service
The J2EE Caller Service is a major consumer of Oracle resources. By tuning it, you may free resources needed by other Caller Services and processes.
...
■ Object bottleneck – Examine the resource consumption of objects 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.
High SQL Executions for J2EE Caller Service
The J2EE Caller Service is a major consumer of Oracle resources, issuing and exceptionally high number of SQL Statements executions. By tuning it and reducing the number of executions, you may free resources needed by other Caller Services and processes.
...
■ Object bottleneck – Examine the resource consumption of objects in context of this Caller Service in the Activity tab.
...