Yep, I may have missed some of the info when skimming through the mail
thread.
I guess if the application code did not clearly re-run the statement, could
be something in between, like some HA thing that restarts a query on
failover - or the SQL Error Mitigation feature in 23c (don't know if or how
it works)... but probably easier to just check from the application logs
(or a SQL trace output that would need to be set up for the next time the
job runs).
For anyone interested, here's an old video with the write consistency demo
(of an UPDATE statement) that shows Starts/Executions = 3, while the
SQL_EXEC_ID and SQL_EXEC_START remain the same (the snapshot SCN increases
at every retry of course, but that's not visible in SQL Mon).
- https://youtu.be/jSvk0lxPjzY
Tanel Poder
tanelpoder.com
On Fri, Apr 12, 2024 at 3:27 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
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).
