Listing Top Sessions by CPU in SQL

In the past I’ve considered writing a SQL version of the Unix TOP utility to view busy database sessions. I never got around to it but a recent post by Christian Antognini gave me the inspiration to finally write my script and a few ideas on how best to go about it. Please note my script is purely reporting sessions as a percentage of an instance’s CPU workload – not of a host server’s potential CPU.

The script is below for anyone who may want to give it a try. I’ve included a user to own the code in order to isolate the privileges and make tidying up easy. There are also 2 types to support the PL/SQL code (idea “borrowed” from the post by C.Antognini referenced previously).

CREATE USER dbtop IDENTIFIED BY dbtop ACCOUNT LOCK;
GRANT SELECT ON v_$sesstat TO dbtop;
GRANT SELECT ON v_$sysstat TO dbtop;
GRANT SELECT ON v_$process TO dbtop;
GRANT SELECT ON v_$session TO dbtop;
GRANT EXECUTE ON dbms_lock TO dbtop;

CREATE OR REPLACE TYPE dbtop.top_rec IS OBJECT (
	sid 		NUMBER
,	pid 		VARCHAR2(12)
,	username	VARCHAR2(30)
,	cpu		NUMBER
,	command		VARCHAR2(25)
);
/

CREATE OR REPLACE TYPE dbtop.top_tab AS TABLE OF dbtop.top_rec;
/

CREATE OR REPLACE FUNCTION dbtop.top(	I_loops IN NUMBER DEFAULT 1
				,	I_interval IN NUMBER DEFAULT 5
				,	I_sessions IN NUMBER DEFAULT 10)
	RETURN top_tab PIPELINED
IS
	L_top_tab1	top_tab;
	L_top_tab2	top_tab;

	L_sys_cpu1	NUMBER;
	L_sys_cpu2	NUMBER;
	L_sys_cpu_delta	NUMBER;

	LC_cpu#	CONSTANT NUMBER := 12;

	CURSOR C_top IS
		SELECT 	top_rec(u.sid,p.spid,u.username,s.value
		, 	SUBSTR(u.program,1,25))
		FROM	v$sesstat s
		, 	v$session u
		, 	v$process p
		WHERE	s.statistic# IN (LC_cpu#)
		AND	s.value > 0
		AND	u.sid = s.sid
		AND	p.addr = u.paddr;

	PROCEDURE record_data(O_sys_cpu OUT NUMBER,O_top_tab OUT top_tab)
	IS
	BEGIN
		SELECT	value
		INTO	O_sys_cpu
		FROM	v$sysstat
		WHERE	statistic# = LC_cpu#;

		OPEN C_top;
		FETCH C_top BULK COLLECT INTO O_top_tab;
		CLOSE C_top;
	END record_data;
BEGIN
	-- record first base line of sessions
	record_data(L_sys_cpu1,L_top_tab1);

	FOR L_count IN 1..I_loops LOOP
		dbms_lock.sleep(I_interval);

		record_data(L_sys_cpu2,L_top_tab2);

		L_sys_cpu_delta := L_sys_cpu2 - L_sys_cpu1;

		-- report delta of snapshots. There's a DECODE on sys_cpu 
		-- as my test system has a habit of having 0 cpu!
		FOR R_top IN (
			SELECT	sid,pid,username,cpu_perc,command
			FROM	(
				SELECT	t2.sid,t2.pid,t2.username
				,	DECODE(L_sys_cpu_delta,0,0,ROUND(((t2.cpu - t1.cpu)
						/ L_sys_cpu_delta)*100,2)) cpu_perc
				,	t2.command
				FROM	TABLE(L_top_tab1) t1
				,	TABLE(L_top_tab2) t2
				WHERE	t1.sid (+) = t2.sid
				ORDER BY cpu_perc DESC
			)
			WHERE ROWNUM <= I_sessions
		) LOOP
			PIPE ROW(top_rec(R_top.sid
				,	R_top.pid
				,	R_top.username
				,	R_top.cpu_perc
				,	R_top.command));
		END LOOP;

		PIPE ROW(top_rec(NULL,NULL,NULL,NULL,NULL));

		L_top_tab1 := L_top_tab2;
		L_sys_cpu1 := L_sys_cpu2;
	END LOOP;
END top;
/

Below is an example of how to run the code (note I’ve again borrowed from Mr Antognini’s post with my ARRAYSIZE setting).

SET ARRAYSIZE 1
COLUMN username FORMAT a15
SELECT sid, pid, username, cpu, command FROM table(dbtop.top(2));

       SID PID          USERNAME               CPU COMMAND
---------- ------------ --------------- ---------- -------------------------
       159 28484        SYS                  98.36 sqlplus@localhost.localdo
       142 28487        OPS$NEIL              1.64 sqlplus@localhost.localdo
       147 3968                                  0 oracle@localhost.localdom
       161 3937                                  0 oracle@localhost.localdom
       163 3933                                  0 oracle@localhost.localdom
       165 3929                                  0 oracle@localhost.localdom
       164 3931                                  0 oracle@localhost.localdom
       162 3935                                  0 oracle@localhost.localdom
       160 3939                                  0 oracle@localhost.localdom
       153 3953                                  0 oracle@localhost.localdom

       159 28484        SYS                  98.21 sqlplus@localhost.localdo
       142 28487        OPS$NEIL              1.79 sqlplus@localhost.localdo
       147 3968                                  0 oracle@localhost.localdom
       161 3937                                  0 oracle@localhost.localdom
       163 3933                                  0 oracle@localhost.localdom
       165 3929                                  0 oracle@localhost.localdom
       164 3931                                  0 oracle@localhost.localdom
       162 3935                                  0 oracle@localhost.localdom
       160 3939                                  0 oracle@localhost.localdom
       153 3953                                  0 oracle@localhost.localdom

The SYS session above is a count of records in DBA_EXTENTS by the way. Session 149 is TOP itself.