QUERY_REWRITE_ENABLED - ORA-00600 - kglpin-bad-lock - Materialized Views

We encountered infamous ORA-00600: internal error code, arguments: [kglpin-bad-lock], [0x2DB07B478] in our development environment (Oracle 12c R1) while executing "UPDATE_AGGREGATES" procedure. The more painful thing was this ORA-00600 with "kglpin-bad-lock" was intermittent, sometimes it was coming and sometimes not. I executed my procedure 10 times to check and ORA-00600 with "kglpin-bad-lock" occurred only trice.

nimish@garg> exec UPDATE_AGGREGATES
BEGIN UPDATE_AGGREGATES; END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kglpin-bad-lock], [0x2DB07B478],
[], [], [], [], [], [], [], [], [], []
ORA-06512: at "APPDEVUSR.UPDATE_AGGREGATES", line 156
ORA-06512: at line 1

As we can see in above execution, the first parameter of ORA-00600 was "kglpin-bad-lock". KGL Pin activity is related to Library Cache, and can be viewed in X$KGLPN. There are 2 other tables X$KGLOB for objects and X$KGLLK for all locking structures on an object in Library Cache (Parse Locks).

Library Cache contains the Parsed SQL and Execution Plans for a given SQL statement, which is shared among multiple users/sessions to avoid re-parsing for SQL statements that are absolutely identical. Library cache locks or Parse Locks deals with dependency mechanism between Database Objects and their dependent objects in Library Cache like Parsed SQL. When a Database Object is modified all SQLs in Library Cache dependent upon the object must be invalidated (breaking the parse locks). Before breaking the Parse Lock (Library cache locks), Library cache pins must be acquired in an Exclusive mode on Library Cache Objects (Parsed SQL), to confirm they are not being used by any of the session and can be dropped. Library Pins will not be available until a session is executing an SQL.

Now coming back to our problem of ORA-00600 with kglpin-bad-lock. The workaround provided was to disable the QUERY_REWRITE_ENABLED parameter for the session to avoid ORA-00600 with "kglpin-bad-lock". The important thing here to note was in "UPDATE_AGGREGATES" procedure, there was no MATERIALIZED VIEW used and also there was no DDL which might cause to invalidate Library Cache objects, but the solution worked.

nimish@garg> ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE;
Session altered.

nimish@garg> exec UPDATE_AGGREGATES
PL/SQL procedure successfully completed.

Most of the developers/dba assume that QUERY_REWRITE_ENABLED parameter is limited to MATERIALIZED VIEWS only and it allows Oracle to rewrite of queries using materialized views if enabled, but actually it is a very vast feature of Oracle Cost based optimizer. QUERY_REWRITE_ENABLED parameter when set to TRUE, allows Oracle checks whether it is advantageous to rewrite the original SQL statement into a semantically equivalent SQL statement with a lower cost, which includes "Join Eliminations", "Pushed Predicates", "Subquery Unnesting", "Query Rewrite with Materialized Views" and others.

I hope you enjoyed reading this article, and there was something to learn/review. :)
Please do write comments and suggestions.


Related Posts:
- ORA-00600 internal error code
- ORA-04031: unable to allocate n bytes of shared memory
- ORA-01555: snapshot too old
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh
- Why Primary Key Foreign Key Relationship and Join Elimination
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- Foreign Key in Oracle Data Warehouse - Best Practice
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example