暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / [Oracle]你需要掌握的undo知识

[Oracle]你需要掌握的undo知识

DBA的自我修养 2021-04-08
3560

本文不介绍oracle undo的基本知识。

1. undo的相关视图

  • V$ROLLNAME：回滚段名称和回滚段ID信息。对于回滚表空间，oracle默认会初始化10个回滚段，然后自动的分配或回收

  • V$ROLLSTAT：该视图保存着所有UNDO表空间中每一个已分配的回滚段当前状态以及相关的统计信息，不显示状态在OFFLINE的回滚段。

    USN：回滚段号                                   对应XIDUSN:就是事务的回滚段号

    RSSIZE：当前回滚段大小                   RSSIZE + 8k = dba_segments.bytes

    HWMSIZE: 回滚段曾经最大的大小

    XACTS：活动事务数

    WRITES：回滚段写入字节数(单位:bytes)

    WRITES：回滚段写入字节数(单位:bytes)

    SHRINKS：回滚段收缩次数

    EXTENDS：回滚段扩展次数

    WRAPS：回滚段翻转(wrap)次数

    GETS：获取回滚段头次数

    WAITS：回滚段头等待次数

    OPTSIZE: 最优大小，为optimal参数设定的值

  1. 事务可以共享使用的同一回滚段。回滚段的区是循环使用的：事务从extent 1的第二个块开始使用，直到extent 5的末尾, 从一个extent跳到下一个exten称为wrap

  2. 假定这样一种情况，存在一个事务，呆在extent 3，一直没有提交，然后回滚段循环使用到了extent 2，当extent 2使用完毕的时候发现extent 3存在未提交的事务，这时即使extent 4,5,1中的事务都已经提交，当前事务也不能越过extent 3而去使用后面的可使用的extent，那么只好扩展新的extent，假定为extent 2-1，因为回滚段之间的区是链状串起来的，但节点2-1的下一个extent依然是extent 3，假如2-1使用完毕发现extent 3仍然存在未提交的事务，则继续扩展。

  3. 回滚段在扩展后，是需要回收的。Smon进程每12h作为一个周期会对所有回滚段进行回收。shrinks表示回收的次数。

  4. ORACLE 提供了 optimal 参对回滚段进行控制，该参数的意义是回滚段在扩展过程中，当事务完成后（发commit，rollback后），将回滚段的大小按照optimal 值进性收缩。

  • V$UNDOSTAT：保存了某一时间段的整个UNDO表空间使用的统计信息以及UNDO自动优化的结果，超出时间就会到DBA_HIST_UNDOSTAT中。

    BEGIN_TIME：统计开始时间

    END_TIME：统计结束时间

    UNDOTSN：最后报告的活动的UNDO表空间的ID。

    UNDOBLKS：期间产生的UNDO数据块的总数。

    TXNCOUNT：期间执行事务的总数。

    MAXQUERYLEN：期间完成的单个查询执行时间最长的长度，单位是秒。此时间计算方法是从游标打开到最后一次执行/提取数据所花费的时间。利用此时间可以调整相应的UNDO RETENTION。不过由于存在游标打开但是中间等待了很长时间没有操作之后再度取数据的情况，因此次数据也不一定准确。

    MAXQUERYID：上面所说查询的SQL ID。

    MAXCONCURRENCY：期间并发事务的最大数值。

    UNXPSTEALCNT：期间发生的未过期UNDO数据盗用的次数。--undo数据盗用是指undo数据在还没有过期的情况下，因为undo表空间大小或者undo_retention的设置等导致的数据块的被盗用，可以分为未过期的undo数据盗用和已过期的undo数据盗用

    UNXPBLKRELCNT：期间发生的未过期UNDO数据被盗用数据块的数量。

    UNXPBLKREUCNT：期间发生的未过期UNDO数据盗用后被重用的数据块的数量。

    EXPSTEALCNT：期间发生的盗用次数。

    EXPBLKRELCNT：期间发生的被盗用UNDO数据块数量。

    EXPBLKREUCNT：期间发生的被盗用数据块被重用的数量。

    SSOLDERRCNT：期间ORA-1555错误发生次数。

    NOSPACEERRCNT：期间空间不足错误发生次数。

    ACTIVEBLKS：采样时刻活动的UNDO块数量。

    UNEXPIREDBLKS：采样时刻未过期的UNDO块数量。

    EXPIREDBLKS：采样时刻已过期的UNDO块数量。

    TUNED_UNDORETENTION：UNDO表空间中不会被回收的UNDO数据到现在的时间，以秒计。通过查询这个字段我们能知道在之前某个特定时间完成的事务的UNDO数据是否还存在，对估计Flashback的可用时间很有帮助。

  • DBA_ROLLBACK_SEGS：此字典显示所有回滚段的当前状态以及与存储空间分配相关的信息，包括undo表空间的使用情况。

  • DBA_UNDO_EXTENTS：保存了UNDO表空间中所有已分配的数据区的存储空间分配情况与使用情况，是得到UNDO数据当前存在状态的一个重要的视图。

