IMU Technology and redo private strand technology in Oracle

oracle030

Oracle030oracle IMU technology and redo private strand technology



3. Illustrate Oracle IMU mechanism
SELECT * from V$sysstat where name is like '%imu% ';

statistic#, NAME, CLASS, VALUE, stat_id 312 IMU commits 128 393 1914489094 313 IMU Flushes 2099506212 314 IMU Contention 128 1 2909373607 315 IMU recursive-transaction Flush 128 2 2591100633 The 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 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. Diagram of Oracle private redo strands mechanism

To view the use of rollback segments, which user is using a rollback segment resource 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; Check the Undo segment status Select Usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks From V$rollstat order by Rssize; Determine which undo tablespace the current routine is using Show parameter Undo_tablespace Show all undo table spaces for a database SELECT tablespace_name from dba_tablespaces WHERE contents= ' UNDO '; Show undo Table Space Statistics SELECT to_char (begin_time, ' HH24:MI:SS ') Begin_time, To_char (end_time, ' HH24:MI:SS ') end_time,undoblks From V$undostat; Show Undo Segment Statistics SELECT A.name, B.xacts, B.writes, b.extents From V$rollname A, V$rollstat b WHERE A.usn=b.usn; Show active Transaction information 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 '; Common columns in the 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 the active extents, averaged over time. Status Rollback segment Status: ONLINE PENDING OFFLINE OFFLINE Full Curext current Extent CURBLK Current Block

5. Read consistency
ORA-01555 Error

6. Undo Advisor
Em

Redo-Read Consistency (ORA-01555 error mechanism problem) issue undo table space Size setting



solve the problem: 1. Make sure that undo tablespace data is at least longer than the longest SQL statement2. Increase the size of the undo table spaceis to find the size according to time.

IMU Technology and redo private strand technology in Oracle