Another query for statistic

Hello everybody!

Here is another stats gathering script, work checked under 11.2:

Runs faster then previous queries.

 
SELECT SS.SID,
       USERNAME,
       CPU              AS "CPU, used by session",
       SESSION_READS    AS "Logical read bytes, in MB",
       PGA              AS "PGA Memory, in MB",
       PHYS_READS       AS "Physical read bytes, in MB",
       PHYS_WRITES      AS "Physical write bytes, in MB",
       REDO             AS "Redo size, in MB",
       NET_RECI_FROM    AS "Received from client, in MB",
       NET_SENT_TO      AS "Sent to client, in MB",
       vs.*
  FROM v$session vs,
       (SELECT SID,
               TO_NUMBER (
                  DECODE (0,
                          ROUND (session_reads / 1024 / 1024, 2), NULL,
                          ROUND (session_reads / 1024 / 1024, 2)))
                  SESSION_READS,
               TO_NUMBER (
                  DECODE (0,
                          ROUND (PGA / 1024 / 1024, 2), NULL,
                          ROUND (PGA / 1024 / 1024, 2)))
                  PGA,
               TO_NUMBER (
                  DECODE (0,
                          ROUND (CPU / 1024 / 1024, 2), NULL,
                          ROUND (CPU / 1024 / 1024, 2)))
                  CPU,
               TO_NUMBER (
                  DECODE (0,
                          ROUND (PHYS_READS / 1024 / 1024, 2), NULL,
                          ROUND (PHYS_READS / 1024 / 1024, 2)))
                  PHYS_READS,
               TO_NUMBER (
                  DECODE (0,
                          ROUND (PHYS_WRITES / 1024 / 1024, 2), NULL,
                          ROUND (PHYS_WRITES / 1024 / 1024, 2)))
                  PHYS_WRITES,
               TO_NUMBER (
                  DECODE (0,
                          ROUND (REDO / 1024 / 1024, 2), NULL,
                          ROUND (REDO / 1024 / 1024, 2)))
                  REDO,
               TO_NUMBER (
                  DECODE (0,
                          ROUND (NET_RECI_FROM / 1024 / 1024, 2), NULL,
                          ROUND (NET_RECI_FROM / 1024 / 1024, 2)))
                  NET_RECI_FROM,
               TO_NUMBER (
                  DECODE (0,
                          ROUND (NET_SENT_TO / 1024 / 1024, 2), NULL,
                          ROUND (NET_SENT_TO / 1024 / 1024, 2)))
                  NET_SENT_TO
          FROM v$sesstat PIVOT (SUM (VALUE)
                         FOR STATISTIC#
                         IN  (12  AS SESSION_READS,
                              17  AS CPU,
                              35  AS PGA,
                              83  AS PHYS_READS,
                              96  AS PHYS_WRITES,
                              185 AS REDO,
                              590 AS NET_RECI_FROM,
                              589 AS NET_SENT_TO))) SS
 WHERE vs.sid = ss.sid AND USER# > 0

Реклама

Useful scripts: Gather some session statistics…

RDBMS Version: 10g.

Script shows following session statistic values:

• PGA Memory, in MB;
• CPU, used by session;
• Hard Parse, %;
• Physical read bytes, in MB;
• Physical write bytes, in MB;
• Redo size, in MB;
• Received from client, in MB;
• Sent to client, in MB.

  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

Oracle 11g have changed statistac ID’s there for I put the same script for 11g:

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