RE: Hanging query puzzle

Kirti,

Just took another look at our kproc processes. What is weird is that we have 26 of them, 9 are owned by oracle the rest are root (huh?). All of them started at system boot. Also, almost all of the CPU is used by the top four
(current %CPU and total over the life of the process) with the rest of the
CPU spread over another 12 (this includes all of the oracle kprocs). Looks like too many are being spawned at startup (I'll check the min number), but I didn't know oracle had its own. Looks like one for each dbw process plus one more. Does oracle io only happen through these? If so, why are 4 of the root processes much busier if almost all IO is from the databases?

I'll keep digging and posting when I have time.

Henry

-----Original Message-----
Kirti
Sent: Thursday, February 06, 2003 12:40 PM To: Multiple recipients of list ORACLE-L

Henry,
 I got aiostat from IBM Support when I bugged them about how to monitor AIO. Not sure if aiostat is readily available as a download from their Web site or in/for the newer versions of AIX.
 It works similar to vmstat and iostat, showing number of pending AIO requests at the time of sampling. Following is an example from one of our Servers, showing 10 samples at 1 sec interval. Ideally, the count should be 0 at all times. If it stays high (relative term) consistently then one needs to increase the number of configured AIO servers, or check the I/O subsystem for other problems...
 By default, aiostat must be run as 'root'.

HTH,

• Kirti

ibmRS50 [VS9XBP]# aiostat 1 10

AIO requestcount: 1
AIO requestcount: 0
AIO requestcount: 0
AIO requestcount: 1
AIO requestcount: 0
AIO requestcount: 2
AIO requestcount: 0
AIO requestcount: 3
AIO requestcount: 0
AIO requestcount: 0

-----Original Message-----
Sent: Thursday, February 06, 2003 9:34 AM To: Multiple recipients of list ORACLE-L

Kirti,
I've been searching on the IBM site for aiostat without much luck. What kind of information does it give you? (if I'm going to convince my SA to call IBM about this I'll need a very good argument. I'm still working on getting the rights to use sar).

Henry

-----Original Message-----
Kirti
Sent: Wednesday, February 05, 2003 8:29 PM To: Multiple recipients of list ORACLE-L

Jeff,

If you see the SEQ# field in the v$session_wait view not incrementing, then something else, other than Oracle, is causing a hang up...

On AIX, if you have AIO enabled, try running 'aiostat'. It will show if there are any pending AIO requests. The difficult part would be to relate those pending calls to your session. It is quite likely that a particular AIO server may show waits on I/O, if this hang up is IO related. You can try 'pstat' to find out AIO server processes and associated 'pid' to dig deeper.

If you do not have 'aiostat', ring up IBM Support. It is a nice utility to have, and it is free.

For those on AIX 5L, can you please check if 'aiostat' is available as a standard distribution? I was told by IBM Support that 'aiostat' was going to be available with AIX 5L.

• Kirti

-----Original Message-----
Sent: Wednesday, February 05, 2003 3:20 PM To: Multiple recipients of list ORACLE-L

It looks like your process really is stuck in a way that has nothing to do with the Oracle code directly.

You might look at the CPU usage of your
session and its shadow using an O/S
utility, but I suspect it would show
zero CPU. Perhaps truss (or the AIX
equivalent) might show your process
spinning on whatever call equates to
'has the async read completed yet'.

You could try doing three processstate
dumps with 5 second intervals to see if
the processstate shows any changes
which might give you a hint - but again
I'd GUESS that you'll find nothing happening.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Henry Poras
  INET: hporas_at_etal.uri.edu

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