- Introduction
- How Is Undo Space Managed by Oracle9i?
- What Initialization Parameters Control Undo Space?
- Can You Use Multiple Undo Tablespaces?
- How Can You Monitor Undo Space Utilization in the SMU Mode?
- How Can Contention for Rollback Segment Be Reduced?
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
How Can You Monitor Undo Space Utilization in the SMU Mode?
Answer: A number of views can be used to determine undo space information in Oracle9i:
The view V$UNDOSTAT can be used to determine statistics regarding the utilization of undo space in Oracle9i. The following query can be used to monitor the effects of transaction execution on undo space over time:
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 Transaction Count", SUM(Ssolderrcnt), SUM(Nospaceerrcnt) from V$UNDOSTAT;The view V$WAITSTAT can be used to determine the waits per undo block class. The following query can be useful:
SELECT class, count FROM V$WAITSTAT WHERE class LIKE '%undo%' AND COUNT > 0;
The view V$SYSSTAT can be used to determine the total number of data requests. The following query can be useful:
SELECT SUM(value) "DATA REQUESTS" FROM V$SYSSTAT WHERE name IN ('db block gets', 'consistent gets');The view DBA_ROLLBACK_SEGS can be used to determine storage, status, and other information about rollback segments. The following query can be useful:
SELECT segment_name, owner, tablespace_name, status, initial_extent, next_extent, min_extents, max_extents, pct_increase FROM DBA_ROLLBACK_SEGS;The view V$TRANSACTION can be used to determine the amount of undo space used per transaction. The following query can be useful:
SELECT MAX(used_ublk) FROM V$TRANSACTION;