RE: Shared Pool Wait while using SQL Loader

From: Chindarkar, Chetan (CONS FIN , Contractor) <Chetan.Chindarkar_at_gecapital.com>
Date: Tue, 08 Jul 2003 21:35:56 -0700
Message-ID: <F001.005C3EE7.20030708212924@fatcity.com>

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