2.UNDO表空间使用率

SELECT d.tablespace_name ,
       TO_CHAR(NVL(a.bytes  1024  1024, 0),'99,999,999.99') "SIZE (M)",
       TO_CHAR((a.bytes-f.bytes)/1024/1024,'99,999,999.99') "Used_HWM (M)",
       TO_CHAR(NVL((a.bytes-f.bytes)/a.bytes* 100, 0),'99,999,999.99') "Used_HWM %",
       TO_CHAR(NVL(u.bytes, 0)  1024  1024,'99,999,999.99') "Using (M)",
       TO_CHAR(NVL(u.bytes  a.bytes * 100, 0), '990.00') "Using %",
       a.autoext "Autoextend",
       TO_CHAR(NVL(a.bytes - NVL(u.bytes, 0), 0)  1024  1024,'99,999,999.99') "Free (M)",
       d.status,
       a.count "# of datafiles",
       d.contents "TS type",
       d.extent_management "EXT MGMT",
       d.segment_space_management "Seg Space MGMT"
  FROM sys.dba_tablespaces d,
       (SELECT tablespace_name,SUM(bytes) bytes,COUNT(file_id) count, decode(sum(decode(autoextensible, 'NO', 0, 1)),0,'NO','YES') autoext FROM dba_data_files GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
        FROM (SELECT tablespace_name, sum(bytes) bytes, status from dba_undo_extents WHERE status = 'ACTIVE' group by tablespace_name, status
              UNION ALL
              SELECT tablespace_name, sum(bytes) bytes, status from dba_undo_extents WHERE status = 'UNEXPIRED' group by tablespace_name, status)
        group by tablespace_name) u,
        (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = u.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND d.contents = 'UNDO';
   
TABLESPACE_NAME    SIZE (M)       Used_HWM (M)   Used_HWM %     Using (M)      Using % Aut Free (M)       STATUS           # of datafiles TS type   EXT MGMT   Seg Sp
------------------ -------------- -------------- -------------- -------------- ------- --- -------------- ---------- -------------------- --------- ---------- ------
UNDOTBS1                   840.00          52.50           6.25          34.31    4.08 YES         805.69 ONLINE                        1 UNDO      LOCAL      MANUAL
UNDOTBS2                   875.00          20.69           2.36           9.25    1.06 YES         865.75 ONLINE                        1 UNDO      LOCAL      MANUAL  

  • SIZE (M) 是undo表空间物理文件总大小

  • Used_HWM 是undo表空间已分配回滚段总大小，等于 ACTIVE + UNEXPIRED + EXPIRED extents

  • Using (M) 单单指 ACTIVE + UNEXPIRED extens 总大小。实际上关注这个值更好意义。

对应起来我们可以查undo extents的各个状态的总大小：

select tablespace_name, status, sum(bytes)/1024/1024 AS space_MB
FROM dba_undo_extents
GROUP by tablespace_name, status
ORDER by 1;

TABLESPACE_NAME      STATUS     Used (M)       Using %
-------------------- ---------- -------------- --------------
UNDOTBS1             EXPIRED             17.19           2.05
UNDOTBS1             UNEXPIRED           34.31           4.08
UNDOTBS2             EXPIRED             10.44           1.19
UNDOTBS2             UNEXPIRED            9.25           1.06

3. 回滚段各个状态占用状态

有时候做分析时，我们需要查回滚段的使用情况：

col INST_ID for 999
col USN for 9999
col segment_name format a25 head "Segment Name"
col "ACT BYTES" format 999,999,999,999 head "Active Bytes"
col "UNEXP BYTES" format 999,999,999,999 head "Unexpired Bytes"
col "EXP BYTES" format 999,999,999,999 head "Expired Bytes"
col "RSSIZE BYTES" format 999,999,999,999
col "HWMSIZE BYTES" format 999,999,999,999
col "WRITES (MB)" format 999,999,999
col "used%" format 9999.99
col XACTS for 9999
with c as (
select TABLESPACE_NAME,segment_name, nvl(sum(act),0) "ACT BYTES",
nvl(sum(unexp),0) "UNEXP BYTES",
nvl(sum(exp),0) "EXP BYTES"
from (select TABLESPACE_NAME,segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp
from dba_undo_extents where status='ACTIVE' group by TABLESPACE_NAME,segment_name
union
select TABLESPACE_NAME,segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp
from dba_undo_extents where status='UNEXPIRED' group by TABLESPACE_NAME,segment_name
union
select TABLESPACE_NAME,segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp
from dba_undo_extents where status='EXPIRED' group by TABLESPACE_NAME,segment_name)
group by TABLESPACE_NAME,segment_name)
select a.INST_ID,b.segment_id USN,b.segment_name,b.tablespace_name,b.status,a.RSSIZE "RSSIZE BYTES",a.HWMSIZE "HWMSIZE BYTES",
c."ACT BYTES", c."UNEXP BYTES",c."EXP BYTES",
round((c."ACT BYTES"+c."UNEXP BYTES")/a.RSSIZE*100,2) "used%",
Trunc(a.WRITES/1024/1024) "WRITES (MB)",a.XACTS
from gv$rollstat a,dba_rollback_segs b,c
where a.usn(+)=b.segment_id
and c.segment_name=b.segment_name(+)
and b.status!='OFFLINE'
order by INST_ID,"ACT BYTES","UNEXP BYTES",USN;

INST_ID   USN Segment Name              TABLESPACE_NAME    STATUS         RSSIZE BYTES    HWMSIZE BYTES     Active Bytes  Unexpired Bytes    Expired Bytes  used%  WRITES (MB) XACTS
------- ----- ------------------------- ------------------ ---------- ---------------- ---------------- ---------------- ---------------- ---------------- ------ ------------ -----
      1     6 _SYSSMU6_2216359420$      UNDOTBS1           ONLINE            2,220,032      788,652,032                0           65,536        2,162,688   2.95        3,033     0
      1     1 _SYSSMU1_1142801762$      UNDOTBS1           ONLINE            1,171,456      780,263,424                0        1,048,576          131,072  89.51           19     0
      1     2 _SYSSMU2_501694502$       UNDOTBS1           ONLINE            1,171,456      771,874,816                0        1,048,576          131,072  89.51        3,575     0
      1     3 _SYSSMU3_1034006492$      UNDOTBS1           ONLINE            1,171,456      797,040,640                0        1,048,576          131,072  89.51        3,650     0
      1     5 _SYSSMU5_3054426872$      UNDOTBS1           ONLINE            1,171,456      511,827,968                0        1,048,576          131,072  89.51          845     0
      1     7 _SYSSMU7_1113077419$      UNDOTBS1           ONLINE            2,285,568      797,040,640                0        1,048,576        1,245,184  45.88        2,572     0
      1     8 _SYSSMU8_3339169850$      UNDOTBS1           ONLINE            2,220,032    4,243,709,952                0        1,048,576        1,179,648  47.23          542     0
      1     9 _SYSSMU9_159023195$       UNDOTBS1           ONLINE            1,171,456      922,935,296                0        1,048,576          131,072  89.51          563     0
      1    10 _SYSSMU10_18589510$       UNDOTBS1           ONLINE            3,268,608      922,869,760                0        1,048,576        2,228,224  32.08        3,645     0
      1    23 _SYSSMU23_1455693634$     UNDOTBS1           ONLINE            1,171,456      545,382,400                0        1,048,576          131,072  89.51        2,653     0
      1     4 _SYSSMU4_1502175470$      UNDOTBS1           ONLINE            8,511,488      796,909,568                0        8,388,608          131,072  98.56          709     0
      1     0 SYSTEM                    SYSTEM             ONLINE              450,560          450,560                                                                      0     0
      2    13 _SYSSMU13_1494371450$     UNDOTBS2           ONLINE            3,268,608      788,652,032                0        1,048,576        2,228,224  32.08        2,560     0
      2    16 _SYSSMU16_3429729781$     UNDOTBS2           ONLINE            2,220,032      797,040,640                0        1,048,576        1,179,648  47.23        3,597     0
      2    17 _SYSSMU17_2189495792$     UNDOTBS2           ONLINE            2,220,032      377,610,240                0        1,048,576        1,179,648  47.23        2,832     0
      2    18 _SYSSMU18_636207217$      UNDOTBS2           ONLINE            3,268,608      377,610,240                0        1,048,576        2,228,224  32.08        2,314     0
      2    19 _SYSSMU19_2304064609$     UNDOTBS2           ONLINE            2,220,032      428,007,424                0        1,048,576        1,179,648  47.23        2,824     0
      2    20 _SYSSMU20_1682815337$     UNDOTBS2           ONLINE            2,220,032      931,258,368                0        1,179,648        1,048,576  53.14        2,287     0
      2    11 _SYSSMU11_4181922804$     UNDOTBS2           ONLINE            8,511,488      369,221,632                0        8,388,608          131,072  98.56        2,558     0
      2    12 _SYSSMU12_2747074303$     UNDOTBS2           ONLINE            8,511,488      385,998,848                0        8,388,608          131,072  98.56        2,702     0
      2    14 _SYSSMU14_1252838626$     UNDOTBS2           ONLINE            8,511,488      838,983,680                0        8,388,608          131,072  98.56        1,415     0
      2    15 _SYSSMU15_3474674857$     UNDOTBS2           ONLINE           13,754,368      797,040,640                0        8,388,608        5,373,952  60.99        3,673     0
      2    21 _SYSSMU21_3544942741$     UNDOTBS2           ONLINE            9,560,064      948,035,584                0        9,502,720           65,536  99.40          271     0
      2     0 SYSTEM                    SYSTEM             ONLINE              450,560          450,560                                                                      0     0

活动事务占用的undo大小，属于ACTIVE extents

SELECT s.sid, s.serial#, s.username,u.TABLESPACE_NAME, u.segment_name, count(u.extent_id) "Extent Count", t.used_ublk, t.used_urec, s.program       --used_ublk: Number of undo blocks used
FROM v$session s, v$transaction t, dba_undo_extents u
WHERE s.taddr = t.addr and u.segment_name like '_SYSSMU'||t.xidusn||'_%$' and u.status = 'ACTIVE'
GROUP BY s.sid, s.serial#, s.username, u.TABLESPACE_NAME, u.segment_name, t.used_ublk, t.used_urec, s.program
ORDER BY t.used_ublk desc, t.used_urec desc, s.sid, s.serial#, s.username, s.program;

4.Unexpired undo 太多

从oracle10g开始，oracle提供了Automatic UNDO Retention特性，即oracle会根据实际情况自动调整undo_retention，面对长查询为了尽可能避免ORA-01555的报错，尽可能保留更多的UNEXPIRED EXTENT，ORACLE会将这些查询的undo信息保留时间变长，选择在undo tablespace中扩充rollback segment，所以undo tablespace使用率会不断上升，最终导致了undo tablespace空间使用率达到100%。

相对于undo_retention参数，这个自动调整的undo_retention我们叫：TUNED_UNDORETENTION。

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select BEGIN_TIME,END_TIME,MAXQUERYLEN,MAXQUERYSQLID,TUNED_UNDORETENTION from DBA_HIST_UNDOSTAT order by 1;
select BEGIN_TIME,END_TIME,MAXQUERYLEN,MAXQUERYID,SSOLDERRCNT,TUNED_UNDORETENTION from v$undostat order by 1;
--默认情况下每10分钟增加一条记录。该视图记录4天以内的UNDO表空间使用情况，超过4天可以查询DBA_HIST_UNDOSTAT视图

BEGIN_TIME          END_TIME            MAXQUERYLEN MAXQUERYSQLID TUNED_UNDORETENTION
------------------- ------------------- ----------- ------------- -------------------
2017-06-20 02:00:58 2017-06-20 02:10:58          63 duhbj66b5nxda                 966
2017-06-20 02:10:58 2017-06-20 02:20:58           0                               900
2017-06-20 02:20:58 2017-06-20 02:30:58          70 duhbj66b5nxda                 972
2017-06-20 02:30:58 2017-06-20 02:40:58           0                               900
2017-06-20 02:40:58 2017-06-20 02:50:58          81 duhbj66b5nxda                 981

TUNED_UNDORETENTION太高会导致大量Unexpired undo extents，可以采用以下之一的应急方法：

  1. 通过修改隐含参数关闭AUR功能alter system set "_undo_autotune" = false;以上设定生效后，V$UNDOSTAT视图上TUNED_UNDORETENTION列不再更新，且撤销信息最短保留时间固定为参数UNDO_RETENTION的设定值。该参数可以不用重启数据库而动态设置生效。

  2. 限制TUNED_UNDORETENTIONSet the AUTOEXTEND and MAXSIZE attributes of each datafile of the undo tablespace in such a way that they are autoextensible and the MAXSIZE is equal to the current size(so the undo tablespace now has the AUTOEXTEND attribute but does not autoextend):

ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size> ;

select 'ALTER DATABASE DATAFILE '''||file_name||''' AUTOEXTEND ON MAXSIZE '|| bytes ||';' from dba_data_files where tablespace_name in (select distinct tablespace_name from dba_rollback_segs);

With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the undo tablespace size.Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

  1. 在pfile中添加 _smu_debug_mode = 33554432This again alters the algorithm used for calculating tuned_undoretention and thus the tuned_undoretention values set wont be too high compared to the actual requirements.

With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the undo tablespace size. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

  1. set the _first_spare_parameter (10.2) or _highthreshold_undoretention (11.x) instance parameter to a value limiting the tuned undo retention value.This value acts as an upper limit for the tuned_undoretention.

5.Undo回滚段管理

  1. 收缩undo回滚段

select 'alter rollback segment "' || segment_name || '" shrink;' from sys.dba_rollback_segs where status = 'ONLINE';

  1. 手动删除undo回滚段在AUM模式下，默认不允许对undo segment进行online/offline/drop操作；必须设置参数_smu_debug_mode才能在AUM模式下对undo segment进行操作


    alter system set "_smu_debug_mode" = 45;
注意:该参数只能在system级别配置

select segment_id,file_id,block_id,segment_name,tablespace_name,status from dba_rollback_segs;

alter rollback segment "_SYSSMU9$" offline;
drop rollback segment "_SYSSMU9$";        





oracle
文章转载自DBA的自我修养，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论