Category: Databases

The Enterprise Manager Cloud Control’s Overview of Incidents and Problems section identified ‘Metrics Global Cache Blocks Lost at XXX’ for a relatively new database, suggesting a potential network issue, likely related to the private network interfaces. Troubleshooting was guided by My Oracle Support documents, which outline common causes and initial diagnostic steps. Further actions included making adjustments across nearly all OSI Model Layers; such as enabling jumbo frames, tuning NIC buffers, adjusting IP fragmentation parameters, and optimizing socket buffers as well as refining the database layer. These measures collectively reduced ‘gc block lost’ events to nearly zero.

When encountering the Enq: TX – row lock contention wait event, it’s crucial to identify the root cause before taking action. This contention arises when multiple sessions attempt to modify the same row in a table simultaneously. To resolve it, database administrators should pinpoint the blocking session and sql statement. A detailed analysis of specific SQL statements can provide valuable insights for the development team, facilitating faster resolution.

This content highlights the impact of SQL cursor invalidation on system performance and suggests solutions to reduce the number of hard parses. It emphasizes the importance of avoiding hard parses in OLTP systems and discusses reasons for SQL cursor invalidation, such as schema changes, statistics updates and parameter changes. The implementation of atomic refresh for materialized views is presented as a solution to reduce SQL cursor invalidations leading to improved performance in OLTP systems.