using ASH to track down library cache lock contention
- From: Adric Norris <landstander668@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Sep 2014 14:27:35 -0500
I'm trying to track down a recent issue on an 11.2.0.3 Solaris SPARC64
system, where the majority of app-related database sessions unexpectedly
hung for about 10 minutes. The condition cleared just as we were getting
onto the system for troubleshooting, unfortunately, so we weren't able to
capture much detail while the problem was ongoing... I'm therefore trying
to identify the cause primarily via ASH data.
At this point, we're fairly confident that it was due to contention for a
library cache lock.
SQL> select instance_number, session_state, event, count(*)
2 from dba_hist_active_sess_history
3 where sample_time >= to_timestamp( '2014-09-15 14:19', 'YYYY-MM-DD
HH24:MI' )
4 and sample_time < to_timestamp( '2014-09-15 14:28', 'YYYY-MM-DD
HH24:MI' )
5 group by instance_number, session_state, event
6 having count(*) >= 40
7 order by 4 desc, 2, 3, 1;
INSTANCE_NUMBER SESSION EVENT COUNT(*)
--------------- ------- ------------------------------ ----------
1 WAITING library cache lock 6264
3 WAITING library cache lock 6216
2 WAITING library cache lock 6031
1 WAITING db file sequential read 50
2 WAITING db file scattered read 41
Unfortunately, the blocking-session info isn't captured for this type of
wait. ASH does grab the P1/P2/P3 details, but everything I'm finding so far
indicates that you have to match this information against various X$ tables
while the issue is actually occurring. So the question, of course, is
whether or not it's possible to definitively identify the offending session
using just the ASH data.
Any suggestions on how to accomplish this?
Thanx!
--
"In the beginning the Universe was created. This made a lot of people very
angry and has been widely regarded as a bad move." -Douglas Adams
Other related posts:
