oracle回滚段利用率,oracle程序性能优化实录-回滚段

1.1回滚段的基本概念

(1)回滚段概述

回滚段用于存放数据修改之前的值(包括数据修改之前的位置和值)。回滚段的头部包含正在使用的该回滚段事务的信息。一个事务只能使用一个回滚段来存放它的回滚信息，而一个回滚段可以存放多个事务的回滚信息。

(2)回滚段的作用

事务回滚：当事务修改表中数据的时候，该数据修改前的值(即前影像)会存放在回滚段中，当用户回滚事务(ROLLBACK)时，ORACLE将会利用回滚段中的数据前影像来将修改的到原来的值。

事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在重做日志文件中，ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。

读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。而且，当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改(语句级读一致性)。当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号(SYSTEM

CHANGE NUMBER-SCN)来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时，若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图。

(3)回滚段的类型

回滚段可分为系统回滚段和非系统回滚段,其中非系统回滚段又分为PUBLIC回滚段和PRIVATE回滚段。

回滚段：1系统回滚段

2非系统回滚段：(1)PUBLIC回滚段

(2)PRIVATE回滚段

系统回滚段用于处理涉及系统的CATALOG的事物(比如大多数的DDL),它位于SYSTEM表空间,由于只有SYSTEM表空间可以随时保持可用,因此,不要把SYSTEM回滚段放在其他的表空间中。

原则1:系统回滚段应放在SYSTEM表空间中,并且应该永远保持ONLINE状态。PUBLIC回滚段对于数据库的所有实例(INSTANCE)都是可用的,除非将其显式设置为OFFLINE。

PRIVATE回滚段是指对于数据库的某个实例是私有的,为了使用PRIVATE回滚段,某个实例应当在其INITsid.ORA的ROLLBACK_SEGMENTS中标明所有要使用的PRIVATE回滚段,或通过使用ALTER

ROLLBACK SEGMENT XXX ONLINE来使用某一个回滚段。

建议1:在单实例系统中,建议将所有回滚段设为PUBLIC.

建议2:在多实例系统中(如OPS,RAC),建议将每个实例的PRIVATE回滚段放置到访问比较快的本地设备上。

(4)回滚段的数量规划

对于OLTP系统，存在大量的小事务处理，一般建议：

数量多的小回滚段；每四个事务一个回滚段；每个回滚段不要超过十个事务。

对于批处理，一般建议：

少的大回滚段；每个事务一个回滚段。

(5)回滚段的使用

分配回滚段：当事务开始时，ORACLE将为该事务分配回滚段，并将拥有最少事务的回滚段分配给该事务。事务可以用以下语句申请指定的回滚段：

SET TRANSTRACTION USE

ROLLBACK SEGMENT rollback_segment

事务将以顺序，循环的方式使用回滚段的区(EXTENTS)，当当前区用满后移到下一个区。几个事务可以写在回滚段的同一个区，但每个回滚段的块只能包含一个事务的信息。

例如(两个事务使用同一个回滚段，该回滚段有四个区)：

Ø事务在进行中，它们正在使用回滚段的第三个区；

Ø当两个事务产生更多的回滚信息，它们将继续使用第三个区；

Ø当第三个区满后，事务将写到第四个区，当事务开始写到一个新的区时，称为翻转(WRAP)；

Ø当第四个区用满时，如果第一个区是空闲或非活动(使用该区的所有事务完成而没有活动的事务)的，事务将接着使用第一个区。

1.2Undo表空间

Undo是存储在表空间中的。UNDO表空间通常都是Oracle自动化管理(通过undo_management初始化参数确定)；UNDO表空间是用于存储DML操作的前镜像数据，它是实例恢复，数据回滚，一致性查询功能的重要组件；我们常常会忽略对它的监控，这会导致UNDO表空间可能出现以下问题：

Ø空间使用率100%，导致DML操作无法进行。

Ø告警日志中出现大量的ORA-01555告警错误。

