MIME-Version: 1.0
References: <4A23E219-1A66-4AFD-8A05-AF56CEBE16A8@williamrobertson.net>
In-Reply-To: <4A23E219-1A66-4AFD-8A05-AF56CEBE16A8@williamrobertson.net>
From: Eriovaldo Andrietta <ecandrietta@gmail.com>
Date: Sat, 8 Aug 2020 06:45:58 -0300
Message-ID: <CAJdDhaPWxBdj=uB94ucYtdwb8knDTci5kTHbGHP4TSrZgu599Q@mail.gmail.com>
Subject: Re: INSERT statement restarts internally
To: William Robertson <william@williamrobertson.net>
Cc: oracle-l <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="0000000000002dfc3705ac5a9754"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: ecandrietta@gmail.com
--0000000000002dfc3705ac5a9754
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi William,

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

Best regards
Eriovaldo


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

> Hi all,
>
> I=E2=80=99m 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=E2=80=99s not blocked, the plan hasn=E2=80=99t c=
hanged and seems
> fine, all row estimates are good, there is no more data than usual, backu=
ps
> aren=E2=80=99t running (though I can=E2=80=99t 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, eve=
n
> 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=E2=80=99m 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 =3D false. We fixed
> that by changing it to true and the issue seemed to go away. This time
> though, this stats job wasn=E2=80=99t running and neither was anything el=
se I could
> see that would have affected the same tables and partitions - all our lar=
ge
> 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=E2=80=99s happened before but sporad=
ically.
>
> I=E2=80=99m a bit stumped about what to do about it. Would this be logged=
 anywhere
> (similar to a deadlock report)? I couldn=E2=80=99t see anything likely in
> v$diag_trace_file_contents but maybe I=E2=80=99m looking for the wrong th=
ing. 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 a=
n
> internal failure? This is 12.2.0.1.
>
> Thanks,
>
> William--
> http://www.freelists.org/webpage/oracle-l
>
>
>

--0000000000002dfc3705ac5a9754
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">Hi William,=C2=A0<div dir=3D"auto"><br><div dir=3D"auto">=
Consider to check :=C2=A0</div><div dir=3D"auto">1) initrans of the table I=
f you are doing distributed transactions.</div><div dir=3D"auto">2) the cac=
h=C3=AA of the sequence If you are using one.</div><div dir=3D"auto"><br></=
div><div dir=3D"auto">Best regards</div><div dir=3D"auto">Eriovaldo</div><d=
iv dir=3D"auto"><br></div><div dir=3D"auto"><br></div><div dir=3D"auto">=C2=
=A0</div><div dir=3D"auto"><br></div></div></div><br><div class=3D"gmail_qu=
ote"><div dir=3D"ltr" class=3D"gmail_attr">Em ter, 4 de ago de 2020 19:20, =
William Robertson &lt;<a href=3D"mailto:william@williamrobertson.net">willi=
am@williamrobertson.net</a>&gt; escreveu:<br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-lef=
t:1ex">Hi all,<br>
<br>
I=E2=80=99m 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=E2=80=99s not blocked, the plan hasn=E2=80=
=99t changed and seems fine, all row estimates are good, there is no more d=
ata than usual, backups aren=E2=80=99t running (though I can=E2=80=99t rule=
 out other background resource hogs, as I have somewhat limited access). Th=
en 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_st=
arts for the sid/serial#/sql_id, even though the procedure only calls it on=
ce. 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 checke=
d whether the session was rolling back, which I=E2=80=99m guessing it was.)=
<br>
<br>
The last time we saw this, it coincided with a job that gathered stats on a=
ll partitions marked as stale, passing no_invalidate =3D false. We fixed th=
at by changing it to true and the issue seemed to go away. This time though=
, this stats job wasn=E2=80=99t running and neither was anything else I cou=
ld see that would have affected the same tables and partitions - all our la=
rge tables are list-partitioned by business date and many are subpartitione=
d by business line etc, partly to isolate batch processes. Looking at dba_a=
ctive_sess_history I can see it=E2=80=99s happened before but sporadically.=
<br>
<br>
I=E2=80=99m a bit stumped about what to do about it. Would this be logged a=
nywhere (similar to a deadlock report)? I couldn=E2=80=99t see anything lik=
ely in v$diag_trace_file_contents but maybe I=E2=80=99m looking for the wro=
ng thing. Is it the case that another session must have hard-invalidated th=
e cursor, and I just have to find the smoking gun, or is there some other s=
cenario like an internal failure? This is 12.2.0.1.<br>
<br>
Thanks,<br>
<br>
William--<br>
<a href=3D"http://www.freelists.org/webpage/oracle-l" rel=3D"noreferrer nor=
eferrer" target=3D"_blank">http://www.freelists.org/webpage/oracle-l</a><br=
>
<br>
<br>
</blockquote></div>

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