-- FILE: libcache_lock.sql
--
-- AUTHOR: Andy Rivenes
--
-- DATE: 01/22/2003
--
-- DESCRIPTION:
-- Query to display library cache lock/pin blockers and waiters
-- Source: Note: 122793.1, HOW TO FIND THE SESSION HOLDING
-- A LIBRARY CACHE LOCK
--
-- The address of the object should allow access through
-- v$open_cursor, v$sql views.
--
-- REQUIREMENTS:
-- Access to x$ tables (connect as sys or sysdba).
--
-- MODIFICATIONS:
--
--
SET LINESIZE 132;
SET PAGESIZE 60;
SET TRIMSPOOL off;
--
COLUMN sid HEADING 'SID' FORMAT 9999;
COLUMN objtyp HEADING 'Object|Type' FORMAT A25;
COLUMN lktyp HEADING 'Lock|Type' FORMAT A4;
COLUMN lkmod HEADING 'Mode|Held' FORMAT A10;
COLUMN lkreq HEADING 'Mode|Request' FORMAT A10;
COLUMN objaddr HEADING 'Address' FORMAT A10;
COLUMN objdef HEADING 'Object' FORMAT A30 WORD_WRAPPED;
--
BREAK ON lk.kgllkhdl SKIP 1;
--
SELECT s.sid,
DECODE(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure/Type', 2, 'Body',
3, 'trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source',
14, 'Java Resource', 32, 'Java Data', TO_CHAR(ob.kglhdnsp)) objtyp,
lk.kgllktype lktyp,
DECODE(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
TO_CHAR(lk.kgllkmod)) lkmod,
DECODE(lk.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
TO_CHAR(lk.kgllkreq)) lkreq,
RAWTOHEX(lk.kgllkhdl) objaddr,
DECODE(ob.kglnaown, NULL, '', ob.kglnaown || '.') || ob.kglnaobj ||
DECODE(ob.kglnadlk, NULL, '', '@' || ob.kglnadlk) objdef
FROM v$session s,
x$kglob ob,
-- dba_kgllock lk
( SELECT kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype FROM x$kgllk
UNION ALL
SELECT kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin' kgllktype FROM x$kglpn ) lk
WHERE lk.kgllkhdl = ob.kglhdadr
AND lk.kgllkuse = s.saddr
AND lk.kgllkhdl IN ( SELECT DISTINCT kgllkhdl
FROM ( SELECT kgllkhdl, kgllkreq FROM x$kgllk
UNION ALL
SELECT kglpnhdl, kglpnreq FROM x$kglpn )
WHERE kgllkreq > 0 )
ORDER BY lk.kgllkhdl,
lk.kgllkreq ASC,
lk.kgllkmod DESC
/
That's the best thing I can do to help you.
On 2003.07.08 23:39, "Chindarkar, Chetan (CONS FIN , Contractor)" wrote:
Here is the information for relevant SID.
SID SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT P1
P1RAW
---------------------------------------------------------------- ----------
--------
P2TEXT P2
P2RAW
---------------------------------------------------------------- ----------
--------
P3TEXT P3
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
---------------------------------------------------------------- ----------
-------- ---------- --------------- -------------------
156 43997 library cache lock
handle address 3830864540
E4565A9C
lock address 4121103412
F5A30C34
10*mode+namespace 31
0000001F 0 101 WAITING
And here is the info from v$lock :
SELECT * FROM v$lock WHERE sid = 156 ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 01A83FD4 01A840A0 156 TX 65635 213866 6 0 5011 0 F58941AC F58941C0 156 TM 1548379 0 3 0 5005 0 F5893B94 F5893BA8 156 TM 2832 0 3 0 5011 0 F58939B4 F58939C8 156 TM 870072 0 3 0 5005 0 F58938C4 F58938D8 156 TM 1548381 0 3 0 4495 0 F58913BC F58913D0 156 TM 1548382 0 3 0 4170 0 F5891074 F5891088 156 TM 1548380 0 3 0 4715 0
7 rows selected.
And here is the info from v$locked_object
SELECT b.name, a.* FROM v$locked_object a, sys.obj$ b WHERE a.object_id = b.obj# and session_id = 156 ;
NAME XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------------------------ OS_USER_NAME PROCESS LOCKED_MODE ------------------------------ --------- ----------- COMMENTS 1 99 213866 1548380 156 CDW collprod 8523 3
COMMENTS 1 99 213866 870072 156 CDW collprod 8523 3
COMMENTS 1 99 213866 1548379 156 CDW collprod 8523 3
COMMENTS 1 99 213866 1548382 156 CDW collprod 8523 3
COMMENTS 1 99 213866 1548381 156 CDW collprod 8523 3
PRODUCTION_LOG_DTL 1 99 213866 2832 156 CDW collprod 8523 3
6 rows selected.
Please advise ......
Thanks - Chetan
-----Original Message----- Sent: Tuesday, July 08, 2003 9:49 PM To: Multiple recipients of list ORACLE-L
You queried everything from v$session_wait and you needed to query only the information relevant to the SID that is waiting. Other then that, the only session in "WAITING" status is waiting for a lock. Your chase is about to continue. Now that you have lock address, go on your way to v$lock table, grasshopper. The legend continues.
On 2003.07.08 21:29, "Chindarkar, Chetan (CONS FIN , Contractor)" wrote: > Gurus , > > We run a Oracle 8.1.7.4.0 dw on Sun Solaris 2.8 version and its a data > warehouse environment. There are lots of SQL Loader jobs which run on a > day-to-day basis. > > Normally it takes 15-20 mins to finish the loading but today it is taking > more than an hour and nothing seems to happen. This is a range partitioned > table. > > Here are the results of the queries on v$ views. > > > SID SEQ# EVENT > ---------- ---------- > ---------------------------------------------------------------- > P1TEXT P2TEXT > ---------------------------------------------------------------- > ---------------------------------------------------------------- > P3TEXT WAIT_TIME > SECONDS_IN_WAIT STATE > ---------------------------------------------------------------- ---------- > --------------- ------------------- > 1 26767 pmon timer > duration > 0 > 4750 WAITING > > 113 9126 db file sequential read > file# block# > blocks - 1 > 0 WAITED SHORT TIME > > 86 2404 db file scattered read > file# block# > blocks 2 > 0 WAITED KNOWN TIME > > 8 7539 smon timer > sleep time failed > 0 > 232 WAITING > > 70 36 SQL*Net message to client > driver id #bytes > - 1 > 0 WAITED SHORT TIMEhandle address > 3830864540 E4565A9C > lock address 4121106472 > F5A31828 > 10*mode+namespace 31 > 0000001F 0 677 WAITING > > 70 32 SQL*Net message to client > driver id 1650815232 > 62657100 > #bytes 1 > 00000001 > 0 > 00 -1 0 WAITED SHORT TIME > > > Please advise ... > > - Chetan > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Chindarkar, Chetan (CONS FIN , Contractor) > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). >
-- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chindarkar, Chetan (CONS FIN , Contractor) INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
-- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
