A customer has reported this error more then once in the last three months. What I'm trying to do is figuring out how to identify the procedures within the application that contributed the most in changing database blocks when this error is raised (or eventually other activities on the database from tools not strictly related to the application).
For the sake of clarity, before acting on UNDO size and UNDO RETENTION parameters, I'd like to investigate the application in order to identify bad practices that can raise the probability of hitting this error.
What I know for sure is:
- the statement that errored is not the root cause, it's just a victime: here a bit of SQL tuning may make it run faster and avoid the issue
- frequent commits (sort of loop like approach) made by concurrent session on tha same tables used by the errored statement, may contribute to hit this problem
How can I investigate the Oracle database in order to identify the procedures at the point #2 ? What Oracle dynamic views or diagnostic views can help me in this quest?
We're facing this porblem on Oracle 12.1 with Diagnostic and Tuning Pack