LLI Wiki — My Digital Knowledge Base

Oracle : diagnostic de performance (CPU, mémoire, sessions, requêtes longues)

Commandes système et requêtes SQL pour trouver les processus et sessions Oracle gourmands en CPU, mémoire, undo ou temp, analyser les requêtes longues et tuer une session.

Collection de commandes système et de requêtes SQL pour identifier le processus ou la session Oracle qui consomme du CPU, de la mémoire, des E/S, de l'undo ou de l'espace temporaire, inspecter les requêtes longues et terminer une session problématique.

Trouver les plus gros consommateurs de mémoire ou de CPU au niveau système

Mémoire, Linux :

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

Mémoire, 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

Autres outils système utiles :

pidstat -dl 20          # E/S par processus
iostat -T 5 3           # statistiques d'E/S
mpstat -P ALL 2         # CPU par coeur
sar -P ALL 2 3
dstat -c --top-cpu -dn --top-mem
lscpu
perf stat -a -- sleep 10
free -m                 # mémoire : ligne 1 physique, ligne 2 applications, ligne 3 swap
vmstat -w 1             # suivi de la mémoire en temps réel
svmon -P -O summary=basic,unit=MB   # AIX

Voir aussi Measuring storage performance for Oracle systems.

Mémoire et CPU par session

PGA utilisée par 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 consommé par les sessions actives :

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#
/

Du PID système à la session et à sa requête

Quel programme se cache derrière un processus système (remplacer le PID par celui trouvé avec 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;

La même chose avec le texte SQL :

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;

Texte SQL d'un PID système ou d'un SID donné :

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;
-- ou : AND s.sid = &SID

Qui est connecté et que se passe-t-il

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;

Requêtes SQL actives, les plus longues en premier (équivalent d'un 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;

Événements d'attente en cours (événements inactifs exclus) :

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
/

Plan d'exécution d'une requête, une fois son sql_id connu :

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

Verrous et opérations longues

Verrous (une requête peut être lente parce qu'elle est bloquée) :

select
  object_name,
  object_type,
  session_id,
  type,         -- type de verrou système/utilisateur
  lmode,        -- mode dans lequel la session détient le verrou
  request,
  block,
  ctime         -- temps écoulé depuis l'obtention du mode courant
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
/

Opérations longues comme les full table scans (de nombreuses opérations courtes n'apparaissent pas ici) :

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
/

Statistiques SQL en temps réel

Top 20 des requêtes par temps CPU :

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;

Requêtes en cours d'exécution, triées par lectures disque :

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;

Consommateurs d'undo et d'espace temporaire

Sessions utilisant de l'undo (la requête suppose des blocs de 16 Ko) :

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;

Avec la requête SQL associée :

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;

Utilisation de l'espace temporaire :

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;

Tuer une session

Lister les sessions avec leur processus système (compatible RAC) :

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';

Tuer ou déconnecter la session depuis SQL*Plus (la partie @inst_id concerne 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;

Si la session reste au statut KILLED, retrouver son processus système et le tuer au niveau OS :

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>

Avant de tuer au niveau OS, vérifier si la session effectue un rollback. Si USED_UREC continue de diminuer, laisser le rollback se terminer.

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;

Estimer l'empreinte mémoire d'une base

SGA et 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;

Détail de la PGA par processus (un programme sans nom de processus d'arrière-plan, comme PMON ou SMON, est un processus de session) :

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

Pour estimer la réserve nécessaire aux sessions pas encore connectées, prendre la plus grande PGA d'une session utilisateur, le paramètre processes et le nombre de sessions connectées :

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;

Estimer l'empreinte de stockage (Unix/Linux)

Générer une commande df par point de montage utilisé par les fichiers de données, temporaires, redo et de contrôle, puis exécuter le résultat dans un shell sur le serveur de base de données :

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;

Vérifier si les paramètres de destination de fichiers pointent vers d'autres points de montage :

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;

Et les objets directory :

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

Une alternative plus concise consiste à filtrer df sur les points de montage concernés :

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

Générer de la charge de test

Pour reproduire un problème ou tester la supervision, ces instructions génèrent de la charge. À utiliser uniquement sur un système de test.

Charge E/S : un produit cartésien entre deux grosses tables.

select count(*) from t1, t2;

Charge CPU :

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

Charge CPU en 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;
/

Informations à rassembler pour le support Oracle

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

Remarques

Mastodon LLIMastodon SocialFosstodon