Page History

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 the statement 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 associated statements. The immediate suspect is the DML statements (and update queries) that are not waiting for locks.

• Examine the device activity over time and database files contention.

• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).

• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.

• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.

• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.

• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.

• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).

• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.

• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.

• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.

• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.

• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).

• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.

• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.

• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.

• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.

• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).

• To relieve inter application logical contention, check if the database's I/O activity is balanced.
  Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device.

• To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application.

• To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location.

• Consider storage tiering - a faster device may reduce the I/O wait time significantly.

• Examine the device activity over time and database files contention.

• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• Examine the device activity over time and database files contention.

• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• Examine the device activity over time and database files contention.

• Examine storage device statistics and contention on the Raid Group and Physical Disks.

• Compare the storage devices activity over time.

• Examine the storage devices statistics.

• In the Activity tab, check which database files are the most I/O consuming and spread them evenly across the storage devices.

• Consult with the storage administrator and check for other applications using the same storage devices or their underlying physical disks.

• Consult with the storage administrator about the RAID policy. A different striping may spread the I/O load across the storage devices.

• Try to determine what is causing the J2EE Caller Service’s high resource consumption.

• In the Activity tab, examine its findings, relevant statements, objects, and the Oracle resources

For resource consumption, these are the possible scenarios:

• Object bottleneck

• . Examine the resource consumption of objects in context of this Caller Service in the Activity tab.

• Statement bottleneck

• . Examine the resource consumption of SQL statements 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.

• Try to determine what is causing the J2EE Caller Service’s high resource consumption and high number of executions.

• In the J2EE application Expert view examine the entry point logic to detect redundant or infinite loop executions.

• In the Activity tab, examine its findings, relevant statements, objects, and the Oracle resources.

For resource consumption, these are the possible scenarios:

• Object bottleneck

• . Examine the resource consumption of objects in context of this Caller Service in the Activity tab.

• Statement bottleneck

• . Examine the resource consumption of SQL statements 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.