using ASH to track down library cache lock contention

From: Adric Norris <landstander668_at_gmail.com>
Date: Thu, 18 Sep 2014 14:27:35 -0500
Message-ID: <CAJueESpbTtK1cMmO_KdQ2H0HkM=-2Hcpkba3PKL5rY6wCVoErw_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 18 2014 - 21:27:35 CEST