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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
OK so the three things you need to look at to see if you need to increase your undo are:
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