Oracle Tuning Rollback Segments Related

Rollback Segments Here are some scripts related to Rollback Segments .

Segments ROLLBACK INFORMATION NOTES: Segment Name - Name of the rollback segment. Owner - Owner of the rollback segment. Tablespace - Name of the tablespace containing the rollback segment. Segment ID - ID number of the rollback segment. File ID - ID number of the block containing the segment header. Block ID - Starting block number of the extent. Initial Extent - Initial extent size in bytes. Next Extent - Secondary extent size in bytes. Min Extents - Minimum number of extents. Max Extents - Maximum number of extents. PCT Increase - Percent increase for extent size. Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE. Instance - Instance this rollback segment belongs to (Parallel Server), or NULL for a single-instance system . select SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, FILE_ID, BLOCK_ID, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, STATUS, INSTANCE_NUM from dba_rollback_segs order by SEGMENT_NAME

Transactions ROLLBACK STATISTIC (TRANSACTION TABLES) NOTES: Statistic Name - Name of the statistic Value - Current value

The name of the consistent changes statistic is misleading. It does not indicate the number of updates (or changes), but rather, the number of times a consistent get had to retrieve and "old" version of a block because of updates that occurred after the cursor had been opened. As of Oracle7.3, a more accurate statistic was added. Named data blocks consistent reads undo records applied; the new statistic gives the actual number of data records applied. The consistent gets statistic reflects the number of accesses made to the block buffer to retrieve data in a consistent mode. Most accesses to the buffer are done with the consistent get mechanism, which uses the SCN (System Change Number) to make sure the data being read has not changed sine the query was started. The data blocks consistent reads - undo records applied statistic reflects the number of updates (or changes) applied. select NAME, VALUE from v$sysstat where name in ( 'consistent gets', 'consistent changes', 'transaction tables consistent reads - undo records applied', 'transaction tables consistent read rollbacks', 'data blocks consistent reads - undo records applied', 'no work - consistent read gets', 'cleanouts only - consistent read gets', 'rollbacks only - consistent read gets', 'cleanouts and rollbacks - consistent read gets') order by NAME

Contention ROLLBACK CONTENTION NOTES: Segment Name - Name of the rollback segment. Seg# - Rollback segment number. Gets - Number of header gets. Waits - Number of header waits. Hit Ratio - Ratio of gets to waits. This should be >= 99%. Active Transactions - Number of active transactions. Writes - Number of bytes written to rollback segment. Hit Ratio should be >= 99% - if not, consider adding additional rollback segments. Check the system undo header, system undo block, undo header, undo block statistics under "Wait Statistics" for additional information on rollback contention. select b.NAME, a.USN seg#, GETS, WAITS,

from where

round(((GETS-WAITS)*100)/GETS,2) hit_ratio, XACTS active_transactions, WRITES v$rollstat a, v$rollname b a.USN = b.USN

Growth ROLLBACK EXTENDING AND SHRINKAGE NOTES: Rollback Segment - Name of rollback segment. Seg# - Rollback segment number. Size - Size in bytes of the rollback segment. OptSize - Optimal size of rollback segment. HWM - High Water Mark of rollback segment size. Extends - Number of times rollback segment was extended to have a new extent. Wraps - Number of times rollback segment wraps from one extent to another. Shrinks - Number of times rollback segment shrank, eliminating one or more additional extents each time. Average Shrink - Total size of freed extents divided by number of shrinks. Average Active - Current average size of active extents, where "active" extents have uncommitted transaction data. Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE. select NAME, a.USN, RSSIZE, OPTSIZE, HWMSIZE, EXTENDS, WRAPS, SHRINKS, AVESHRINK, AVEACTIVE, STATUS from v$rollstat a , v$rollname b where a.USN=b.USN order by NAME

-- --------------------------------- 0.4 QUICK VIEW ON UNDO/ROLLBACK:

-- -------------------------------SELECT substr(username, 1, 10), substr(terminal, 1, 10), substr(osuser, 1, 10), t.start_time, r.name, t.used_ublk "ROLLB BLKS", log_io, phy_io FROM sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s WHERE t.xidusn = r.usn AND t.ses_addr = s.saddr; SELECT substr(n.name, 1, 10), s.writes, s.gets, s.waits, s.wraps, s.extents, s.status, s.optsize, s.rssize FROM V$ROLLNAME n, V$ROLLSTAT s WHERE n.usn=s.usn; SELECT substr(r.name, 1, 10) "RBS", s.sid, s.serial#, s.taddr, t.addr, substr(s.username, 1, 10) "USER", t.status, t.cr_get, t.phy_io, t.used_ublk, t.noundo, substr(s.program, 1, 15) "COMMAND" FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r WHERE t.addr = s.taddr AND t.xidusn = r.usn ORDER BY t.cr_get, t.phy_io; SELECT substr(segment_name, 1, 20), substr(tablespace_name, 1, 20), status, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE FROM DBA_ROLLBACK_SEGS; select 'FREE',count(*) from sys.fet$ union select 'USED',count(*) from sys.uet$;

SELECT NAME, XACTS "ACTIVE TRANSACTIONS" FROM V$ROLLNAME, V$ROLLSTAT WHERE V$ROLLNAME.USN = V$ROLLSTAT.USN; SELECT to_char(BEGIN_TIME, 'DD-MM-YYYY;HH24:MI'), to_char(END_TIME, 'DD-MMYYYY:HH24:MI'),UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON" FROM V$UNDOSTAT WHERE trunc(BEGIN_TIME)=trunc(SYSDATE); select TO_CHAR(MIN(Begin_Time),'DD-MON-YYYY HH24:MI:SS') "Begin Time", TO_CHAR(MAX(End_Time),'DD-MON-YYYY HH24:MI:SS') "End Time", SUM(Undoblks) "Total Undo Blocks Used",SUM(Txncount) "Total Num Trans Executed",MAX(Maxquerylen) "Longest Query(in secs)",MAX(Maxconcurrency) "Highest Concurrent TrCount",SUM(Ssolderrcnt),SUM(Nospaceerrcnt) from V$UNDOSTAT; SELECT used_urec FROM v$session s, v$transaction t WHERE s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr; (used_urec = Used Undo records)

SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr; SELECT v.SID, v.BLOCK_GETS, v.BLOCK_CHANGES, w.USERNAME, w.OSUSER, w.TERMINAL FROM v$sess_io v, V$session w WHERE v.SID=w.SID ORDER BY v.SID;