| 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
Here is the information for relevant SID.
SID SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
P1TEXT P1 P1RAWWAIT_TIME SECONDS_IN_WAIT STATE
---------------------------------------------------------------- ---------- --------
P2TEXT P2 P2RAW
---------------------------------------------------------------- ---------- --------
P3TEXT P3 P3RAW
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_IDORACLE_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 servicesReceived on Tue Jul 08 2003 - 21:46:12 CDT
---------------------------------------------------------------------
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).
 |
 |