Oracle Database Discussions

Hi All,

Sometime back we were seeing library cache lock in one of our database.

One of my colleague execute alter system flush shared pool.

After some time the library cache event was gone however i see the enq:HW – contention as the top event in AWR

before flushing shared pool.

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
library cache lock 2,356,438 64,073 27 71.26 Concurrency
library cache: mutex X 4,780,489 21,881 5 24.34 Concurrency
db file sequential read 329,465 2,279 7 2.54 User I/O
cursor: pin S wait on X 50,922 615 12 0.68 Concurrency
DB CPU 481 0.53

after flushing shared pool

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: HW - contention 40,570 59,928 1477 77.45 Configuration
db file sequential read 2,455,030 12,175 5 15.73 User I/O
DB CPU 2,419 3.13
log file sync 214,483 1,551 7 2.00 Commit
enq: TX - row lock contention 396 443 1118 0.57 Application

Just wanted to know is there any relation between enq:HW – contention and library cache lock.

Kindly suggest.

Thanks in advance.

Thanks and Regards,