If you think you can fix oracle error "ORA-01555: snapshot too old" just by increasing the rollback segments, think again as reading this article may potentially alter your opinion.
The article explains the inner details about this oracle error, indicates the possible course of action and provides details on how to fix or avoid this error. It also explains with an example scenario that the best solution for ORA-01555 error is tuning application logic rather than adding more rollback segments.
ORA-01555: snapshot too old: rollback segment number string with name "string" too small.
Cause: rollback records needed by a reader for consistent read are overwritten by other writers.
Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments Details: There are number of reasons for the ORA-01555 error. The most common reason is that the rollback segments are too small. Rollback segments are used by the Oracle for reconstructing the read-consistent snapshot of the data. In Oracle during the DML operations for any changes to the data records, a snapshot of the record before the changes were made is copied to a rollback segment.
After transaction completes, its data is not deleted from the rollback segment. It remains there to service the queries and transactions that began executing before it was committed. This may cause problems with long queries because these blocks may be overwritten by other transactions, even though the separate long-running query against those blocks has not completed. So ORA-01555 error may occur whenever a long-running query is executed at the same time as data manipulation transactions.
How to avoid/fix this error :
• Augment the size of all the roll back segments.
• Add more rollback segments.
• Augment the 'OPTIMAL' size of the roll back segments.
• Tune the application to "commit" more frequently such that smaller rollback space is used and this condition can be avoided.
• Follow the Oracle recommended 'proper' solution: schedule long-running queries at times when online transaction processing is at a minimum.
Example:
ORA-01555 Error Scenario: Sample application logic causing ORA-01555 error:
* Prepare a select
* fetchrowArray
* while the row is not null
* update the row
* if nrows mod commitsize then commit
* fetch the next row
* end while
* final commit
This application logic was implemented using Perl, DBD, DBI and oracle. It raised the error as shown below:
DBD::Oracle::st fetchrow_array failed: ORA-01555: snapshot too old: rollback segment number 15 with name "_SYSSMU15$" too small (DBD ERROR: OCIStmtFetch) [for Statement ..]"
Analysis and Resolution:
As you can see from the application logic, the rows selected by the outer select query are being modified by the update statement. ie both are acting on the same table . It can be resolved by setting UNDO_RETENTION appropriately large enough BUT it may not be the best solution.The better solution may be separating out select and updates in to different transactions. After rewriting the application logic by separating data selecti and update activities, the user didn't experience ORA-01555: snapshot too old error any longer.
Oracle Error Information: http://oracle-error.blogspot.com
Article Source: http://EzineArticles.com/?expert=S_Mendu
สมัครสมาชิก:
ส่งความคิดเห็น (Atom)
ไม่มีความคิดเห็น:
แสดงความคิดเห็น