与undo 有关的事件
SELECT * FROM v$statname WHERE NAME LIKE '%undo%'
---------- -------------------------------------------
DBWR undo block writes
undo change vector size
transaction tables consistent reads - undo records applied
data blocks consistent reads - undo records applied
rollback changes - undo records applied
auto extends on undo tablespace
total number of undo segments dropped
global undo segment hints helped
global undo segment hints were stale
local undo segment hints helped
local undo segment hints were stale
undo segment header was pinned
SMON posted for undo segment recovery
SMON posted for undo segment shrink
IMU undo retention flush
IMU undo allocation size
---------------------------------------------------------------------
比较关注的统计项目
undo change vector size
产生undo数据的大小,bytes计算，分析单个事务|整个系统的
undo产生变化情况都是很有用
data blocks consistent reads --undo records applied
一致性读，bytes
rollback changes --undo reocords applied
回滚操作所应用的undo记录的大小，单位是bytes
----------------------------------------------------------------------
--------------------实验- --------------------------
--mystat.sql
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
-- mystat2.sql
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
---通过观察v$mystat变换来观察当前会话事务undo的产生情况
--表DBA_hist_sysstat 每隔1小时的v$sysstat的快照，
--通用，将undi change 改成别的事件名称
SET pages 999
SET lines 140
col curval FORMAT 99999999999999999
col prevval FORMAT 99999999999999999
SELECT cur_stat.snap_id CurID,
prev_stat.snap_id PrevID,
To_char(sn.begin_interval_time, 'YYYY-MM-DD HH24:MI') BeginTime,
To_char(sn.end_interval_time, 'YYYY-MM-DD HH24:MI') EndTime,
cur_stat.VALUE curval,
prev_stat.VALUE prevval,
ROUND(( cur_stat.VALUE - prev_stat.VALUE )/1024/1024,2) "UNDO Gen(MB/Hour)"
FROM dba_hist_snapshot sn,
(SELECT snap_id, VALUE
FROM dba_hist_sysstat
WHERE stat_name = 'undo change vector size') cur_stat,
(SELECT snap_id,VALUE
FROM dba_hist_sysstat
WHERE stat_name = 'undo change vector size') prev_stat
WHERE sn.snap_id = cur_stat.snap_id
AND cur_stat.snap_id = prev_stat.snap_id + 1
AND sn.begin_interval_time > sysdate-3
ORDER BY 1
/
--after run
CURID PREVID BEGINTIME ENDTIME CURVAL PREVVAL UNDO Gen(MB/Hour)
----- ------ ---------------- ---------------- --------- ---------- -----------------
3 2 2011-02-27 02:56 2011-02-27 03:07 188080 1298732 -1.06
4 3 2011-02-27 03:07 2011-02-27 04:00 656708 188080 .45
5 4 2011-02-27 04:00 2011-02-27 05:00 1122792 656708 .44
6 5 2011-02-27 05:00 2011-02-27 06:00 1547508 1122792 .41
7 6 2011-02-27 06:00 2011-02-27 07:00 1976980 1547508 .41
8 7 2011-02-28 23:57 2011-03-01 00:08 6570100 1976980 4.38
9 8 2011-03-01 00:08 2011-03-01 01:00 12689980 6570100 5.84
10 9 2011-03-01 01:00 2011-03-01 02:00 13180860 12689980 .47
11 10 2011-03-01 02:00 2011-03-01 03:00 15099052 13180860 1.83
12 11 2011-03-01 12:08 2011-03-01 12:19 140304 15099052 -14.27
13 12 2011-03-01 12:19 2011-03-01 13:00 589088 140304 .43
14 13 2011-03-01 13:00 2011-03-01 14:00 1241508 589088 .62
15 14 2011-03-01 22:09 2011-03-01 22:20 3406144 1241508 2.06
------------------------------------------------------------------------------------
--显示的UNDO表空间说明当前正在被使用，不显示说明没被使用
--（一）
col tablespace_name for a20
SELECT D.TABLESPACE_NAME,
D.SPACE "TOTAL_SPACE(M)",
U.U_SPACE "USED_SPACE(M)",
ROUND((U.U_SPACE/D.SPACE)*100,2) "USED_RATE(%)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 U_SPACE FROM DBA_UNDO_EXTENTS WHERE STATUS='ACTIVE'
GROUP BY TABLESPACE_NAME) U
WHERE D.TABLESPACE_NAME=U.TABLESPACE_NAME;
--after run
TABLESPACE_NAME TOTAL_SPACE(M) USED_SPACE(M) USED_RATE(%)
-------------------- -------------- ------------- ------------
UNDOTBS1 25 .0625 .25
--详细（二）
SELECT seg.tablespace_name "Tablespace Name", ts.bytes/1024/1024 "TS Size(MB)",
ue.status "UNDO Status", count(*) "Used Extents",
round(sum(ue.bytes)/1024/1024, 2) "Used Size(MB)",
round(sum(ue.bytes)/ts.bytes*100, 2) "Used Rate(%)"
FROM dba_segments seg, DBA_UNDO_EXTENTS ue,
(SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files GROUP BY tablespace_name) ts
WHERE ue.segment_NAME=seg.segment_NAME and seg.tablespace_name=ts.tablespace_name
GROUP BY seg.tablespace_name, ts.bytes, ue.status
ORDER BY seg.tablespace_name;
--after run
Tablespace TS Size(MB) UNDO Stat Used Extents Used Size(MB) Used Rate(%)
---------- ----------- --------- ------------ ------------- ------------
UNDOTBS1 25 ACTIVE 1 .06 .25
UNDOTBS1 25 EXPIRED 122 13.25 53
UNDOTBS1 25 UNEXPIRED 13 .81 3.25
--查找session,undo 大小
--等等
--得到活动事务的UNDO,sid,得到的语句并不准确
--是当前的SID所执行的最后一个SQL语句
col sid for 99999
col osuser for a8
col username for a12
col tablespace_name for a10
col segment for a15
col sql_text for a30
col status for a10
set lines 150 pages 999
SELECT sess.SID,
sess.serial#, sess.osuser, sess.username,
rseg.segment_name SEGMENT, rseg.tablespace_name,
trans.status,
trans.used_ublk,
trans.used_ublk*8/1024 "UNDO SIZE(M)",
rseg.STATUS,
sa.sql_text,
sess.program
FROM v$session sess, v$transaction trans, dba_rollback_segs rseg, v$sql sa
WHERE sess.taddr=trans.addr AND trans.xidusn=rseg.segment_id(+)
AND (sess.sql_hash_value=sa.hash_value OR sess.prev_hash_value=sa.hash_value)
ORDER BY sql_text;
