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
| Anchor | ||||
|---|---|---|---|---|
|
...
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
■ 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...