Session plus consommatrices

Fiche express
Type	Script SQL*Plus de diagnostic
Voir aussi	Sess users by CPU · Sql

Session plus consommatrices affiche, via un bloc PL/SQL utilisant DBMS_OUTPUT, les N requêtes SQL les plus coûteuses actuellement en mémoire dans le library cache (v$sqlarea), triées par nombre moyen de lectures disque par exécution. N est demandé à l'exécution (variable de substitution &top).

À la différence de la famille Sess users by CPU (qui identifie quelle session consomme), ce script s'intéresse à quelle requête SQL consomme, toutes sessions confondues — les deux angles sont complémentaires pour un diagnostic de performance complet. Voir aussi Sql pour le langage lui-même.

Script

SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SET SERVEROUTPUT ON

BEGIN

  Dbms_Output.Enable(1000000);

  Dbms_Output.Put_Line(Rpad('SQL Text',50,' ') ||
                       Lpad('Reads/Execution',16,' ') ||
                       Lpad('Buffer Gets',12,' ') ||
                       Lpad('Disk Reads',12,' ') ||
                       Lpad('Executions',12,' ') ||
                       Lpad('Sorts',12,' ') ||
                       Lpad('Address',10,' '));
  Dbms_Output.Put_Line(Rpad('-',50,'-') || ' ' ||
                       Lpad('-',15,'-') || ' ' ||
                       Lpad('-',11,'-') || ' ' ||
                       Lpad('-',11,'-') || ' ' ||
                       Lpad('-',11,'-') || ' ' ||
                       Lpad('-',11,'-') || ' ' ||
                       Lpad('-',9,'-'));

  FOR cur_rec IN (SELECT * FROM (SELECT Substr(a.sql_text,1,50) sql_text,
                    Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
                         a.buffer_gets,
                         a.disk_reads,
                         a.executions,
                         a.sorts,
                         a.address
                  FROM   v$sqlarea a
                                ORDER BY 2 DESC) WHERE ROWNUM <= &top) LOOP
    Dbms_Output.Put_Line(Rpad(cur_rec.sql_text,50,' ') ||
                         Lpad(cur_rec.reads_per_execution,16,' ') ||
                         Lpad(cur_rec.buffer_gets,12,' ') ||
                         Lpad(cur_rec.disk_reads,12,' ') ||
                         Lpad(cur_rec.executions,12,' ') ||
                         Lpad(cur_rec.sorts,12,' ') ||
                         Lpad(cur_rec.address,10,' '));

  END LOOP;

END;
/

L'appel Dbms_Output.Enable(1000000) fixe explicitement la taille du tampon de sortie — un réglage hérité des versions antérieures à Oracle 10g Release 2, où la taille par défaut était limitée (2000 octets, avec un maximum réglable jusqu'à 1 Mo). Depuis 10gR2, le tampon est illimité par défaut et cet appel est sans effet, mais reste inoffensif.

Voir aussi

• Sess users by CPU — approche complémentaire par session plutôt que par requête
• Sql — langage SQL