My Oracle Support Banner

How To Trace Specific Cursor Invalidation Using Library Cache Debugging (_KGL_DEBUG) Event (Doc ID 2745080.1)

Last updated on JANUARY 28, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Goal

Invalidation caused by DDL statements like ALTER/DROP/TRUNCATE/GRANT etc leads to library cache wait events like "library cache lock", "library cache pin", "cursor: pin S wait on X",   "library cache: mutex X" etc in an highly concurrent environment. It is very challenging to identify the exact DDL statement(s) that cause invalidation to the dependent cursors. Goal of this document is to explain the method to trace the specific cursor(s) invalidation using _KGL_DEBUG command and to identify the DDL statement causing the invalidation to dependent cursor(s). Corrective actions shall be taken once the invalidating SQL statement is identified with the exact date & time and with PL/SQL stack if any.

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.