LLI Wiki — My Digital Knowledge Base

Oracle: performance troubleshooting (CPU, memory, sessions, long queries)

OS commands and SQL queries to find Oracle processes and sessions consuming CPU, memory, undo or temp, analyse long-running queries and kill a session.

A collection of OS commands and SQL queries to find which Oracle process or session consumes CPU, memory, I/O, undo or temp space, to inspect long-running SQL, and to terminate a problematic session.

Find the top memory or CPU consumers at OS level

Memory, Linux:

ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head

Memory, AIX:

ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -r -n -k 1,1 | head

CPU, Linux:

ps -e -o pcpu,pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head

CPU, AIX:

ps -e -o pcpu,pmem,pid,user,tty,args | grep -i oracle | sort -r -n -k 1,1 | head

Other useful system tools:

pidstat -dl 20          # I/O per process
iostat -T 5 3           # I/O statistics
mpstat -P ALL 2         # CPU per core
sar -P ALL 2 3
dstat -c --top-cpu -dn --top-mem
lscpu
perf stat -a -- sleep 10
free -m                 # memory: line 1 physical, line 2 application, line 3 swap
vmstat -w 1             # follow memory in real time
svmon -P -O summary=basic,unit=MB   # AIX

See also Measuring storage performance for Oracle systems.

Memory and CPU per session

PGA used per session:

SET PAGESIZE 60
SET LINESIZE 300

SELECT a.username, a.osuser, a.program, b.spid, b.pga_used_mem/1024/1024 "PGA USED (MB)",
       a.sid, a.serial#, a.module, a.logon_time, a.terminal
FROM v$session a, v$process b
WHERE a.paddr = b.addr
ORDER BY b.pga_used_mem DESC;

CPU used by active sessions:

SET PAGESIZE 60
SET LINESIZE 300

COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)" FORMAT 999,999,999.0000

SELECT s.username, t.sid, s.serial#, SUM(VALUE/100) AS "cpu usage (seconds)"
FROM v$session s, v$sesstat t, v$statname n
WHERE t.STATISTIC# = n.STATISTIC#
AND NAME LIKE '%CPU used by this session%'
AND t.SID = s.SID
AND s.status = 'ACTIVE'
AND s.username IS NOT NULL
GROUP BY username, t.sid, s.serial#
/

From an OS process id to the session and its SQL

Which program is behind an OS process (replace the pid with the one found with ps):

select
'USERNAME : ' || s.username || chr(10) ||
'OSUSER : ' || s.osuser || chr(10) ||
'PROGRAM : ' || s.program || chr(10) ||
'SPID : ' || p.spid || chr(10) ||
'SID : ' || s.sid || chr(10) ||
'SERIAL# : ' || s.serial# || chr(10) ||
'MACHINE : ' || s.machine || chr(10) ||
'TERMINAL : ' || s.terminal
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = &PID_FROM_OS;

The same with the SQL text:

select
'USERNAME : ' || s.username || chr(10) ||
'OSUSER : ' || s.osuser || chr(10) ||
'PROGRAM : ' || s.program || chr(10) ||
'SPID : ' || p.spid || chr(10) ||
'SID : ' || s.sid || chr(10) ||
'SERIAL# : ' || s.serial# || chr(10) ||
'MACHINE : ' || s.machine || chr(10) ||
'TERMINAL : ' || s.terminal || chr(10) ||
'SQL TEXT : ' || sa.sql_text
from v$process p, v$session s, v$sqlarea sa
where p.addr = s.paddr
and s.username is not null
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
and p.spid = &PID_FROM_OS;

SQL text of a given OS process or session id:

SELECT s.sid, s.serial#, s.sql_id, q.sql_text
FROM v$process p, v$session s, v$sql q
WHERE p.addr = s.paddr
AND s.sql_id = q.sql_id
AND p.spid = &PID_FROM_OS;
-- or: AND s.sid = &SID

Who is connected and what is running

set linesize 180
set pagesize 20
col "Logon Time" format a11
col "UNIX Proc" format a9
col username format a15
col osuser format a13
col sid format 9999
col serial# format 9999999

select s.username, osuser, status,
to_char(logon_time,'fmHH:MI:SS AM') as "Logon Time",
sid, s.serial#, p.spid as "UNIX Proc"
from v$session s, v$process p
where s.paddr = p.addr
and s.username is not null
order by status, s.username, logon_time;

Active SQL, longest running first (a kind of sp_who):

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,
       serial#,
       MACHINE,
       SES.sql_id, SES.sql_exec_start, SES.prev_exec_start,
       ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT,
       SQL.SQL_TEXT STMT
  FROM V$SESSION SES,
       V$SQL SQL
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS = SQL.ADDRESS
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
 order by runt desc, 1;

Current wait events (idle events excluded):

select a.sid, substr(b.username,1,10) username, substr(b.osuser,1,10) osuser,
substr(b.program||b.module,1,15) program, substr(b.machine,1,22) machine,
a.event, a.p1, b.sql_hash_value
from v$session_wait a, v$session b
where b.sid = a.sid
and a.event not in ('SQL*Net message from client','SQL*Net message to client',
'smon timer','pmon timer')
and username is not null
order by 6
/

Execution plan of a statement, once its sql_id is known:

SELECT *
FROM v$sql_plan
WHERE sql_id = '<sql_id>';

Locks and long operations

Locks (a query can be slow because it is blocked):

select
  object_name,
  object_type,
  session_id,
  type,         -- type of system/user lock
  lmode,        -- lock mode in which the session holds the lock
  request,
  block,
  ctime         -- time since the current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

Long operations such as full table scans (many short operations will not show up here):

