Re: INSERT statement restarts internally

Hi William,

Consider to check :
1) initrans of the table If you are doing distributed transactions. 2) the cachê of the sequence If you are using one.

Best regards
Eriovaldo

Em ter, 4 de ago de 2020 19:20, William Robertson < william_at_williamrobertson.net> escreveu:

> Hi all,
>
> I’m trying to get to the bottom of an issue where a SQL statement (in this
> case an insert) that normally runs in 15 minutes suddenly takes two hours.
> Checking the session, it’s not blocked, the plan hasn’t changed and seems
> fine, all row estimates are good, there is no more data than usual, backups
> aren’t running (though I can’t rule out other background resource hogs, as
> I have somewhat limited access). Then I notice that the start time shown in
> SQL Monitor has changed, and going back to the session I see there have
> been six sql_exec_ids and sql_exec_starts for the sid/serial#/sql_id, even
> though the procedure only calls it once. Some of them are a couple of
> minutes apart, some are after 40 minutes. (With hindsight I should have
> looked at v$sql.object_status and also checked whether the session was
> rolling back, which I’m guessing it was.)
>
> The last time we saw this, it coincided with a job that gathered stats on
> all partitions marked as stale, passing no_invalidate = false. We fixed
> that by changing it to true and the issue seemed to go away. This time
> though, this stats job wasn’t running and neither was anything else I could
> see that would have affected the same tables and partitions - all our large
> tables are list-partitioned by business date and many are subpartitioned by
> business line etc, partly to isolate batch processes. Looking at
> dba_active_sess_history I can see it’s happened before but sporadically.
>
> I’m a bit stumped about what to do about it. Would this be logged anywhere
> (similar to a deadlock report)? I couldn’t see anything likely in
> v$diag_trace_file_contents but maybe I’m looking for the wrong thing. Is it
> the case that another session must have hard-invalidated the cursor, and I
> just have to find the smoking gun, or is there some other scenario like an
> internal failure? This is 12.2.0.1.
>
> Thanks,
>
> William--
> http://www.freelists.org/webpage/oracle-l
>
>
>

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