SQL Performance (MOSC)

MOSC Banner

Interpreting SQL Invalidation (_kgl_debug) trace

edited Oct 19, 2023 5:56PM in SQL Performance (MOSC) 8 comments

Oracle Database 19.10 (RAC 2 node).


We have a report that issues a large SQL statement (~1200 lines, multiple union). This statement takes 20 - 30 seconds to parse in our busy prod environment (and of course, when hard parsing, blocks other instances of the same SQL with "cursor: pin s wait on x). After a recent change to this query (WHERE clause filter change), it has sometimes started experiencing significant hard parsing througout the day (one morning had 700 hard parses in 4 hr period). This SQL is run from a parameterized report and uses bind variables, so we would

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center