Re: Increased runtime and 4 xids for one insert

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_at_gmail.com> 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_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