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 <[EMAIL PROTECTED]>: > 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] > -- 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).
