Perf hit ratio by session

Fiche express
Type	Script SQL*Plus de diagnostic
Domaine	Performance
Voir aussi	Perf hit ratio system

Perf hit ratio by session calcule le buffer cache hit ratio (taux de succès du cache de blocs) session par session, à partir de v$session, v$sess_io et v$process. Utile pour identifier une session précise responsable d'un volume anormal de lectures physiques, par opposition au ratio global de l'instance calculé par Perf hit ratio system.

Script

SET LINESIZE 180
SET PAGESIZE 9999

COLUMN unix_id          FORMAT a10                  HEAD Username
COLUMN oracle_id        FORMAT a10                  HEAD OracleID
COLUMN os_user          FORMAT a20                  HEAD OS_User
COLUMN sid              FORMAT 99999                HEAD SID
COLUMN serial_id        FORMAT 999999               HEAD Serial#
COLUMN unix_pid         FORMAT a9                   HEAD UNIX_Pid
COLUMN consistent_gets  FORMAT 999,999,999,999,999  HEAD Cons_Gets
COLUMN block_gets       FORMAT 999,999,999,999,999  HEAD Block_Gets
COLUMN physical_reads   FORMAT 999,999,999,999,999  HEAD Phys_Reads
COLUMN hit_ratio        FORMAT 999.00               HEAD Hit_Ratio

SELECT
    p.username            unix_id
  , s.username            oracle_id
  , s.osuser              os_user
  , s.sid                 sid
  , s.serial#             serial_id
  , LPAD(p.spid, 7)       unix_pid
  , sio.consistent_gets   consistent_gets
  , sio.block_gets        block_gets
  , sio.physical_reads    physical_reads
  , ROUND((sio.consistent_gets + sio.block_gets - sio.physical_reads) /
          (sio.consistent_gets + sio.block_gets) * 100, 2)  hit_ratio
FROM
    v$process p
  , v$session s
  , v$sess_io sio
WHERE
      p.addr (+) = s.paddr
  AND s.sid      = sio.sid
  AND (sio.consistent_gets + sio.block_gets) > 0
  AND s.username IS NOT NULL
ORDER BY hit_ratio
/

La clause (sio.consistent_gets + sio.block_gets) > 0 évite la division par zéro pour les sessions inactives ; les colonnes de la formule ont été explicitement préfixées par l'alias sio. pour lever toute ambiguïté avec les alias du SELECT.

Interprétation

• Un ratio bas pour une session isolée (typiquement inférieur à 90 %) alors que le ratio global de l'instance reste correct pointe vers une requête ou un traitement précis générant des lectures physiques excessives (full table scan, index inadapté, mauvais plan d'exécution) plutôt qu'un sous-dimensionnement global du buffer cache.
• Croiser le SID identifié avec v$sql ou v$session_longops pour retrouver la requête en cause.
• Sur une base multi-applicative, ce script permet de disculper — ou d'incriminer — une application précise avant de toucher au dimensionnement du SGA.

Voir aussi

• Perf hit ratio system — la même mesure calculée au niveau de l'instance entière plutôt que par session.
• Perf performance snapshot — rapport de synthèse reprenant le hit ratio du buffer cache parmi d'autres indicateurs.