Dear all,
We're facing a performance issue using Oracle 11.2.0.3 (Red Hat Enterprise Linux 6.5).
Sometimes database sessions are locked by "library cache lock" and AWR shows the following TOP 5 timed events:
Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
library cache lock | 171 | 91,715 | 536346 | 76.76 | Concurrency |
cursor: pin S wait on X | 413 | 15,584 | 37734 | 13.04 | Concurrency |
DB CPU | | 3,232 | | 2.71 | |
read by other session | 58,805 | 1,714 | 29 | 1.43 | User I/O |
enq: TX - row lock contention | 3 | 1,458 | 485913 | 1.22 | Application |
There's no pattern in blocking / blocker sessions. Sometimes sessions are blocked by other session with "library cache lock" or "library cache: mutex x".
In Mutex Sleep Summary I found a huge value of kkslce [KKSCHLPIN2] and kglhdgh1 64 (compared with values in a normal behaviour).
Abnormal behavior:
Mutex Type | Location | Sleeps | Wait Time (ms) |
Cursor Pin | kkslce [KKSCHLPIN2] | 2,059,418 | 0 |
Library Cache | kglhdgh1 64 | 94,422 | 0 |
Library Cache | kgllkdl1 85 | 29,671 | 0 |
Library Cache | kglllal3 111 | 2,258 | 0 |
Library Cache | kgllldl2 112 | 721 | 0 |
Normal behavior:
Mutex Type | Location | Sleeps | Wait Time (ms) |
Cursor Pin | kksfbc [KKSCHLFSP2] | 1,876 | 0 |
Cursor Pin | kksLockDelete [KKSCHLPIN6] | 714 | 0 |
Library Cache | kglhdgn1 62 | 293 | 0 |
Cursor Pin | kkslce [KKSCHLPIN2] | 189 | 0 |
Library Cache | kgllkdl1 85 | 112 | 0 |
Shared pool size is about 37GB (I don't know why but PRTMV is consuming 11GB).
My first thought was that the issue was related to cursor_sharing=SIMILAR, however I already checked that in 11.2.0.3 SIMILAR will behave as though cursor_sharing was set to FORCE.
Symptoms are:
- Significant database time spent waiting for library cache lock
- Intermittent database slowdowns
Any idea what could be happening?
Thanks a lot
Best Regards