Re: Very slowly progressing INSERT INTO SELECT statement

Ilmar,

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.

Regards
Jonathan Lewis

On Fri, May 15, 2020 at 10:50 AM Ilmar Kerm <ilmar.kerm_at_gmail.com> 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 <jlewisoracle_at_gmail.com>
> 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
>> or UNFORMATTED.
>>
>> 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
>

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