Re: Very slowly progressing INSERT INTO SELECT statement

From: Ilmar Kerm <ilmar.kerm_at_gmail.com>
Date: Fri, 15 May 2020 11:13:46 +0200
Message-ID: <CAKnHwtftkP8YWbeL-VTLaww5aA41_F1pXqeHfdQvSOHX+z8TXQ_at_mail.gmail.com>



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
Received on Fri May 15 2020 - 11:13:46 CEST

Original text of this message