How often I did meet ORA-01555: snapshot too old: rollback segment too small ?
I cannot count. On one of the database I am currently administrating it is about once a day.
Back to Oracle 7 and 8, the solution was usually to size the rollback segments properly. Using big rollback segments for big jobs, and many small segments for OLTP.
Oracle 9i introduced the automatic undo management. When you meet ORA-01555, just increase the UNDO tablespace (or/and set it autoextensible). Well, how did I read complains from users which used to have tiny rollback segments, and did not understand why the undo tablespace grows to gigabytes in 9i. There is also a parameter, called UNDO_RETENTION, which prevents Oracle from rewritting old extents before it really needs them.
In 10g, you can also force oracle to keep old undo extents up to the UNDO_RETENTION by using
alter tablespace UNDOTBS1 retention guarantee;
What I learned today is the TUNED_UNDORETENTION mechanism. By setting UNDO_RETENTION to 0, you enable auto tuned retention. That is, as long as your tablespace can autoextend, you will less probably get an ORA-01555. A recommended lecture is Metalink Note 240746.1
Back to my 9i database with ORA-01555, I will try to increase undo_retention to something bigger than
SQL> select max(maxquerylen) from v$undostat; MAX(MAXQUERYLEN) ---------------- 105047
And see how long I will survive until the next ORA-01555…