COLUMN percent FORMAT 999.99

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message, (sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1
/

Real-time SQL statistics

Top 20 statements by CPU time:

select * from (
select a.sid session_id, a.sql_id
,a.status
,a.cpu_time/1000000 cpu_sec
,a.buffer_gets, a.disk_reads
,b.sql_text sql_text
from v$sql_monitor a
,v$sql b
where a.sql_id = b.sql_id
order by a.cpu_time desc)
where rownum <= 20;

Statements currently executing, ordered by disk reads:

select * from (
select a.sid session_id, a.sql_id, a.status
,a.cpu_time/1000000 cpu_sec
,a.buffer_gets, a.disk_reads
,substr(b.sql_text,1,35) sql_text
from v$sql_monitor a
,v$sql b
where a.sql_id = b.sql_id
and a.status = 'EXECUTING'
order by a.disk_reads desc)
where rownum <= 20;

Undo and temporary space consumers

Sessions using undo (the query assumes a 16 KB block size):

select s.sid, s.serial#, s.osuser, s.logon_time
,s.status, s.machine
,t.used_ublk, t.used_ublk*16384/1024/1024 undo_usage_mb
from v$session s
,v$transaction t
where t.addr = s.taddr;

With the associated SQL:

select s.sid, s.serial#, s.osuser, s.logon_time, s.status
,s.machine, t.used_ublk
,t.used_ublk*16384/1024/1024 undo_usage_mb
,q.sql_text
from v$session s
,v$transaction t
,v$sql q
where t.addr = s.taddr
and s.sql_id = q.sql_id;

Temporary space usage:

SELECT s.sid, s.serial#, s.username
,p.spid, s.module, p.program
,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used
,su.tablespace
FROM v$sort_usage su, v$session s
,dba_tablespaces tbsp
,v$process p
WHERE su.session_addr = s.saddr
AND su.tablespace = tbsp.tablespace_name
AND s.paddr = p.addr
GROUP BY
s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,
p.program, tbsp.block_size, su.tablespace
ORDER BY s.sid;

Kill a session

List sessions with their OS process (RAC aware):

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

Kill or disconnect the session from SQL*Plus (the @inst_id part is for RAC):

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

If the session stays in the KILLED status, find its OS process and kill it at OS level:

SELECT spid, s.sid, s.serial#, osuser, s.status, s.username, s.program
FROM v$process p, v$session s
WHERE p.addr = s.paddr AND s.sid = &SID;
kill -9 <SPID>

Before killing at OS level, check whether the session is rolling back. If USED_UREC keeps decreasing, let the rollback finish.

SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Estimate the memory footprint of a database

SGA and PGA:

SELECT sum(value)/1024/1024 "TOTAL SGA (MB)" FROM v$sga;
select sum(pga_max_mem)/1024/1024 "TOTAL MAX PGA (MB)" from v$process;

PGA detail per process (a program with no background process name, such as PMON or SMON, is a session process):

SELECT spid, program,
       pga_max_mem      max,
       pga_alloc_mem    alloc,
       pga_used_mem     used,
       pga_freeable_mem free
FROM V$PROCESS;

To estimate the reserve needed for sessions not yet connected, take the largest PGA used by a user session, the processes parameter and the number of connected sessions:

select max(p.pga_max_mem)/1024/1024 "PGA MAX MEM OF USER SESS(MB)"
from v$process p, v$session s
where p.addr = s.paddr and s.username is not null;

select name, value from v$parameter
where name = 'processes';

select count(1) from v$session
where username is not null;

Estimate the storage footprint (Unix/Linux)

Generate one df command per mount point used by data, temp, redo and control files, then run the result in a shell on the database server:

select unique 'df -k '||a.MTPOINT MOUNT_POINT
from ( select substr(FILE_NAME,0,instr(FILE_NAME,'/',1,2)-1) MTPOINT
from dba_data_files
union
select substr(FILE_NAME,0,instr(FILE_NAME,'/',1,2)-1) MTPOINT
from dba_temp_files
union
select substr(MEMBER,0,instr(MEMBER,'/',1,2)-1) MTPOINT
from v$logfile
union
select substr(NAME,0,instr(NAME,'/',1,2)-1) MTPOINT
from v$controlfile
) a;

Check whether the file destination parameters are on other mount points:

select name, value
from v$parameter
where (regexp_like(name, '^log_archive_(dest|dest_([1-9]))$', 'i') or name like '%dump_dest' or name like '%file_dest' or name like 'diag%dest' or name ='utl_file_dir') and value is not null;

And the directory objects:

select 'df -k '||substr(DIRECTORY_PATH,0,instr(DIRECTORY_PATH,'/',1,2)-1) MTPOINT from dba_directories;

A more concise alternative is to filter df on the relevant mount points:

df -k | grep -E '/u01|/u02|/utldir|/opt'

Generating test load

To reproduce a problem or test monitoring, these statements generate load. Use them on a test system only.

I/O load: a Cartesian join between two large tables.

select count(*) from t1, t2;

CPU load:

select count(dummy)
from (
select dummy
from dual
connect by nocycle 1=1
order by 1
)
/

CPU load with PL/SQL:

DECLARE
   l_result NUMBER;
BEGIN
   FOR id IN 1..999999999
   LOOP
       l_result := (id/DBMS_RANDOM.VALUE + id*DBMS_RANDOM.VALUE)/DBMS_RANDOM.VALUE;
   END LOOP;
END;
/

Information to gather for Oracle Support

cd $ORACLE_HOME/OPatch
./opatch lsinventory -detail -oh $ORACLE_HOME > /tmp/hostname_lsinventory.txt

Notes

Mastodon LLIMastodon SocialFosstodon