Thanks for the script Mladen.
I think I figured out what is the problem. There was a long running query which was
creating a materialized view which had that table in the where clause. So the update
and the SQL Loader jobs were hanging.
- Chetan
-----Original Message-----
Sent: Wednesday, July 09, 2003 1:09 AM
To: Multiple recipients of list ORACLE-L
Oh, it's a library cache lock. Here is the script, contributed about 2 days
ago by Andy Rivenes:
-- 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).
--
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).
