Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Stuck Query

RE: Stuck Query

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Mon, 22 Sep 2003 18:24:40 -0800
Message-ID: <F001.005D0BC4.20030922182440@fatcity.com> 





Fyi, seconds_in_wait is not incremented by queries of v$session_wait. It
is incremented by LGWR approximately every three seconds.




Cary Millsap


Hotsos Enterprises, Ltd.


http://www.hotsos.com



Upcoming events:

- Hotsos Clinic 101 in Sydney
- Hotsos Symposium 2004, March 7-10 Dallas
- Visit www.hotsos.com for schedule details...






-----Original Message-----


MacGregor, Ian A.


Sent: Monday, September 22, 2003 6:40 PM
To: Multiple recipients of list ORACLE-L



Good catch.  It was showing  Waited Known Time, and  the seconds_in_wait
kept increasing.  I was fooled by the latter being incremented each time
v$session_event was queried.  I did run a trace on it to track the
waits, but had to kill the session.  I've never gotten dbms_system
session traces to  work.  They never return anything even on newly
rebooted machines.  



Ian MacGregor



-----Original Message-----


Sent: Saturday, September 20, 2003 4:05 AM
To: Multiple recipients of list ORACLE-L




Hi!



How do you verify that your session is still waiting on sequential read?


>From v$session_event? Is the status column saying WAITING there? (if it

says WAITED%, then your CPU is doing something else already, and this
record in session wait just shows the last wait).



Tanel.


----- Original Message ----- 


To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Saturday, September 20, 2003 12:54 AM




> SQL> describe chanarch_nlc.archive_wave_i

>  Name                                      Null?    Type

>  ----------------------------------------- -------- 

> ----------------------





>  PV_ID                                              NUMBER(38)


>  TIMESTAMP                                          DATE


>  NANOSECS                                           NUMBER(9)


>  STAT                                               NUMBER(8)


>  SEVR                                               NUMBER(8)


>  OSTAT                                              NUMBER(16)


>  VALUE




CHANARCH_NLC.INT_VALUES



>

> SQL> describe chanarch_nlc.arch_wave_i

>  Name                                      Null?    Type

>  ----------------------------------------- -------- 

> ----------------------





>  PV_ID                                              NUMBER(38)


>  TIMESTAMP                                          DATE


>  NANOSECS                                           NUMBER(9)


>  STAT                                               NUMBER(8)


>  SEVR                                               NUMBER(8)


>  OSTAT                                              NUMBER(16)


>  VALUE




CHANARCH_NLC.INT_VALUES



>

>

>

>

> SQL> describe chanarch_nlc.int_values

>  chanarch_nlc.int_values VARRAY(16384) OF NUMBER(38)

>

> Select * from chanarch_nlc.arch_wave_i

> Where pv_id = 433 and

> Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' 

> ORDER BY TIMESTAMP, NANOSECONDS /


>

> Returns 1 row plus the associated varray data in one second

>

> Select * from chanarch_nlc.archive_wave_i

> Where pv_id = 433 and

> Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' 

> ORDER BY TIMESTAMP, NANOSECONDS


>

> Returns 0 rows in  about a second

>

> Select * from chanarch_nlc.arch_wave_i

> Where pv_id = 433 and

> Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' 

> Union all Select * from chanarch_nlc.archive_wave_i

> Where pv_id = 433 and

> Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00'

>

> Returns a single row with the varray data in a second

>

> However

>

> Select * from chanarch_nlc.arch_wave_i

> Where pv_id = 433 and

> Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' 

> Union all Select * from chanarch_nlc.archive_wave_i

> Where pv_id = 433 and

> Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00'

> Order by 2,3

>

> Never returns.  It waits forever on a sequential dbfile read event of 

> one



the files used by the lob  segment which contains the varray data for
the value column of chanarch_nlc.arch_wave_i.  Nothing appears to be
blocking the session.  The query plan is as one would expect.


>

> Similar queries  against different  tables with the same structure 

> proceed



without incident.


>

> Ian MacGregor

> Stanford Linear Accelerator Center

> ian_at_SLAC.Stanford.edu

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> -- 

> Author: MacGregor, Ian A.

>   INET: ian_at_SLAC.Stanford.EDU

>

> 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: ListGuru_at_fatcity.com (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: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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: ListGuru_at_fatcity.com (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: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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: ListGuru_at_fatcity.com (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: Cary Millsap
  INET: cary.millsap_at_hotsos.com

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: ListGuru_at_fatcity.com (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 Mon Sep 22 2003 - 21:24:40 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US