Extract from AWR Report

How can I use undo statistics in oracle AWR report for tuning Oracle? which indicators are important to identify that size of undo segments needs to be increased?

  • Two questions before I give you an answer: 1. Was this AWR taken during a period of peak workload 2. What is the value of your undo retention guarantee? – Andrew Brennan Mar 14 '14 at 10:41
  • @AndrewBrennan Value of Undo_retention is 21600 from init.ora parameters in AWR. AWR report corresponds to 7 minutes of peak activity in a performance testing scenario. I am not concerned about this specific scenario but am looking for tips on how to approach these stats to use them effectively for tuning. – Deven Mar 14 '14 at 12:18

OK so the three things you need to look at to see if you need to increase your undo are:

  1. The number of failures occuring - the value of STO/OOS
  2. The value of Min/Max TR in comparison to your Undo Retention
  3. The number of undo blocks per second * your block size * your retention guarantee, in comparison to the size of your undo tablespace

If there are any non-zero values in STO/OOS then you have a problem. The values of uS/uR/... will tell you what kind of problems are occurring. You will need to check the alert log for errors to figure out what's going wrong.

If Max TR is close to Undo Retention, let's say within 70%, then you should probably increase the retention to prevent ORA-1555 errors.

If Num Undo Blocks * Block Size is close to the size of your Undo Tbs then you should probably increase the size of the undo to avoid ORA-1652 errors.

But really you should be using the Undo Advisor to choose a good size for the tablespace: http://docs.oracle.com/cd/E11882_01/server.112/e25494/undo.htm#ADMIN11466

So in the example given, you are not having any active problems with undo size. Your undo retention is way above the length of the longest query so that's fine. Your undo tablespace should be a minimum of (UNDO_RETENTION * 12,333 * DB_BLOCK_SIZE ) / Length Of AWR Window In Seconds.

This article is ancient but it explains it well enough: http://www.orafaq.com/node/1888

| improve this answer | |
  • just one comment. Oracle uses 2 algorithms for allocating space in UNDO. One for fixed sized ones the other for auto-extensible ones. When using fixed size UNDO then Undo Retention parameter is ignored. – ibre5041 Mar 14 '14 at 14:51

Not the answer you're looking for? Browse other questions tagged or ask your own question.