Sess user stats

Fiche express
Type	Script SQL*Plus de diagnostic (Sessions)
Voir aussi	Sess users · Sess user sessions

Sess user stats liste les sessions actuellement connectées, triées par volume d'entrées/sorties logiques décroissant, avec pour chacune : CPU consommé, I/O logique et physique (lectures/écritures), mémoire PGA, nombre de curseurs ouverts et de transactions validées.

Script

SET TERMOUT OFF
COLUMN current_instance NEW_VALUE current_instance NOPRINT
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON

PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report   : User Sessions and Statistics Ordered by Logical I/O         |
PROMPT | Instance : &current_instance                                           |
PROMPT +------------------------------------------------------------------------+

SET LINESIZE 180
SET PAGESIZE 50000
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN sid                FORMAT 999999           HEADING 'SID'
COLUMN session_status     FORMAT a9                HEADING 'Status'
COLUMN oracle_username    FORMAT a18                HEADING 'Oracle User'
COLUMN session_program    FORMAT a40                HEADING 'Session Program' TRUNC
COLUMN cpu_value          FORMAT 999,999,999       HEADING 'CPU'
COLUMN logical_io         FORMAT 999,999,999,999   HEADING 'Logical I/O'
COLUMN physical_reads     FORMAT 999,999,999,999   HEADING 'Physical Reads'
COLUMN physical_writes    FORMAT 999,999,999,999   HEADING 'Physical Writes'
COLUMN session_pga_memory FORMAT 9,999,999,999     HEADING 'PGA Memory'
COLUMN open_cursors       FORMAT 99,999            HEADING 'Cursors'
COLUMN num_transactions   FORMAT 999,999           HEADING 'Txns'

-- Chaque alias sstatN/statnameN cible une statistique différente de v$sesstat/v$statname
-- pour la même session (approche historique par auto-jointures répétées).
SELECT
    s.sid       sid
  , s.status    session_status
  , s.username  oracle_username
  , s.program   session_program
  , sstat1.value                cpu_value
  , sstat2.value + sstat3.value logical_io
  , sstat4.value                physical_reads
  , sstat5.value                physical_writes
  , sstat6.value                session_pga_memory
  , sstat7.value                open_cursors
  , sstat8.value                num_transactions
FROM
    v$process  p
  , v$session  s
  , v$sesstat  sstat1
  , v$sesstat  sstat2
  , v$sesstat  sstat3
  , v$sesstat  sstat4
  , v$sesstat  sstat5
  , v$sesstat  sstat6
  , v$sesstat  sstat7
  , v$sesstat  sstat8
  , v$statname statname1
  , v$statname statname2
  , v$statname statname3
  , v$statname statname4
  , v$statname statname5
  , v$statname statname6
  , v$statname statname7
  , v$statname statname8
WHERE
      p.addr(+)           = s.paddr
  AND s.sid                = sstat1.sid
  AND s.sid                = sstat2.sid
  AND s.sid                = sstat3.sid
  AND s.sid                = sstat4.sid
  AND s.sid                = sstat5.sid
  AND s.sid                = sstat6.sid
  AND s.sid                = sstat7.sid
  AND s.sid                = sstat8.sid
  AND statname1.statistic# = sstat1.statistic#
  AND statname2.statistic# = sstat2.statistic#
  AND statname3.statistic# = sstat3.statistic#
  AND statname4.statistic# = sstat4.statistic#
  AND statname5.statistic# = sstat5.statistic#
  AND statname6.statistic# = sstat6.statistic#
  AND statname7.statistic# = sstat7.statistic#
  AND statname8.statistic# = sstat8.statistic#
  AND statname1.name = 'CPU used by this session'
  AND statname2.name = 'db block gets'
  AND statname3.name = 'consistent gets'
  AND statname4.name = 'physical reads'
  AND statname5.name = 'physical writes'
  AND statname6.name = 'session pga memory'
  AND statname7.name = 'opened cursors current'
  AND statname8.name = 'user commits'
ORDER BY logical_io DESC
/

Limite de l'approche : la jointure multiplie huit copies de v$sesstat (une par statistique recherchée) — correct fonctionnellement mais coûteux sur une instance très chargée. Une alternative plus légère consiste à agréger une seule fois v$sesstat filtré sur les huit statistic# voulus, puis à pivoter le résultat (PIVOT ou MAX(DECODE(...))) plutôt que d'enchaîner des jointures.

Voir aussi

• Sess users — inventaire nominatif des sessions, sans les statistiques
• Sess user sessions — synthèse du nombre de connexions par utilisateur