RE: Hanging query puzzle

[Thomas Jeff]

Sorry,  running 8.1.7.2 on AIX 4.3.3.   

 

 

-----Original Message-----
From: John Shaw [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 05, 2003 2:09 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Hanging query puzzle

What version are you running - I have a similar problem on 9.2.0.2 on Solaris 9 that I've had an open tar on since November - Support has finally called up and said other people are having the same kind of problem - especially in regards to parallel processes. I am supposedly getting  a test scenario from them to check out. I'll post the results if and when I get any.

>>> [EMAIL PROTECTED] 02/05/03 11:59AM >>>

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]

Indy DBA Master Documentation available at:
http://gkmqp.tce.com/tis_dba
Select 'Indy DBA' then 'DBA Web Pages'
--------------------------------------------