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

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

oracle030

oracle030Oracle中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資料表空間的大小就是根據時間找大小。