Undo Management

  1. Undo What is that

  2. Configuring Undo

  3. Sizing Undo Tablespaces

  4. Undo Advisor

    1. .

    2. Undo, why undo ?

    3. We require undo for following

    4. Read Consistency : Read consistency gives your query the output

    5. as it was at that point in time

    6. Example : Imagine you issued select query which takes 4-5 min to

    7. execute, and on the second minute someone modified the data by

    8. issuing update command, inspite of this the first query's output is

    9. unaffected. That is because the before modified image is stored

    10. in the undo

    11. Rollback Transaction : To undo any modified data Example suppose

    12. you have modified any data and then you change mind not to modify

    13. the data, then you could undo that.

    14. When a rollback statement is issued, undo records are used to undo changes that were made to the

    15. database by uncommited transaction.

    16. Recover Database : During recovery undo records are used to undo

    17. any uncommited changes applied from the redo logs to the datafiles.

    18. Flashback Query : Here you could get the image of the database as

    19. it was before some time. say 15-20 minutes if undo is configured properly

    20. Configuring Undo

    21. We have three imp undo parameter for configuration

    22. undo_management = auto

    23. undo_tablespace = <undo tablespace name>

    24. undo_retention = <time in seconds>

    25. Undo Management : Setting it to auto lets oracle manage the undo

    26. Undo Tablespace : Undo tablespace which will be active.

    27. Undo retention : Time in seconds til which oracle will store the undo

    28. information in tablespace.

    29. Sizing Undo Tablespaces

    30. Oracle provides tool v$undostat, it stores undo block generation data,

    31. which are taken at 10 minutes interval. In the v$undostat table you

    32. would find column undoblks which is in bytes.

    33. Two parameter vital for preventing errors related to undo are

    34. 1) undo retention

    35. 2) Undo tablespace size

    36. SQL>select max(maxquerylen) from v$undostat;

    37. above query gives us the undo retention, now we have to calculate the

    38. size of undo

    39. For getting the undo size we again query the v$undostat, which

    40. store the undo blocks generated in 10 minutes interval

    41. Note : v$undostat keeps 4 days of information

    42. Now,

    43. undo size = transaction rate * block size * undo retention

    44. select max(undoblks) from v$undostat;

    45. transaction rate = [select max(undoblks) from v$undostat;] / 600

    46. Now undo size = transaction rate * block size * undo retention

    47. Monitor Undo status

    48. select status , sum(blocks)*8192/1024/1024/1024 GB from dba_undo_extents group by status;

    49. Additional Details

    50. Each transaction is assigned to only one undo segment,however one undo segment can be

    51. assigned to multiple transaction at a given point of time, to check which transaction have

    52. which undo segments assigned query the v$transaction view.

    53. Undo segments expand and contract automatically as needed and act as circular storage

    54. buffer for transactions

    55. Transaction fills extents in its undo segment until a transaction is completed or all space is

    56. consumed.

    57. When an extend fills up and more space is needed, the transaction will acquire more space

    58. from next extent in the segment.

    59. When all extent in the undo segment are consumed, the transaction overwrites undo data

    60. which is no longer needed. Undo tablespace contain undo segments, an instance can

    61. have only one active undo tablespace at a time

    62. Undo segments are owned by SYS and they have segment type "TYPE 2 UNDO"

    63. Undo tablespace are specialized, permanent tablespace with automatic

    64. extent allocation.

    65. There are only two this one needs to know to monitor undo

    66. 1) Undo space

    67. 2) Snapshot to old error

    68. UNDO Space : when huge transaction takes place in the database, this requires huge space

    69. in the undo tablespace to store the original data

    70. insert operation requires very little undo space

    71. delete operation requies huge undo space

    72. when undo tablespace runs out of space, the oracle server raises ORA-01650

    73. unable to extend rollback segment

    74. Snapshot to old error : Here when a query tries to access data that no longer exists in the

    75. undo tablespace, this happens when a query takes long time to execute, here we get ORA-1555

    76. snapshot too old error

    77. Undo management.

    78. Two Types Of Undo management

    79. Automatic : Here the original data is stored in undo segment

    80. Oracle server determines the time period for which the undo

    81. data is retained in undo segments, data is stored in the undo

    82. segments even after the transaction is complete (called unexpired data)

    83. Undo retention : Amount of time for which the commited undo data should be retained in the

    84. undo tablespace, after which it will expire or will be overwritten. In Automatic undo management

    85. undo is retained till it expires, however if an active transaction required more space the

    86. commited but unexpired data is overwritten. If undo retention is set to 0, automatic undo retention

    87. tuning is enabled, this retains undo information as required to support longest running query

    88. Manual : The segment where the original data is stored is called as rollback segment

    89. Undo data falls in three categories

    90. 1) Uncommited undo info for active transaction, used for rollback or recovering failed transaction,

    91. this is never overwritten

    92. 2) Commited undo : These is retained to meed the retention period

    93. 3) Expired undo : these is not required to support an active transaction and is overwritten when

    94. an active transaction needs space and no free is available When space is required by active

    95. transaction and there is no free space Commited undo data is overwritten instead of failing

    96. the transaction. However if the retention_guarantee is set the, these would not happen and lets

    97. transaction to fail for lack of undo space.