Library Cache Lock Wait Event Due to Recursive Sql with Sql ID 'a6r5q9yqaqabs'
(Doc ID 2431576.1)
Last updated on AUGUST 10, 2018
Applies to:Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
- The DB is experiencing performance issue with high library cache locks from AWR report:
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
library cache lock 1,320,245 15.4K 11.68 61.0 Concurrency
cursor: pin S wait on X 1,209,204 3547.2 2.93 14.0 Concurrency
library cache: mutex X 1,160,748 2572.2 2.22 10.2 Concurrency
- The following recursive sql is taking high parse time:
a6r5q9yqaqabs select obj#, name, stab#, sobj#, sobjd#, ttab#, tobj#, tobjd#, mflags from rmtab$ where (stab# = :1 or ttab# = :1) order by obj#
SQL ordered by Parse Calls
Total Parse Calls: 113,613
Captured SQL account for 38.0% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module PDB Name SQL Text
42,630 42,630 37.52 a6r5q9yqaqabs PDB_PRODHIM select obj#, name, stab#, sobj... <<<<<<<<<<< High No of executions
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!