Re: Very slowly progressing INSERT INTO SELECT statement
- From: Ilmar Kerm <ilmar.kerm@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 15 May 2020 10:10:29 +0200
Hi
On Fri, May 15, 2020 at 8:27 AM Lothar Flatz <l.flatz@xxxxxxxxxx> 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
Other related posts: