Script para obter estatísticas da sessão – Oracle

Neste artigo mostrarei 2 scripts (uma na versão 10g e a outra na 11g) que irá mostrar algumas estatísticas das sessões ativas na base de dados.

Este artigo possuí algumas informações a mais do que em um outro artigo feito recentemente.

* Este script foi adaptado de alguns artigos que encontrei em fóruns recentemente.

Para a versão 10g do Oracle:

SELECT Logon_time,
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 25 AND v$sesstat.SID = v$session.sid)
            AS "PGA Memory, in MB",
         (SELECT VALUE
            FROM v$sesstat
           WHERE STATISTIC# = 12 AND v$sesstat.SID = v$session.sid)
            AS "CPU, used by session",
         ROUND ( (SELECT VALUE
                    FROM v$sesstat
                   WHERE STATISTIC# = 339 AND v$sesstat.SID = v$session.sid)
                / (SELECT DECODE (VALUE, 0, 1, VALUE)
                     FROM v$sesstat
                    WHERE STATISTIC# = 338 AND v$sesstat.SID = v$session.sid),
                2)
            AS "Hard Parse, %",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 58 AND v$sesstat.SID = v$session.sid)
            AS "Physical read bytes, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 66 AND v$sesstat.SID = v$session.sid)
            AS "Physical write bytes, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 139 AND v$sesstat.SID = v$session.sid)
            AS "Redo size, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 344 AND v$sesstat.SID = v$session.sid)
            AS "Received from client, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 343 AND v$sesstat.SID = v$session.sid)
            AS "Sent to client, in MB",
         SID,
         SERIAL#,
         v$session.STATUS,
         PROGRAM,
         USER#,
         USERNAME,
         COMMAND,
         OWNERID,
         OSUSER,
         PROCESS,
         MACHINE,
         OBJECT_NAME
    FROM    v$session
         LEFT OUTER JOIN
            DBA_OBJECTS
         ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
   WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
         --AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC;

E para a versão 11g do Oracle:

SELECT Logon_time,
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 35 AND v$sesstat.SID = v$session.sid)
          AS "PGA Memory, in MB",
       (SELECT VALUE
          FROM v$sesstat
         WHERE STATISTIC# = 17 AND v$sesstat.SID = v$session.sid)
          AS "CPU, used by session",
       ROUND ( (SELECT VALUE
                  FROM v$sesstat
                 WHERE STATISTIC# = 584 AND v$sesstat.SID = v$session.sid)
              / (SELECT DECODE (VALUE, 0, 1, VALUE)
                   FROM v$sesstat
                  WHERE STATISTIC# = 583 AND v$sesstat.SID = v$session.sid),
              2)
          AS "Hard Parse, %",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 83 AND v$sesstat.SID = v$session.sid)
          AS "Physical read bytes, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 96 AND v$sesstat.SID = v$session.sid)
          AS "Physical write bytes, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 185 AND v$sesstat.SID = v$session.sid)
          AS "Redo size, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 590 AND v$sesstat.SID = v$session.sid)
          AS "Received from client, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 589 AND v$sesstat.SID = v$session.sid)
          AS "Sent to client, in MB",
       SID,
       SERIAL#,
       v$session.STATUS,
       PROGRAM,
       USER#,
       USERNAME,
       COMMAND,
       OWNERID,
       OSUSER,
       PROCESS,
       MACHINE,
       OBJECT_NAME
  FROM    v$session
       LEFT OUTER JOIN
          DBA_OBJECTS
       ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
 WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
       --AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s