Ø实例恢复失败，数据库无法正常打开。

(1)查找当前undo表空间

show parameter undo_tablespace

(2)创建Undo表空间undotbs2

create undo tablespaceundotbs2 datafile

'/u01/app/oracle/undotbs2.dbf'

(3)设置undotbs2为当前undo表空间

alter system set undo_tablespace=undotbs2;

(4)UNDO表空间总大小

UNDO表空间下也以段的形式存储数据，每个事务对应一个段，这种类型的段通常被称为回滚段，或者UNDO段。默认情况下，数据库实例会初始化10个UNDO段，这主要是为了避免新生成的事务对UNDO段的争用。

UNDO表空间的总大小就是UNDO表空间下的所有数据文件大小的总和

select tablespace_name,contentsfrom dba_tablespaces where

tablespace_name='UNDOTBS1'

(5)查看UNDO表空间的使用情况

select owner,segment_name,bytes/1024/1024mb from

dba_segments

where tablespace_name='UNDOTBS1';

(6)分析UNDO的使用情况

SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS)* 8 / 1024) AS "Size

M",

COUNT(*) Undo_Extent_Num FROM DBA_UNDO_EXTENTSGROUP BY

TABLESPACE_NAME, STATUS;

(7)监控undo表空间

SELECT BEGIN_TIME, END_TIME,UNDOTSN, UNDOBLKS,

TXNCOUNT,MAXCONCURRENCY AS "MAXCON"

FROM V$UNDOSTAT;

1.3回滚段的查询

(1)查询数据库的的回滚段情况

select owner,segment_id,segment_name,tablespace_name,statusfrom

dba_rollback_segs;

(2)查看系统回滚段基本信息

select segment_name,tablespace_name,bytes,next_extentfrom

dba_segments

where segment_type='ROLLBACK';

从上面仅仅是查询到回滚段的基本信息，要了目前各个回滚段的动态信息，还要查询V$ROLLNAME和V$ROLLSTAT视图。V$ROLLNAME视图只存放各回滚段的编号和名字，V$ROLLSTATS存放各个回滚段当前的情况信息。要想得到每个回滚段的信息，就要查询两个表的信息才能得到。如：

select s.usn,n.name,s.extents,s.optsize,s.hwmsize,s.statusfrom

v$rollstat s,

v$rollname n where s.usn=n.usn

(3)查看回滚段的使用情况，哪个用户正在使用回滚段的资源(当提交或回滚后资源释放):

SELECT s.username, u.nameFROM v$transaction t, v$rollstat r,

v$rollname u,

v$session s WHERE s.taddr = t.addrAND t.xidusn = r.usn AND r.usn =

u.usn

ORDER BY s.username;

(4)回滚段当前活动的事物(事务提交或回滚后自动清空)

SELECT

s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk FROM v$session s,

v$transaction t;

(5)查询是否有回滚段的争用

select * from v$waitstat;

SELECT name, waits,gets, waits/gets "Ratio" FROM v$rollstat a,

v$rollname b

WHERE a.usn =b.usn;

(6)查看回滚段的统计信息：

SELECT n.name, s.extents,s.rssize, s.optsize, s.hwmsize, s.xacts,

s.status

FROM v$rollname n, v$rollstats WHERE n.usn = s.usn;

(7)查询回滚段的事务回退率

transaction rollbacks/(transactionrollbacks+user

commits)

select name,value fromv$sysstat where name in('user

commits','transaction rollbacks');

(8)查询回滚段在使用,扩展,回缩的时候extent在循环的次数

select usn,wraps fromv$rollstat;

(9)查询回滚段收缩的情况

select usn,optsize,shrinksfrom v$rollstat;

(10)查看未提交事务的信息

Select * from v$transaction;

其中，需要重点关注used_ublk。多次查询，如果used_ublk在增大，说明正在执行数据处理；如果used_ublk在减小，说明正在执行rollback，一直到used_ublk为0表示rollback结束，可以通过这个值大致估算出rollback的时间。