首页 » ORACLE » Script: AWR snap sessions,load profile, SCN or Transactions per second

Script: AWR snap sessions,load profile, SCN or Transactions per second

在AWR报告的头部有如下面的信息,但是生成的sql是被加壳的,尝试用sql手动统计这部分信息

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 2631 22-Nov-12 08:00:36 948 19.9
End Snap: 2632 22-Nov-12 09:00:55 820 19.5
Elapsed: 60.31 (mins)
DB Time: 7,366.69 (mins)
SELECT "snap time",
       sessions,
       cursors,
       TRUNC (cursors / sessions, 1) "cursors/session"
  FROM (SELECT VALUE sessions
          FROM DBA_HIST_SYSSTAT
         WHERE snap_id = 2631 AND STAT_NAME = 'logons current'),
       (SELECT VALUE cursors
          FROM DBA_HIST_SYSSTAT
         WHERE snap_id = 2631 AND STAT_NAME LIKE 'opened cursors current'),
       (SELECT end_interval_time "snap time"
          FROM dba_hist_snapshot
         WHERE snap_id = 2631);

snap time              SESSIONS    CURSORS cursors/session
-------------------- ---------- ---------- ---------------
2012-11-22 08:00:36         948      18830            19.8

SELECT TRUNC (  (  (SELECT end_interval_time + 1 - 1
                      FROM dba_hist_snapshot
                     WHERE snap_id = 2632)
                 - (SELECT end_interval_time + 1 - 1
                      FROM dba_hist_snapshot
                     WHERE snap_id = 2631))
              * 24
              * 60,
              2)
          "Elapsed mins"
  FROM DUAL;

Elapsed mins
------------
       60.31

Tip:这里有个小技巧,timestamp类型相减通过隐藏转换也可以像date类型一样返回以天为单位的数值。

--列出AWR中每个快照的SESSIONS数,观察登录数的变化趋向

  SELECT sn.end_interval_time, snap_id, VALUE sessions
    FROM    DBA_HIST_SYSSTAT st
         JOIN
            dba_hist_snapshot sn
         USING (snap_id, dbid, instance_number)
   WHERE STAT_NAME = 'logons current'
ORDER BY 1;

Load Profile

Per Second
Redo size: 6,476.10
Logical reads: 6,091.91
Block changes: 36.40
Physical reads: 13.32
Physical writes: 4.32
User calls: 412.65
Parses: 86.00
Hard parses: 1.23
Sorts: 21.06
Logons: 2.23
Executes: 156.41
Transactions: 5.13

 

WITH sysstat
     AS (SELECT sn.begin_interval_time begin_interval_time,
                sn.end_interval_time end_interval_time,
                ss.stat_name stat_name,
                ss.VALUE e_value,
                LAG (ss.VALUE, 1)
                   OVER (PARTITION BY stat_name ORDER BY ss.snap_id)
                   b_value,
                sn.snap_id
           FROM dba_hist_sysstat ss, dba_hist_snapshot sn
          WHERE     TRUNC (sn.begin_interval_time) = TRUNC (SYSDATE) --need modify
                AND ss.snap_id = sn.snap_id
                AND ss.dbid = sn.dbid
                AND ss.instance_number = sn.instance_number
                AND ss.dbid = (SELECT dbid FROM v$database)
                AND ss.instance_number =
                       (SELECT instance_number FROM v$instance)
                AND ss.stat_name IN ('parse count (total)',
                                     'parse count (hard)',
                                     'physical reads',
                                     'physical writes',
                                     'redo size'))
  SELECT snap_id,
            TO_CHAR (BEGIN_INTERVAL_TIME, 'yyyy-mm-dd(hh24:mi)')
         || TO_CHAR (END_INTERVAL_TIME, '-(hh24:mi)')
            date_time,
         stat_name,
         ROUND (
              (e_value - NVL (b_value, 0))
            / (    EXTRACT (DAY FROM (end_interval_time - begin_interval_time))
                 * 24
                 * 60
                 * 60
               +   EXTRACT (
                      HOUR FROM (end_interval_time - begin_interval_time))
                 * 60
                 * 60
               +   EXTRACT (
                      MINUTE FROM (end_interval_time - begin_interval_time))
                 * 60
               + EXTRACT (
                    SECOND FROM (end_interval_time - begin_interval_time))),
            0)
            per_sec
    FROM sysstat
   WHERE (e_value - NVL (b_value, 0)) > 0           --AND NVL (b_value, 0) > 0
                                         AND snap_id = 2781
ORDER BY 1, 2, 3

SNAP_ID DATE_TIME                 STAT_NAME               PER_SEC
---------- ------------------------- -------------------- ----------
2781 2012-11-28(13:00)-(14:00) parse count (hard)            1
2781 2012-11-28(13:00)-(14:00) parse count (total)          86
2781 2012-11-28(13:00)-(14:00) physical reads               13
2781 2012-11-28(13:00)-(14:00) physical writes               4
2781 2012-11-28(13:00)-(14:00) redo size                  6476

awr 中scn的增加速度

ALTER SESSION SET nls_date_format='dd-mon-yy';
SET LINES 160 PAGES 1000 ECHO OFF FEEDBACK OFF
COL stat_name FOR a25
COL date_time FOR a40
COL BEGIN_INTERVAL_TIME FOR a20
COL END_INTERVAL_TIME FOR a20

