Oracle中IMU技术和redo private strand技术，imustrand

oracle030

oracle030

---

Oracle中IMU技术和redo private strand技术



3、图解Oracle IMU机制
     select * from v$sysstat where name like '%IMU%';

STATISTIC#, NAME,       CLASS, VALUE, STAT_ID 312     IMU commits     128     393     1914489094 313     IMU Flushes     128     88     2099506212 314     IMU contention     128     1     2909373607 315     IMU recursive-transaction flush     128     2     2591100633 316     IMU undo retention flush     128     0     2087226422 317     IMU ktichg flush     128     0     1206609541 318     IMU bind flushes     128     0     2756376339 319     IMU mbu flush     128     0     3723686946 320     IMU pool not allocated     128     0     659017805 321     IMU CR rollbacks     128     114     2225124543 322     IMU undo allocation size     128     1867712     244193920 323     IMU Redo allocation size     128     161408     3945654623 324     IMU- failed to get a private strand     128     0     2412863545

4、图解Oracle private redo strands机制

查看回滚段的使用情况，哪个用户正在使用回滚段的资源 select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username; 检查UNDO Segment状态 select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize; 确定当前例程正在使用的UNDO表空间 Show parameter undo_tablespace 显示数据库的所有UNDO表空间 SELECT tablespace_name FROM dba_tablespaces WHERE contents='UNDO'; 显示UNDO表空间统计信息 SELECT TO_CHAR(BEGIN_TIME,'HH24:MI:SS') BEGIN_TIME, TO_CHAR(END_TIME,'HH24:MI:SS') END_TIME,UNDOBLKS FROM V$UNDOSTAT; 显示UNDO段统计信息 SELECT a.name, b.xacts, b.writes, b.extents FROM v$rollname a, v$rollstat b WHERE a.usn=b.usn; 显示活动事务信息 Col username format a10 Col name format a10 SELECT a.username, b.name, c.used_ublk FROM v$session a, v$rollname b, v$transaction c WHERE a.saddr=c.ses_addr AND b.usn=c.xidusn AND a.username='HR'; V$ROLLSTAT中的常用列 USN            Rollback segment number EXTENTS      Number of extents in the rollback segment RSSIZE       Size (in bytes) of the rollback segment. WRITES       Number of bytes written to the rollback segment XACTS           Number of active transactions GETS           Number of header gets WAITS           Number of header waits OPTSIZE      Optimal size of the rollback segment HWMSIZE      High-watermark of rollback segment size SHRINKS      Number of times the size of a rollback segment decreases WRAPS           Number of times rollback segment is wrapped EXTENDS      Number of times rollback segment size is extended AVESHRINK      Average shrink size AVEACTIVE      Current size of active extents, averaged over time. STATUS           Rollback segment status:                ONLINE                PENDING OFFLINE                OFFLINE                FULL CUREXT          Current extent CURBLK          Current block

5、读一致性
     ORA-01555错误

6、undo advisor
     EM

redo的中读一致性（ORA-01555错误机制问题）问题undo表空间的大小设置



解决这样问题：1. 确保undo表空间数据的保留时间至少大于最长sql语句的时间             2. 增大undo表空间的大小 就是根据时间找大小。