在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);