Re: Very slowly progressing INSERT INTO SELECT statement

Thanks for the response. That's useful information. (And it means I don't have to create a model to satisfy my curiosity ;)

Regards
Jonathan Lewis

On Sat, May 16, 2020 at 5:51 PM OracleML <myoracleml_at_gmail.com> wrote:

> There is a bug for the issue of foreground process cpu reported as
> background cpu. It is actually fixed recently. I will find the bug when I
> get to my desk.
>
> -Uday
>
> On May 15, 2020, at 6:03 AM, Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
> 
>
> 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