Re: Very slowly progressing INSERT INTO SELECT statement

From: Jonathan Lewis <>
Date: Fri, 15 May 2020 12:00:53 +0100
Message-ID: <>


Thanks for the reply.
That might explain why it's labelled as background CPU - it's the sort of little detail that goes on the list of "I really ought to check that some day" - especially when you worry about "Top 10 Foreground events", and the "Time Model" reporting CPU -- does it also go into background or does it change to foreground.

Jonathan Lewis

On Fri, May 15, 2020 at 10:50 AM Ilmar Kerm <> wrote:

> Hi Jonathan,
> Thank you for the response. Quick note about the session - I sent
> statistics from the main session doing the INSERT, it is a job slave - a
> one time DBMS_SCHEDULER job.
> PROGRAM=oracle_at_hostname (J005)
> On Fri, May 15, 2020 at 11:01 AM Jonathan Lewis <>
> wrote:
>> This looks like a case of the ASSM bitmaps getting into a state that
>> confuses your session (and that means the /*+ append */ strategy will
>> bypass the problem since it will handle only bitmaps that are current FREE
>> Things like this aren't supposed to happen in 19.3, but the statistics
>> suggest the session is looking in bitmap blocks for free space, and finding
>> a bltmap that says "block X has space" - so the session visits block X and
>> finds that it can't use it so it goes back to the bitmap and checks another
>> "bit" and goes through the cycle again.
>> I've written a couple of notes about what can make this happen, and one
>> of the notes raised the problem of 16KB blocks with lots of migrated rows
>> and a full ITL - but the problems I've blogged about should have been fixed
>> by 19.3 (unless the fix for the ITL overlooked a detail of the 16KB / ITL
>> problem).
>> I am a little curious that the CPU usage is reported as "background cpu
>> time" - I had assumed that you'd be showing us the session doing the
>> insert. Is this the case, or is 642 really a background process (maybe one
>> of the Wnnn processes).
>> (Tanel Poder and I got into a discussion about ASSM behaviour a couple of
>> days ago - this looks like something on the same theme)
>> Regards
>> Jonathan Lewis
> --
> Ilmar Kerm

Received on Fri May 15 2020 - 13:00:53 CEST

Original text of this message