Re: CPU usage in a multi database environment

From: Norman Dunbar <norman.dunbar_at_environment-agency.gov.uk>
Date: Thu, 23 Nov 2006 08:06:08 +0000
Message-Id: <s5655684.094@environment-agency.gov.uk>

Hi Orlando,

>> I have a server with 6 Oracle databases running. How do I find out
which
>> processes are consuming the CPU?
>> top shows something like this which does not help me:

>> PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
>> 16475 oracle 11 26 2 698M 660M sleep 1:34 8.97% oracle
>> 16477 oracle 11 46 2 697M 659M sleep 1:56 7.96% oracle
<SNIP>

I presume you are asking how to connect the processes using CPU as shown by top into a session on the database ? If so, this is what I tend to do :

... run top and see which Unix process is running the modst CPU. From that I can usually find the PID and the database it is connecting to. (Although I'm on HPUX whihc shows me the oracleSID under the Command column, your's only seems to show ORACLE - interesting.)

... run TOAD (SQLPLUS, shatever), connect to the SID noted in top (in my case) and then run this command :

undefine WHAT_UNIX_PID

set lines 150
set trimspool on

select vs.sid                  SID,
       vp.spid                 THREAD,       
       vs.username             USERNAME,
       vs.sql_hash_value       HASH_VALUE,	
       substr(vs.program,1,25) PROGRAM
from   v$session vs,
       v$process vp

where vp.addr = vs.paddr
and vp.spid = &&WHAT_UNIX_PID
/

That shows me the username etc and from the hash, I can fetch back the sql running, it's explain plan and so forth.

Cheers,
Norman.

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 23 2006 - 02:06:08 CST