WITH sysstat
     AS (SELECT sn.begin_interval_time begin_interval_time,
                sn.end_interval_time end_interval_time,
                ss.stat_name stat_name,
                ss.VALUE e_value,
                LAG (ss.VALUE, 1) OVER (ORDER BY ss.snap_id) b_value
           FROM dba_hist_sysstat ss, dba_hist_snapshot sn
          WHERE     ss.snap_id = sn.snap_id
                AND ss.dbid = sn.dbid
                AND ss.instance_number = sn.instance_number
                AND ss.dbid = (SELECT dbid FROM v$database)
                AND ss.instance_number =
                       (SELECT instance_number FROM v$instance)
                AND ss.stat_name = 'calls to kcmgas')
SELECT    TO_CHAR (BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi')
       || TO_CHAR (END_INTERVAL_TIME, '_hh24_mi')
          date_time,
       stat_name,
       ROUND (
            (e_value - NVL (b_value, 0))
          / (    EXTRACT (DAY FROM (end_interval_time - begin_interval_time))
               * 24
               * 60
               * 60
             +   EXTRACT (
                    HOUR FROM (end_interval_time - begin_interval_time))
               * 60
               * 60
             +   EXTRACT (
                    MINUTE FROM (end_interval_time - begin_interval_time))
               * 60
             + EXTRACT (
                  SECOND FROM (end_interval_time - begin_interval_time))),
          0)
          per_sec
  FROM sysstat
 WHERE (e_value - NVL (b_value, 0)) > 0 AND NVL (b_value, 0) > 0
/

or

alter session set NLS_DATE_FORMAT='YYYY-mm-dd hh24:mi:ss';
WITH t1
     AS (SELECT time_dp,
                  24
                * 60
                * 60
                * (time_dp - LAG (time_dp) OVER (ORDER BY time_dp))
                   timediff,
                scn - LAG (scn) OVER (ORDER BY time_dp) scndiff
           FROM smon_scn_time)
  SELECT time_dp,
         timediff,
         scndiff,
         TRUNC (scndiff / timediff) rate_per_sec
    FROM t1
ORDER BY 1;

每秒事务数

WITH stat
     AS (SELECT sn.begin_interval_time begin_interval_time,
                sn.end_interval_time end_interval_time,
                ss.stat_name stat_name,
                ss.VALUE e_value,
                LAG (ss.VALUE, 1)
                   OVER (PARTITION BY stat_name ORDER BY ss.snap_id)
                   b_value,
                sn.snap_id
           FROM dba_hist_sysstat ss, dba_hist_snapshot sn
          WHERE     TRUNC (sn.begin_interval_time) > SYSDATE - 6 --need modify
                AND ss.snap_id = sn.snap_id
                AND ss.dbid = sn.dbid
                AND ss.instance_number = sn.instance_number
                AND ss.dbid = (SELECT dbid FROM v$database)
                AND ss.instance_number =
                       (SELECT instance_number FROM v$instance)
                AND ss.stat_name IN ('user rollbacks', 'user commits'))
  SELECT end_interval_time,
         ROUND (SUM ( (e_value - NVL (b_value, 0))) / 60 / 60, 2)
            transactions_per_sec
    FROM stat
GROUP BY end_interval_time
ORDER BY 1

DB_TIME OF 2nodes oracle RAC

 
WITH A
     AS (  SELECT B.SNAP_ID, INSTANCE_NUMBER, SUM (VALUE) / 1000000 / 60 VALUE
             FROM dba_hist_sys_time_model B
            WHERE     B.DBID = (SELECT DBID FROM V$DATABASE) -- AND b.INSTANCE_NUMBER = 1
                  AND B.STAT_NAME IN ('DB time')
         GROUP BY B.SNAP_ID, INSTANCE_NUMBER
         ORDER BY SNAP_ID),
     inst1
     AS (SELECT TO_CHAR (END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI') "END_TIME",
                ROUND (VALUE - LAG (VALUE, 1, '0') OVER (ORDER BY A.SNAP_ID),
                       2)
                   "D-VALUE(inst1)"
           FROM A,
                (SELECT END_INTERVAL_TIME, SNAP_ID, INSTANCE_NUMBER
                   FROM DBA_HIST_SNAPSHOT B
                  WHERE     B.DBID = (SELECT dbid FROM v$database)
                        AND B.INSTANCE_NUMBER = 1) B               --- inst_id
          WHERE     A.SNAP_ID = B.snap_id
                AND a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
                AND END_INTERVAL_TIME >= SYSDATE - 30),
     inst2
     AS (SELECT TO_CHAR (END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI') "END_TIME",
                ROUND (VALUE - LAG (VALUE, 1, '0') OVER (ORDER BY A.SNAP_ID),
                       2)
                   "D-VALUE(inst2)"
           FROM A,
                (SELECT END_INTERVAL_TIME, SNAP_ID, INSTANCE_NUMBER
                   FROM DBA_HIST_SNAPSHOT B
                  WHERE     B.DBID = (SELECT dbid FROM v$database)
                        AND B.INSTANCE_NUMBER = 2) B               --- inst_id
          WHERE     A.SNAP_ID = B.snap_id
                AND a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
                AND END_INTERVAL_TIME >= SYSDATE - 30)
SELECT end_time, "D-VALUE(inst1)", "D-VALUE(inst2)"
  FROM inst1 FULL JOIN inst2 USING (end_time);
打赏

对不起,这篇文章暂时关闭评论。