MIME-Version: 1.0
References: <CAEjw_fgV9psqr4UmLzbVAVXLHiQegNp2eA7A4J0FV_fHvMFu3g@mail.gmail.com>
 <495f94ae-3567-1a5c-0813-694ffa65c910@gmail.com> <CAEjw_fgG4OJHmYG+TuYRqqEv5YD7UjMXV0iqw-MctTF-=P9zSA@mail.gmail.com>
 <dfd8c14c-d72b-8f90-d424-9c62a9b8d617@gmail.com>
In-Reply-To: <dfd8c14c-d72b-8f90-d424-9c62a9b8d617@gmail.com>
From: Paul Drake <bdbafh@gmail.com>
Date: Sun, 21 Mar 2021 13:51:43 -0400
Message-ID: <CAPptggXcnT3Vm-1vm7WuYQkxgaA6vYAM5gMs0hREFnHem6AEoQ@mail.gmail.com>
Subject: Re: UNDO Space Error
To: Pap <oracle.developer35@gmail.com>
Cc: Oracle L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="000000000000cfd1b805be0f9ab1"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: bdbafh@gmail.com
--000000000000cfd1b805be0f9ab1
Content-Type: text/plain; charset="UTF-8"

https://imgflip.com/i/52l14s

http://www.nocoug.org/download/2012-11/NoCOUG_201211_Tim_Gorman_Data_Warehouse_Partitioning.pdf

You want to maximize the amount of work not being performed.

Do you really care about how much undo is being generated? No. You want the
data loaded. Quickly.

Stop focusing on making more room available for undo and minimize
generating undo.

The volume of redo being generated is likely causing other issues as well.

Apologies for replying to the wrong thread snippet.

Paul

On Sun, Mar 21, 2021, 12:49 Mladen Gogala <gogala.mladen@gmail.com> wrote:

> This can be replaced by a PL/SQL selecting into an array, doing bulk
> inserts and committing regularly. PL/SQL will not perform as well as the
> SQL below but will probably perform good enough. There is a rather
> infamous optimization of commit in a PL/SQL loop which will mitigate the
> impact of a gazillion commits. As for the conjecture about indexes, it
> is most likely correct. Direct insert uses the blocks after the HWM
> which are empty  so there is not much content in the data blocks to
> write to the undo space. The content is probably coming from the index
> blocks.
>
> On 3/21/21 1:16 AM, Pap wrote:
> >
> > INSERT /*+ append parallel(16) nologging */ INTO USER1.TAB (c1, c2,
> > c3, c4... c11) SELECT...;
> >
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

<div dir=3D"auto"><div dir=3D"auto"><a href=3D"https://imgflip.com/i/52l14s=
" rel=3D"noreferrer noreferrer" target=3D"_blank">https://imgflip.com/i/52l=
14s</a><div dir=3D"auto"><br></div><div dir=3D"auto"><a href=3D"http://www.=
nocoug.org/download/2012-11/NoCOUG_201211_Tim_Gorman_Data_Warehouse_Partiti=
oning.pdf" target=3D"_blank" rel=3D"noreferrer">http://www.nocoug.org/downl=
oad/2012-11/NoCOUG_201211_Tim_Gorman_Data_Warehouse_Partitioning.pdf</a><br=
></div><div dir=3D"auto"><br></div><div dir=3D"auto">You want to maximize t=
he amount of work not being performed.</div><div dir=3D"auto"><br></div><di=
v dir=3D"auto">Do you really care about how much undo is being generated? N=
o. You want the data loaded. Quickly.</div><div dir=3D"auto"><br></div><div=
 dir=3D"auto">Stop focusing on making more room available for undo and mini=
mize generating undo.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Th=
e volume of redo being generated is likely causing other issues as well.</d=
iv><div dir=3D"auto"><br></div><div dir=3D"auto">Apologies for replying to =
the wrong thread snippet.</div><div dir=3D"auto"><br></div><div dir=3D"auto=
">Paul</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"=
gmail_attr">On Sun, Mar 21, 2021, 12:49 Mladen Gogala &lt;<a href=3D"mailto=
:gogala.mladen@gmail.com" rel=3D"noreferrer noreferrer" target=3D"_blank">g=
ogala.mladen@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1e=
x">This can be replaced by a PL/SQL selecting into an array, doing bulk <br=
>
inserts and committing regularly. PL/SQL will not perform as well as the <b=
r>
SQL below but will probably perform good enough. There is a rather <br>
infamous optimization of commit in a PL/SQL loop which will mitigate the <b=
r>
impact of a gazillion commits. As for the conjecture about indexes, it <br>
is most likely correct. Direct insert uses the blocks after the HWM <br>
which are empty=C2=A0 so there is not much content in the data blocks to <b=
r>
write to the undo space. The content is probably coming from the index <br>
blocks.<br>
<br>
On 3/21/21 1:16 AM, Pap wrote:<br>
&gt;<br>
&gt; INSERT /*+ append parallel(16) nologging */ INTO USER1.TAB (c1, c2, <b=
r>
&gt; c3, c4... c11) SELECT...;<br>
&gt;<br>
-- <br>
Mladen Gogala<br>
Database Consultant<br>
Tel: (347) 321-1217<br>
<a href=3D"https://dbwhisperer.wordpress.com" rel=3D"noreferrer noreferrer =
noreferrer noreferrer" target=3D"_blank">https://dbwhisperer.wordpress.com<=
/a><br>
<br>
--<br>
<a href=3D"http://www.freelists.org/webpage/oracle-l" rel=3D"noreferrer nor=
eferrer noreferrer noreferrer" target=3D"_blank">http://www.freelists.org/w=
ebpage/oracle-l</a><br>
<br>
<br>
</blockquote></div></div>

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