Re: System state dump

Other than looking at x$kgllk, you can also simply find the object (x$kglob.kglhdadr = v$session_wait.p1raw where event is library cache lock or pin). Find the SQLs in v$sql where users_executing > 0 and lower(sql_text) like '%thatobject%'. Find the sessions in v$session where sql_address = v$sql.address for the rows just found. The last step can also be done with v$open_cursor. But you have to use your judgement all along. E.g, not necessarily all rows selected from v$sql are relevant.

This doesn't work for row cache lock wait. Incidentally, using locally managed tablespace is said to reduce row cache locks.

Yong Huang

mark_at_bobak.net (Mark J. Bobak) wrote in message news:<fe9b0e1b.0210051800.74e3bab4_at_posting.google.com>...
> Hi Chuck,
>
> Oracle has actually published this info. See MetaLink Doc. ID 122793.1.
> It outlines two techniques: One utilizing systemstate dump analysis and
> one using X$KGLLK.
>
> Hope that helps,
>
> -Mark
>
> "Chuck" <chuckh_at_softhome.net> wrote in message news:<anhh60$cupeo$1_at_ID-85580.news.dfncis.de>...
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > Is there any information available on how to interpret the results of
> > a system state dump? Specifically I want to know who is holding and
> > waiting on library cache locks, library cache pins, and row cache
> > locks. I have had an ongoing problem with materialized view refreshes
> > hanging, and when I query v$session_wait I find dozens of sessions
> > waiting on these events for long periods of time. OTS has been able
Received on Tue Oct 08 2002 - 10:24:34 CDT