Oracle: ORA-01555: snapshot too old Tuning

Product: Oracle Databse
Version: 9.0.0 - 18.0

Since Oracle Database 9i, many DBA have been keeping UNDO_MANAGEMENT=AUTO setting, and developers/support often encountered following UNDO related error on long running SQL

Oracle <ORCL> error message for operation <OCIStmtExecute>: <ORA-12801: error signaled in parallel query server P07G
ORA-01555: snapshot too old: rollback segment number 271 with name "_SYSSMU271_3009956035$" too small>.

If DBA don't study Oracle documentation on "Automatic Tuning of Undo Retention" but simply search Google/Internet for answer, then they often comes to following incorrect workaround:

1. Increase UNDO_RENTENTION time - no change in the long running SQL
2. Modify code to split the long running SQL to have several shorter running SQL, e.g. store into intermedia table, then select from the intermedia table
3. Asks developer to tune the long running SQL to run faster, e.g. create index, don't select unnecessary columns, filter out unrequired data (more WHERE criteria)
4. Don't insert/update/delete on the table that the long running SQL is SELECTing

So what does DBA missed in above analysis is he/she only focus in ORA-01555 error without considering the rest of the message.  Following are the things that DBA missed:

1. Parallel query server is enabled
2. Above PQ number is P07G with starting number as P000
3. Number of concurrent running SQL that that point of time, without considering Parallel query server (excludes parallel query coordination and slave)
4. Size of the UNDO tablespace, which could be few TB in size, and bigger than entire DB size
5. UNDO tablespace's dbf files are not in auto extend mode
6. Even if UNDO dbf files are in auto extend mode, it hit max dbf file size

As the result, tuning UNDO_RETENTION or even increase the size of UNDO dbf to 300GB larger doesn't resolve the issue.

The resolution is in fact totally not related to modify UNDO_RETENTION.

Post your comment if you want to know the answer, as I want to know how many visitors are actually reading my blog post