Perf top sql by buffer gets

Fiche express
Type	Script SQL*Plus de diagnostic
Vue source	v$sqlarea
Voir aussi	Perf top sql by disk reads · Profil de performance

Perf top sql by buffer gets liste les instructions SQL en mémoire (v$sqlarea) triées par nombre de buffer gets (lectures logiques), un des indicateurs les plus parlants pour repérer une requête coûteuse en CPU/mémoire — à l'inverse des lectures physiques (disque), qui dépendent davantage de l'état du cache.

Script

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY   OFF

COLUMN username       FORMAT a18                  HEADING 'Username'
COLUMN buffer_gets    FORMAT 999,999,999,999,999  HEADING 'Buffer Gets'
COLUMN executions     FORMAT 999,999,999,999,999  HEADING 'Executions'
COLUMN gets_per_exec  FORMAT 999,999,999,999,999  HEADING 'Gets / Executions'
COLUMN sql                                        HEADING 'SQL Statement'

BREAK ON report
COMPUTE sum OF buffer_gets   ON report
COMPUTE sum OF executions    ON report
COMPUTE sum OF gets_per_exec ON report

PROMPT
PROMPT =============================================
PROMPT SQL avec plus de 1000 buffer gets
PROMPT =============================================

SELECT
    UPPER(b.username)                                        username
  , a.buffer_gets                                             buffer_gets
  , a.executions                                              executions
  , a.buffer_gets / DECODE(a.executions, 0, 1, a.executions)  gets_per_exec
  , sql_text || CHR(10) || CHR(10)                            sql
FROM
    sys.v_$sqlarea a
  , dba_users      b
WHERE
      a.parsing_user_id = b.user_id
  AND a.buffer_gets > 1000
  AND b.username NOT IN ('SYS', 'SYSTEM')
ORDER BY
    buffer_gets DESC;

Le seuil buffer_gets > 1000 et l'exclusion des comptes SYS/SYSTEM sont arbitraires : à ajuster selon le volume d'activité de la base pour ne garder qu'un nombre raisonnable de lignes. La jointure passe par sys.v_$sqlarea (le nom de la vue sous-jacente réelle, préfixée par le schéma) plutôt que par le synonyme public v$sqlarea — une habitude utile lorsqu'un compte non-DBA n'a accès qu'au synonyme et pas directement à la vue.

Voir aussi

• Perf top sql by disk reads — même principe, trié par lectures physiques (disque) plutôt que logiques
• Profil de performance — profils SQL et diagnostics de performance plus larges
• Requete plan execution — comprendre le plan d'exécution d'une requête identifiée comme coûteuse