My users are launching an ugly UPDATE with linked sub-selects, an online query coexisting in a very agressive online environment.
In the trace, I saw an estimation of the UNDO retention needed, which is roughly 10x our current retention for UNDO.
As a newbie DBA, my instinct tells me optimizing the query might help and resolve the UPDATE with no ORA-01555.
Am I completely wrong?
Thank you so much!
Oracle database [uniquely] runs all queries AS OF the timestamp when the query / transaction started. To achieve this, it effectively "flashes back" every Data Block it uses, in memory, to that timestamp, using the available Undo segments. If there isn't enough in the Undo buffers to achieve that, Bang! You get error ORA-01555.
So this is a query that's using lots of data and taking a long time to do it, so that it needs lots of Undo to get all that data "back" to a consistent timestamp.
Ouch!
My users are launching an ugly UPDATE with linked sub-selects ...
Just to be clear; are your Users logged into the Production database and executing update queries? If so, you have far, far bigger problems, waiting in the wings.
Hopefully, this is something that they're running through an Application.
If so, then this issue should have been found during Testing of this application and its query, specifically Performance Testing.
If your Developers aren't doing this, they d***ed well should be.
... my instinct tells me optimizing the query might help ...
Good Instinct!
Tune your Workload, not your Database.
