Troubleshooting ORA-01555

A couple of days ago the app batch tasks that resides in product oracle 19.11 with the architecture of primary and secondary ran failed and subsequently reported a level 1 warning for application at 09:03, I went to log into that oracle database to look into it immediately when the project group sent that warning message to me.

Based on the advice of the app developer I found out the log file recording app batch tasks result then checked the last some lines of log file, it has expected to show ORA-01555 rollback segment “_SYS_XXXXXXX” too small for the PL/SQL package.procedure name. It’s difficult to make sure what stuff there has in those procedure and I have to ask the developer to check that procedure so that he is able to find some clue. Waiting for a while he checked a sql (due to the security reason I don’t put it on here) and at the same time I’ve also found that sql (including sql id, sql text and the error ORA-01555) in oracle alert log.

Next I went to check the history execution situation of that sql, OMG, it has produced two different execution plans becaus it has shown 2 number of plan_hash_value(s) and the current plan_hash_value has the more elapsed time (1800 seconds) but the previous plan_hash_value has just shorter elapsed time (200 seconds), obviously, the good execution plan used the three indexed and the bad one used only one index. I talked the developer whether he can rewrite the sql, he said it’s really hard for him to rewrite it, if so, I have to bind the good execution to the sql by sqlprofile approach. I am able to discover the sql profile of sql_id of the sql from dba_sql_profiles when I finished binding sql execution plan.

Furthermore, I’ve also checked the used ratio of the historical UNDO tablespace, it’s always 95% – 99% at 8:00-11:00 every day but other time periods it’s only 10% – 30%. By the way, the used ratio of UNDO tablespace has contained the sum(bytes) for the status of undo is in ACTIVE, UNEXPIRED. Unfortunately there has also occupied plenty of unexpired usage (not much too usage in active) in UNDO tablespace when I observed the used ratio of UNDO at that moment. If the sql stuff doesn’t exist in the unexpired of undo and oracle goes to fetch them as possible as it can now it will report the ORA-01555. Afterwards I’ve doubled the total size of undo tablespace.

The parameter undo_retention is also a considerable factor reflecting undo entry. Therefore, I’ve also adjusted it from the original 3600s to the 5400s.

That’s all stuff I shared with you, happy Sunday!