Tanel,
Thinking about your SQL Monitor comment - an earlier posting showed 4
different SQL_EXEC_IDs over the total time period, and thinking about the
way you can select the SQL_EXEC_ID when you report an SQL_ID using SQL
Monitor I think this suggests that this is 4 separate executions rather
than an internal restart - i.e. the application restarting a failed job,
Regards
Jonathan Lewis
On Thu, 11 Apr 2024 at 19:02, Tanel Poder <tanel@xxxxxxxxxxxxxx> wrote:
If you have ASH, you have SQL Monitoring too and if this thing took hours,
Oracle may already have saved a previous SQL Mon report to data dictionary.
-
https://mauro-pagano.com/2015/05/04/historical-sql-monitor-reports-in-12c/
If you find a single saved report from that time, check if the
"Executions" column of the 2nd line from the top (should be LOAD DATA
CONVENTIONAL) shows 4 or just 1 (and double check that it's a serial
insert-select.
If it's 4, it's a DML restart that happens under the hood.
If you see (up to) 4 different SQLMon reports all with a different
SQL_EXEC_START + SQL_EXEC_ID values (and Executions is 1), then it's your
app that restarts the query.
In the latter case, it could be any error (that doesn't get logged to
alert.log) like perhaps running out of UNDO or TEMP tablespace space or any
other error (I don't remember if such errors are logged into alert, like
the ORA-1555 errors).
