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