Re: Very slowly progressing INSERT INTO SELECT statement

Hi

On Fri, May 15, 2020 at 8:27 AM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi Ilmar,
>
> Quote *"Parallel DML is not enabled,"*.
> My first idea: Why on earth? Enable parallel dml and throw in an append
> hint.
> It is not the first time I see the insert step taking most of the time
> when parallel processing gets queued on the QC.
>

Enabling parallel DML and adding APPEND hint for the next execution was also my recommendation and I think developers have implemented it for the next execution.

> Other than that you could query ash for planline 1 and group by event,
> current object.
>
>
On the wait event side, there is nothing to see, fully on CPU. Object level heaviest use are the TARGET table partitions and TARGET table primary key.
From AWR I see that TARGET table partitions are targets for heavy Logical reads (Segments by Logical Reads) - about 1.4B logical reads per hour, comparing it to Segments by DB Blocks changes report that only show about 26M block changes per hour per target table segment. Looks like something "abnormal" is causing session to do huge amounts of logical reads on the target table. My current thinking is around block cleanout area, due to "active txn count during cleanout" and "cleanout - number of ktugct calls" stats.

I copied the snapper output to pastebin for better formatting: https://pastebin.com/Rs37xrh5

-- 
Ilmar Kerm

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