RE: Hanging query puzzle

  Did you try tracing it from the OS ? Any error messages in the OS system log ? Is it always one datafile/mount point ? A while back, I had seen a similar problem when an array had gone bad. You may want to try using "dd" to read and write some files and check the timing.

Regards,
Denny

Quoting Thomas Jeff <ThomasJe_at_tce.com>:

> Still sitting there, while we try to figure out exactly why it's
> waiting.
>
>
> SID Username EVENT
> WAIT_TIME STATE SECONDS_IN_WAIT
> ----- ----------------
> --------------------------------------------------
> ---------- ------------------- ---------------
> 1 pmon timer
> 0 WAITING 79579
> 5 smon timer
> 0 WAITING 80
> 12 slave wait
> 0 WAITING 199
> 13 slave wait
> 0 WAITING 199
> 14 slave wait
> 0 WAITING 262
> 15 slave wait
> 0 WAITING 199
> 28 NIK db file scattered read
> 0 WAITING 20119
>
> -----Original Message-----
> From: Denny Koovakattu [mailto:groups_at_koovakattu.com]
> Sent: Wednesday, February 05, 2003 2:58 PM
> To: ORACLE-L_at_fatcity.com
> Cc: Thomas Jeff
> Subject: Re: Hanging query puzzle
>
>
>
>
> What is the value for WAIT_TIME ? This may not be an IO problem
> if 'WAIT_TIME' is not 0. A session is waiting only when 'WAIT_TIME' is
> 0. I
> would suggest running some utility like tusc (HP), truss (Sun), strace
> (Linux)
> and check it from the OS side. Since this is a third party tool, it
> could be
>
> performing some CPU only operation which is not reflected in
> v$session_wait.
>
> Regards,
> Denny
>
> Quoting Thomas Jeff <ThomasJe_at_tce.com>:
>
> > We have a query from a 3rd-party tool that seems to either run very
> > quick or
> > crawls to a complete stop.
> >
> > We can find no patterns to this behavior. The hang can be
> > experienced
> > even when there are no other
> > processes active in the database. Checking waits, we see a db
> file
> > scattered read.
> >
> > SID EVENT P1TEXT P1
> P2TEXT
> > P2 P3TEXT P3
> > ----- ---------------------------- ------------------ ----------
> > ------------------ ---------- ------------------ ----------
> > 1 pmon timer duration 300
> > 0 0
> > 12 slave wait msg ptr 5.0440E+17
> > 0 0
> > 13 slave wait msg ptr 5.0440E+17
> > 0 0
> > 14 slave wait msg ptr 5.0440E+17
> > 0 0
> > 15 slave wait msg ptr 5.0440E+17
> > 0 0
> > 28 db file scattered read file# 12
> > block#
> > 21047 blocks 2
> > 5 smon timer sleep time 300
> > failed
> > 0 0
> >
> > Then going to v$sess_io, we see the process is comletely stuck, no
> > activity
> > going on at all, and it's the
> > only active process in the database.
> >
> > SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES
> > CONSISTENT_CHANGES
> > ----- ---------- --------------- -------------- -------------
> > ------------------
> > 28 6233582 60812023 36589516 4076353
> > 115
> >
> > The query looks like this:
> >
> > SELECT PRAssignment.*, SRM_RESOURCES.RESOURCE_TYPE
> > FROM PRAssignment, SRM_RESOURCES
> > WHERE prModTime > TIMESTAMP '2003-02-05 09:23:56.0'
> > AND PRAssignment.prResourceID=SRM_RESOURCES.ID
> >
> > If I check the file/block values for the wait I get the prassignment
> > table.
> > Prassignment has 5K rows
> > while srm_resources has 300 rows. Prassignment also has a LONG RAW
> > column,
> > consequently we
> > see a high chain count, with the result that it's taking up 135
> extents
> > to
> > cover those 5K rows.
> >
> > I'm at a loss to explain why we see such inconsistent results with
> > this
> > query. Thoughts?
> >
> > Thanks.
> >
> > --------------------------------------------
> > Jeffery D Thomas
> > DBA
> > Thomson Information Services
> > Thomson, Inc.
> >
> > Email: jeff.thomas_at_thomson.net
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Denny Koovakattu
  INET: groups_at_koovakattu.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).