Muita contenção em ‘library cache lock’

[eversonpizaParticipante]

Sobre como eu cheguei a conclusão que estou tento muito contenção desse tipo, eu peguei essa informação em vários locais, inclusive no AWR, abaixo segue um select que fiz na gv$active_session_history

SELECT h.event
,h.wait_class
,count(*) total
,sum(h.wait_time + h.time_waited)/1000000 total_wait_time
FROM
gv$active_session_history h,
v$event_name e
WHERE h.sample_time BETWEEN sysdate - (1/24) AND sysdate
AND h.event_id = e.event_id
GROUP BY h.event, h.wait_class
ORDER BY total_wait_time DESC;

                                                                 TOT_WAIT

EVENT WAIT_CLASS TOT (s)

---

library cache lock Concurrency 81,523 39,751.58
log file sync Commit 1,054 444.64
row cache lock Concurrency 830 388.63
cr request retry Other 572 348.75
gc buffer busy Cluster 788 324.69
gc cr block busy Cluster 351 51.85
gc current block busy Cluster 401 43.62
enq: TX - row lock contention Application 54 19.96
gcs drm freeze in enter server mode Other 47 18.89
gc cr block 3-way Cluster 351 15.05
gc current block 3-way Cluster 318 11.47
gc remaster Cluster 16 11.16
db file sequential read User I/O 998 8.07
gc current grant busy Cluster 118 6.00
gc cr block 2-way Cluster 315 4.51
gc current block 2-way Cluster 376 4.28
library cache pin Concurrency 15 3.44
enq: TX - index contention Concurrency 30 3.29
db file scattered read User I/O 317 2.89
gc cr block congested Cluster 18 1.75
gc cr grant 2-way Cluster 42 1.26
gc current block congested Cluster 12 0.88
DFS lock handle Other 14 0.84
...