Hi we are using version 11.2.0.4.0 of oracle. We have a query which fails with snapshot too old error after ~30minutes. As per my understanding the chances of this error would be there , if there has been any DML operation happening on the base table during the SELECT query. As we have verified we don't perform any DML operation during the SELECT query execution. INSERT into the base table completes before this SELECT query triggers, so there is dependency set between INSERT and SELECT into this base table. Same thing is noticed from Dba_hist_active_sess_history/gv$active_session_history also. So as per our understanding, in ideal scenario the SELECT query should not visit any UNDO record from the base table during its full execution, as there is no DML happens on it during this period. So my question is, if there any other scenario possible which can cause the SELECT query to visit the UNDO records and failure with Ora-01555? As this failure happens occasionally , is there any way we can do some instrumentation/tracing to capture the DML(which we are unaware of), if any happening during the SELECT query execution causing this failure?
We have below UNDO setup:
We have AUTO undo management and having UNDO retention ~15minutes and having all the data files for the UNDO tablespace set as "AUTO EXTEND ON". Size of current UNDO tablespace being ~60GB. We are in process of making the data files AUTO EXTEND OFF.
Update- Additionally i see from the real time monitoring, the SELECT query actually completes in ~5minutes, it just lies there for ~25minutes on wait event "sql*net message from client" as the rows fetched from this query are very large and the fetch size used by the client is lesser. So is it correct to assume that in this case, the chances of "Snapshot too old error" should be minimum, as from database side the query is completing faster?