How many times a co-worker comes to you and tells you that the database is running slowly?
Of course there are many reasons that can cause the database to slug, for example:

1. High concurrency of active sessions
2. Queries using a lot of disk time such as full table scans
3. Long duration locks on rows
4. Hardware failure such as disks defect

Another reason could be because there is a very CPU intensive query that is effecting the performance of the entire database.

This post will demonstrate how to determine which process is taking the most CPU in the system and how to retrieve the SQL statement it is currently running in the database.

** This post applies to Unix/Linux and Oracle 10g, 11g

In order to reach are goal we have to do the following things:

1. Determine which process is consuming the most CPU in the system – we can use the TOP Linux/Unix command.
2. Finding out the query this process is running – we can prepare a script in advance to help us.

Let’s begin with the preparations – preparing the script that will display the actual activity of the session within the database. The script will be a shell script that incorporates an SQL script within (you can find out more on this topic here oracle-running-sqlplus-from-unix-bash-scripts/). The script receives an argument which is the Linux/Unix process ID of the process we want to examine. I usually put such scripts in a folder that is part of my $PATH such as ~/bin (or /home/oracle/bin).

Create a run-able script in Linux:

touch /home/oracle/bin/oraproc
chmod +x /home/oracle/bin/oraproc
vi /home/oracle/bin/oraproc

In this file write the a copy of the following snippet

#!/bin/bash
if [ $# -ne 1 ];then
	echo "Usage: oraproc <Unix Process ID>"
	exit
fi

unix_pid=$1

sqlplus -s -l / as sysdba <<EOF
set head off feed off time off timing off
set serveroutput on lines 10000
DECLARE
	stmt VARCHAR2(32000) := '';
	report BOOLEAN := TRUE;
	SPACECHAR CONSTANT CHAR(1) := ' ';
	TABLEN CONSTANT NUMBER(2) := 10;
BEGIN
	dbms_output.enable(99999);
	FOR rec IN (
		SELECT  s.sid, s.username, s.osuser, s.machine,
				nvl(s.module, s.program) prog, s.event, t.sql_text
		FROM    v\$process p
		JOIN    v\$session s ON s.paddr = p.addr
		LEFT JOIN v\$sqltext_with_newlines t
				ON	t.address = s.sql_address
				AND	t.hash_value = s.sql_hash_value
		WHERE   p.spid = ${unix_pid}
		ORDER BY t.piece ) LOOP

		stmt := stmt || rec.SQL_TEXT;
		IF report THEN
			DBMS_OUTPUT.PUT_LINE(RPAD('SID:', TABLEN, spacechar) || rec.sid);
			DBMS_OUTPUT.PUT_LINE(RPAD('USERNAME:', TABLEN, spacechar) || rec.username);
			DBMS_OUTPUT.PUT_LINE(RPAD('OSUSER:', TABLEN, spacechar) || rec.osuser);
			DBMS_OUTPUT.PUT_LINE(RPAD('MACHINE:', TABLEN, spacechar) || rec.machine);
			DBMS_OUTPUT.PUT_LINE(RPAD('PROGRAM:', TABLEN, spacechar) || rec.prog);
			DBMS_OUTPUT.PUT_LINE(RPAD('EVENT:', TABLEN, spacechar) || rec.event);
			DBMS_OUTPUT.PUT_LINE('---------------------------------------');
			report := FALSE;
		END IF;
	END LOOP;
	IF NOT report THEN
		IF LENGTH(stmt) > 0 THEN
			DBMS_OUTPUT.PUT_LINE(stmt);
		ELSE
			DBMS_OUTPUT.PUT_LINE('No current running statement');
		END IF;
	ELSE
		DBMS_OUTPUT.PUT_LINE('Unix PID ${unix_pid} not found');
	END IF;
END;
/
EXIT
EOF

Now let’s see this script at work. I have written a PL/SQL anonymous block that uses a lot of CPU. It looks like this:

declare
	start_date date;
	end_time date;
	dum number;
begin
	start_date := sysdate;
	end_time := sysdate + 1/24/60; -- minute
	while end_time > sysdate loop
		dum := 0;
		for i in 1..10000 loop
			dum := dum + 1;
		end loop;
	end loop;
	dbms_output.put_line (start_date || ' ' || end_time);
end;
/

This script runs for a minute in a loop so it uses CPU very intensively. I run it from one session and run the TOP command from another session like this:

Notice that we see the process ID 9530 takes 98% CPU time.

We use this PID as an argument to our script by calling it from shell like this:

$ oraproc 9530

This will display the actual statement this process is currently running in the database (with some additional info I like to add). It looks like this:

$ oraproc 9503
SID:      1
USERNAME: DEMO
OSUSER:   oracle
MACHINE:  ora2
PROGRAM:  SQL Developer
EVENT:    SQL*Net message from client
---------------------------------------
declare
	start_date date;
	end_time date;
	dum number;
begin
	start_date := sysdate;
	end_time := sysdate + 1/24/60; -- minute
	while end_time > sysdate loop
		dum := 0;
		for i in 1..10000 loop
			dum := dum + 1;
		end loop;
	end loop;
	dbms_output.put_line (start_date || ' ' || end_time);
end;

Good luck.