| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Shared Pool Wait while using SQL Loader
Oh, it's a library cache lock. Here is the script, contributed about 2 days
ago by Andy Rivenes:
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 P1P1RAW
P2TEXT P2P2RAW
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
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_IDSESSION_ID ORACLE_USERNAME
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). >
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: [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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: [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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: [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). Received on Tue Jul 08 2003 - 23:15:58 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |