UNDOSegment深入解析-创新互联
Undo Segment深入解析
成都创新互联是一家集网站建设,昭通企业网站建设,昭通品牌网站建设,网站定制,昭通网站建设报价,网络营销,网络优化,昭通网站推广为一体的创新建站企业,帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿,时刻以成就客户成长自我,坚持不断学习、思考、沉淀、净化自己,让我们为更多的企业打造出实用型网站。在undo自动管理时,设置了undo_retention以后,undo块就存在四种状态。
Active:表示正在使用该undo的事务还没有提交或回滚。
Inactive:表示该undo上没有活动的事务,该状态的undo可以被其他事务覆盖。
Expired:表示该undo持续inactive的时间超过undo_retention所指定的时间。
Freed:表示该undo块内容是空的,从来没有被使用过。
回滚段自动管理模式下UNDO block分配算法:
1、如果当前extent有空闲的数据块,则使用当前extent
2、如果当前extent的下一个extent已经处于过期(expired)状态,那么环绕(wrap)到一个extent,然后使用该extent的第一个数据块。
3、如果下一个extent没有expired,则从undo tablespace中分配空间。如果有剩余空间则使用新分配extent的第一个数据块。此时undo tablespace的使用率开始增加。
4、如果没有剩余空闲的extent,则从offline状态的回滚段中偷取(steal)过期的extent,加入当前回滚段,并使用第一个数据块。
5、如果offline状态的回滚段中没有expired extent,则从online状态的回滚段中偷取(steal)过期区加入到当前的回滚段中,并使用extent中的第一个数据块。
6、如果undo tablespace可以扩展,则扩展undo tablespace,并将新extent加入到当前的回滚段中,同时使用第一个数据块,此时undo所占的操作系统空间开始增加。
7、如果undo tablespace 不能扩展,则自动调整(下降幅度为10%)回滚段的保留时间,然后偷取在更短保留时间下的未过期的extent,如果还未找到过期的extent,则继续以10%的速度减少回滚段的保留时间,重复几次。
8、随机从其他offline状态的回滚段中偷取未过期(unexpired)的extent。
9、尝试使用当前回滚段中未过期的extent,如果所有的区都为ACTIVE状态,则进入下一步。
10、如果以上所有的尝试都失败,则报ORA-30036的错误。
[oracle@rh7 ~]$ oerr ora 30036 30036, 00000, "unable to extend segment by %s in undo tablespace '%s'" // *Cause: the specified undo tablespace has no more space available. // *Action: Add more space to the undo tablespace before retrying // the operation. An alternative is to wait until active // transactions to commit. 案例: 10:34:45 SYS@ prod>select tablespace_name,contents from dba_tablespaces; TABLESPACE_NAME CONTENTS ------------------------------ --------- SYSTEM PERMANENT SYSAUX PERMANENT TEMP TEMPORARY USERS PERMANENT UNDOTBS2 UNDO EXAMPLE PERMANENT TBS1 PERMANENT 7 rows selected. Elapsed: 00:00:00.03 10:34:56 SYS@ prod>create undo tablespace undotbs1 10:35:15 2 datafile '/u01/app/oracle/oradata/prod/undotbs1.dbf' size 1m; Tablespace created. Elapsed: 00:00:01.38 10:35:40 SYS@ prod>show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS2 10:35:49 SYS@ prod>alter system set undo_tablespace=undotbs1; System altered. Elapsed: 00:00:00.13 10:36:03 SYS@ prod> 10:36:03 SYS@ prod>select s.sid,s.serial#,s.username,u.segment_name,count(u.extent_id) "Extent count", 10:37:37 2 t.used_ublk,t.used_urec,s.program 10:38:00 3 from v$session s,v$transaction t,dba_undo_extents u 10:38:24 4 where s.taddr=t.addr and u.segment_name like '_SYSSMU'||t.xidusn||'_%$' and u.status='ACTIVE' 10:39:50 5 GROUP BY s.sid,s.serial#,s.username,u.segment_name,t.used_ublk,t.used_urec,s.program 10:40:37 6 order by t.used_ublk desc,t.used_urec desc,s.sid,s.serial#,s.username,s.program; 开启新的session,执行DML: 10:41:45 SYS@ prod>conn scott/tiger Connected. 10:42:45 SCOTT@ prod>insert into t1 select * from t1 where rownum <1000; 999 rows created. 查看undo tablespace 使用情况: 10:43:29 SYS@ prod> select s.sid,s.serial#,s.username,u.segment_name,count(u.extent_id) "Extent count", 2 t.used_ublk,t.used_urec,s.program 3 from v$session s,v$transaction t,dba_undo_extents u 4 where s.taddr=t.addr and u.segment_name like '_SYSSMU'||t.xidusn||'_%$' and u.status='ACTIVE' 5 GROUP BY s.sid,s.serial#,s.username,u.segment_name,t.used_ublk,t.used_urec,s.program 6* order by t.used_ublk desc,t.used_urec desc,s.sid,s.serial#,s.username,s.program SID SERIAL# USERNAME SEGMENT_NAME Extent count USED_UBLK USED_UREC PROGRAM ---------- ---------- ---------- -------------------- ------------ ---------- ---------- ---------- 37 36 SCOTT _SYSSMU12_2144756092 1 1 11 sqlplus@rh $ 6 (TNS V1- V3) Elapsed: 00:00:00.20 10:45:01 SCOTT@ prod>insert into t1 select * from t1 where rownum <20000; 19999 rows created. Elapsed: 00:00:00.07 10:44:52 SYS@ prod>select s.sid,s.serial#,s.username,u.segment_name,count(u.extent_id) "Extent count", 2 t.used_ublk,t.used_urec,s.program 3 from v$session s,v$transaction t,dba_undo_extents u 4 where s.taddr=t.addr and u.segment_name like '_SYSSMU'||t.xidusn||'_%$' and u.status='ACTIVE' 5 GROUP BY s.sid,s.serial#,s.username,u.segment_name,t.used_ublk,t.used_urec,s.program 6* order by t.used_ublk desc,t.used_urec desc,s.sid,s.serial#,s.username,s.program SID SERIAL# USERNAME SEGMENT_NAME Extent count USED_UBLK USED_UREC PROGRAM ---------- ---------- ---------- -------------------- ------------ ---------- ---------- ---------- 37 36 SCOTT _SYSSMU16_2726800344 2 7 109 sqlplus@rh $ 6 (TNS V1- V3) Elapsed: 00:00:00.01 10:45:13 SYS@ prod> 10:48:16 SYS@ prod>select a.usn,a.name,b.xacts,b.extents,b.status from v$rollname a,v$rollstat b 10:49:01 2 where a.usn=b.usn; USN NAME XACTS EXTENTS STATUS ---------- ------------------------------ ---------- ---------- --------------- 0 SYSTEM 0 6 ONLINE 11 _SYSSMU11_2517864848$ 0 2 ONLINE 12 _SYSSMU12_2144756092$ 0 2 ONLINE 13 _SYSSMU13_527038519$ 0 3 ONLINE 14 _SYSSMU14_2951869305$ 0 2 ONLINE 15 _SYSSMU15_2206823906$ 0 2 ONLINE 16 _SYSSMU16_2726800344$ 1 2 ONLINE 17 _SYSSMU17_2098084560$ 0 2 ONLINE 10:50:45 SYS@ prod>select SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID,STATUS from dba_undo_extents SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS ------------------------- ------------------------------ ---------- --------- _SYSSMU17_2098084560$ UNDOTBS1 0 UNEXPIRED _SYSSMU17_2098084560$ UNDOTBS1 1 UNEXPIRED _SYSSMU16_2726800344$ UNDOTBS1 0 ACTIVE _SYSSMU16_2726800344$ UNDOTBS1 1 ACTIVE _SYSSMU15_2206823906$ UNDOTBS1 0 UNEXPIRED _SYSSMU15_2206823906$ UNDOTBS1 1 UNEXPIRED _SYSSMU14_2951869305$ UNDOTBS1 0 UNEXPIRED _SYSSMU14_2951869305$ UNDOTBS1 1 UNEXPIRED _SYSSMU13_527038519$ UNDOTBS1 0 UNEXPIRED _SYSSMU13_527038519$ UNDOTBS1 1 UNEXPIRED _SYSSMU13_527038519$ UNDOTBS1 2 UNEXPIRED _SYSSMU12_2144756092$ UNDOTBS1 0 UNEXPIRED _SYSSMU12_2144756092$ UNDOTBS1 1 UNEXPIRED _SYSSMU11_2517864848$ UNDOTBS1 0 UNEXPIRED _SYSSMU11_2517864848$ UNDOTBS1 1 UNEXPIRED _SYSSMU30_1737877121$ UNDOTBS2 0 EXPIRED _SYSSMU30_1737877121$ UNDOTBS2 1 UNEXPIRED SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS ------------------------- ------------------------------ ---------- --------- _SYSSMU30_1737877121$ UNDOTBS2 2 EXPIRED _SYSSMU29_2754652023$ UNDOTBS2 0 EXPIRED _SYSSMU29_2754652023$ UNDOTBS2 1 EXPIRED _SYSSMU29_2754652023$ UNDOTBS2 2 EXPIRED _SYSSMU29_2754652023$ UNDOTBS2 3 UNEXPIRED _SYSSMU28_707429450$ UNDOTBS2 0 UNEXPIRED _SYSSMU28_707429450$ UNDOTBS2 1 EXPIRED _SYSSMU28_707429450$ UNDOTBS2 2 EXPIRED _SYSSMU27_3269963619$ UNDOTBS2 0 EXPIRED _SYSSMU27_3269963619$ UNDOTBS2 1 EXPIRED _SYSSMU27_3269963619$ UNDOTBS2 2 EXPIRED _SYSSMU27_3269963619$ UNDOTBS2 3 UNEXPIRED _SYSSMU27_3269963619$ UNDOTBS2 4 EXPIRED _SYSSMU27_3269963619$ UNDOTBS2 5 EXPIRED _SYSSMU26_2968904537$ UNDOTBS2 0 EXPIRED _SYSSMU26_2968904537$ UNDOTBS2 1 EXPIRED _SYSSMU26_2968904537$ UNDOTBS2 2 EXPIRED SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS ------------------------- ------------------------------ ---------- --------- _SYSSMU26_2968904537$ UNDOTBS2 3 EXPIRED _SYSSMU26_2968904537$ UNDOTBS2 4 UNEXPIRED _SYSSMU26_2968904537$ UNDOTBS2 5 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 0 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 1 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 2 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 3 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 4 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 5 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 6 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 7 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 8 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 9 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 10 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 11 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 12 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 13 EXPIRED SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS ------------------------- ------------------------------ ---------- --------- _SYSSMU25_2810228709$ UNDOTBS2 14 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 15 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 16 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 17 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 18 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 19 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 20 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 21 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 22 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 23 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 24 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 25 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 26 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 27 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 28 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 29 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 30 EXPIRED SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS ------------------------- ------------------------------ ---------- --------- _SYSSMU25_2810228709$ UNDOTBS2 31 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 32 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 33 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 34 EXPIRED _SYSSMU25_2810228709$ UNDOTBS2 35 UNEXPIRED _SYSSMU24_386518199$ UNDOTBS2 0 EXPIRED _SYSSMU24_386518199$ UNDOTBS2 1 UNEXPIRED _SYSSMU24_386518199$ UNDOTBS2 2 EXPIRED _SYSSMU23_4084707454$ UNDOTBS2 0 EXPIRED _SYSSMU23_4084707454$ UNDOTBS2 1 UNEXPIRED _SYSSMU23_4084707454$ UNDOTBS2 2 EXPIRED _SYSSMU22_3375463809$ UNDOTBS2 0 UNEXPIRED _SYSSMU22_3375463809$ UNDOTBS2 1 UNEXPIRED _SYSSMU22_3375463809$ UNDOTBS2 2 EXPIRED _SYSSMU22_3375463809$ UNDOTBS2 3 EXPIRED _SYSSMU21_2312338076$ UNDOTBS2 0 EXPIRED _SYSSMU21_2312338076$ UNDOTBS2 1 EXPIRED SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS ------------------------- ------------------------------ ---------- --------- _SYSSMU21_2312338076$ UNDOTBS2 2 UNEXPIRED 86 rows selected. Elapsed: 00:00:00.06 10:52:33 SYS@ prod>alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'; Session altered. Elapsed: 00:00:00.02 10:53:03 SYS@ prod>select BEGIN_TIME,END_TIME,UNDOTSN,UNDOBLKS,TUNED_UNDORETENTION from v$undostat; BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TUNED_UNDORETENTION ------------------- ------------------- ---------- ---------- ------------------- 2014-07-01 10:52:51 2014-07-01 10:53:08 2 0 921 2014-07-01 10:42:51 2014-07-01 10:52:51 2 74 921 2014-07-01 10:32:51 2014-07-01 10:42:51 2 43 1260 估算undo tablespace 的大小: UndoTablesapce = UR * (UPS * DBS) UR: undo_retention UPS:在业务高峰期每秒产生的undo blocks的数量 DBS:undo tablespace的数据库的大小 10:53:08 SYS@ prod>select (UR * (UPS * DBS)) AS "BYTES" 10:56:12 2 from (select value AS UR from v$parameter where name='undo_retention'), 10:57:04 3 (select undoblks/((end_time-begin_time)*900) as UPS 10:58:39 4 FROM v$undostat 10:58:50 5 where undoblks=(select max(undoblks) from v$undostat)), 10:59:29 6 (select block_size as DBS 10:59:51 7 FROM dba_tablespaces 11:00:01 8 where tablespace_name=(select upper(value) from v$parameter where name='undo_tablespace')); BYTES ---------- 87293952 Elapsed: 00:00:00.07 11:00:45 SYS@ prod> 11:00:45 SYS@ prod>select (UR * (UPS * DBS))/1024/1024 AS "BYTES" 11:01:42 2 from (select value AS UR from v$parameter where name='undo_retention'), 11:01:42 3 (select undoblks/((end_time-begin_time)*900) as UPS 11:01:42 4 FROM v$undostat 11:01:42 5 where undoblks=(select max(undoblks) from v$undostat)), 11:01:42 6 (select block_size as DBS 11:01:42 7 FROM dba_tablespaces 11:01:42 8 where tablespace_name=(select upper(value) from v$parameter where name='undo_tablespace')); BYTES ---------- 83.25 Elapsed: 00:00:00.10 11:01:44 SYS@ prod>以上部分内容参考了《Oracle DBA实战攻略》书中的内容,这里表示感谢!
另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。
网站栏目:UNDOSegment深入解析-创新互联
URL链接:http://myzitong.com/article/dcphss.html