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