Identifying Oracle sessions that are pegging the CPU

[DeepDiverMom]

When I run "top" in *nix, there are about a dozen processes that are running between 80%-100% CPU utilization on an 8-CPU box. They are all processes logged in as Oracle. Customers are starting to complain.

We are not presently running Oracle Enterprise Manager.

What queries can I run (as a DBA, against the instance) that show me which connections to the instance are running "way hot"?

 

[DrD123]

Hi Mom,

You mentioned you don't have OEM, do you have access and permissions to run SQLPLUS?

Also which *nix you running?

When you ran TOP, you saw the PID's for these Oracle processes correct? write them or make note somewhere. You'll see in the following script a column called "Process ID", these are your Unix PID's

If you do, try this script:

SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF

SELECT Substr(a.username,1,15) "Username",
       a.osuser "OS User",
       a.sid "Session ID",
       a.serial# "Serial No",
       d.spid "Process ID",
       a.lockwait "LockWait",
       a.status "Status",
       Trunc(b.value/1024) "PGA (Kb)",
       Trunc(e.value/1024) "UGA (Kb)",
       a.module "Module",
       Substr(a.machine,1,15) "Machine",
       a.program "Program",
       Substr(To_Char(a.logon_Time,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Time"
FROM   v$session a,
       v$sesstat b,
       v$statname c,
       v$process d,
       v$sesstat e,
       v$statname f
WHERE  a.paddr      = d.addr
AND    a.sid        = b.sid
AND    b.statistic# = c.statistic#
AND    c.name       = 'session pga memory'
AND    a.sid        = e.sid
AND    e.statistic# = f.statistic#
AND    f.name       = 'session uga memory'
ORDER BY 1,2;

SET PAGESIZE 14
SET FEEDBACK ON

With high CPU usage also can be disk I/O, and wait I/O when the system is getting soaked.

Try this also during when you see the CPU getting pegged:

iostat -xcn 1 30

You can also get an idea of the disk I/O per table space using this to start looking for any contention:

col name for a50

select d.name,
f.phyrds reads,
f.phywrts wrts,
(f.readtim/ decode(f.phyrds,0,-1,f.phyrds)) readtime,
(f.writetim / decode(f.phywrts,0,-1,phywrts)) writetime
from
v$datafile d,
v$filestat f
where
d.file# = f.file#
order by
d.name;

Post some results if you need to.

Good Luck
DrD

 

[DeepDiverMom]

DrD,

Thanks for your response...I'll try out your suggestions in a few minutes after I put out another brush fire.

In short answer to your earlier questions:

Yes, we have SQL*Plus (in which I'll try your code).

Here are the results of an o/s "uname -a":

Linux <domain> 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux

Thanks again, and I'll get back to you with the results of my running your code.

 

[sem]

Here's my script (BTW called top.sql) I use:

set serverout on size 100000
set feedback off
set verify oFF

declare
  cursor c1 is
   select decode(upper('&&1')
	, 'DISK',DISK_READS
	, 'GETS', BUFFER_GETS
	, 'EXEC', EXECUTIONS
	, 'SHARE', SHARABLE_MEM
	, 'PARSE', PARSE_CALLS
	,  EXECUTIONS) VAL
	,  module,EXECUTIONS, sql_text from v$sqlarea
   order by 1 desc;
  top number :=0;
  text1 varchar2(4000);
  EXECUTIONS NUMBER;
  x number;
  len1 number;
  mod_name varchar2(64);
  line_length number := 80;

begin
  dbms_output.put_line ('&&1');
  dbms_output.put_line ('Value     Text' );
  dbms_output.put_line ('-------   -----------------------------------------------------------');

  open c1;

  for i in 1..&&2 loop
    fetch c1 into top, mod_name,EXECUTIONS,  text1;
    if mod_name is null then mod_name := 'unknown'; end if;
    dbms_output.put_line ('Module :'||mod_name||' executed '||EXECUTIONS);
    dbms_output.put_line (rpad(to_char(top),9)||'  '||substr(text1,1,line_length));
    len1 := length(text1);
    x:=line_length;
    while len1>x-1 loop
        dbms_output.put_line ('|          '||substr(text1,x+1,line_length));
	x := x+line_length;
    end loop;
        dbms_output.put_line ('|');
  end loop;
  close c1;
end;
/

It's called like @top <WHAT> <HOW MANY>, e.g.

SQL> @top DISK 10

returns top ten disk abusers (not connections but rather queries - candidates to be rewritten).

Regards, Dima

http://seminihin.narod.ru