Hi all,
During the last weekend we faced many performance issues; the application nodes drop / join the clusters and as consequence we bounced the client nodes and the database.
The database is in 11.2.0.1 version running in Solaris.
My first action was taking a statspack report level 7 for the period we faced more issues; one day shot and in the top 5 events I have the following entries;
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
enq: TX - row lock contention 7,711 350,880 45504 47.1
log file sync 32,118,676 136,992 4 18.4
CPU time 126,520 17.0
db file sequential read 2,822,786 65,127 23 8.7
log file parallel write 21,925,319 26,980 1 3.6
SQL ordered by Elapsed time for DB: NBDB Instance: nbdb Snaps: 12211 -1223
-> Total DB Time (s): 739,355
-> Captured SQL accounts for 56.4% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
312350.94 27,329 11.43 42.2 23.47 295 4139680750
Module: JDBC Thin Client
SELECT * FROM QRTZ_LOCKS WHERE LOCK_NAME = :1 FOR UPDATE
OS Statistics DB/Inst: NBDB/nbdb Snaps: 12211-12236
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 28,449,425
IDLE_TIME 547,490,536
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
So I supposed the locks, deadlocks are killing the database, but, my manager needs me to convence him that the Locks are the REAL cause of the performance degradation ... I have been looking on the net and seen that there is a method named Hang Analyze, I never used it before but, it seems that thos tool is adequate to convence my manager or to serach of the real cause of the poor performance, is it the appropiate method or there is another approch?. I am posting in this forum as I know there are many experts that could help me in troubleshooting and know the real cause.
Please help me,
Many thanks