Re: Increased runtime and 4 xids for one insert

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_at_tanelpoder.com> 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).
>
>

--
http://www.freelists.org/webpage/oracle-l