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:[EMAIL PROTECTED]]
Sent: Wednesday, February 05, 2003 2:58 PM
To: [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>:
> 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: [EMAIL PROTECTED]
