Recently I was asked to help with a case involving library cache locks, and even though it was really simple, I think it’s good for illustrating a few points in performance analysis.
As it often happens, it all started with customers complaining about “the database being slow”. I asked for more specific details, and I found that it wasn’t the entire database, but rather a reporting subsystem. Since I didn’t have specific session sid or sql_id at hand, I started with analyzing a 30-min AWR report, and since there wasn’t much time to look at everything, I just focused on top timed events:
Event Waits Time (s) Avg Wait(ms) %Total Wait Class CPU time 16,188 46.2 db file sequential read 18,230,956 11,787 1 33.6 User I/O db file parallel write 278,480 4,509 16 12.9 System I/O library cache lock 1,131 3,297 2,915 9.4 Concurrency log file sync 120,356 1,686 14 4.8 Commit
