UNDO retention causing excessive I/O ?
652312Jul 28 2008 — edited Jul 29 2008New DBA here.
Quick Summary:
We have an oracle 10gR2 DB on AIX 5.3. OS runs from the box, Database runs from a san. Aside from the DB, there is a web based application running on the box, which uses the DB.
System is dedicated to this application.
We have a consulting company that is making suggestions, and I am not sure about some of them.
When we set up the software, the vendor told us to set up the database with undo retention to 10800. So we did.
Our consulting group is telling me that Undo I/O represents 34% of the total database I/O, and that I should reduce the retention time to 1080. *(they also tell me that the longest running query is 100s; I don't quite understand how this is relevent).
Why would reducing the UNDO retention lenght reduce the UNDO I/O ?