Home > Articles > Data > Oracle

  • Print
  • + Share This
From the author of

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;
  • + Share This
  • 🔖 Save To Your Account