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).
