Re: Stuck Query

Hi!

You souldn't look at seconds_in_wait only, because it's contents depend on wait_time/status as well. If session is currently waiting (wait_time=0), then seconds_in_wait show how long the session has been waiting for the current wait, but if wait_time is not 0, then seconds_in_wait shows seconds since the start of last wait occurrence.

So, dbms_system.set_ev didn't work? Or did you use set_sql_trace_in_session? Which session did you have to kill, the one you were trying to set event from? Try oradebug session_event, maybe this works then?

Also, in my experience, the traces work only for newly executed commands, thus if you set trace in the middle of a long running query, you won't get any results. Anyway, in your case, I'd dump relevant datablocks to see whether your varray is ok itself (whether Oracle is able to dump it at all).

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, September 23, 2003 2:39 AM

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