Re: Question on global index maintenance

Andy,

Thanks for the giving us the details.
It's very helpful to hear some details of the problems and side-effects that can appear in full-scale production systems.

Regards
Jonathan Lewis

On Sat, 8 Jan 2022 at 21:14, Andy Klock <andy_at_klockmail.com> wrote:

> Hi Jonathan,
>
> On Saturday, January 8th, 2022 at 12:39 PM, Jonathan Lewis <
> jlewisoracle_at_gmail.com> wrote:
>
> >> "ORA-00600: internal error code, arguments: [GKL-heap-size-exceeded]"
> Was that a typo for "KGL..."
>
>
> Yes, nice catch. I just copied and pasted from my notes and didn't even
> notice that. In my editor I do "C-c t" a lot, but depending on where my
> cursor is, I sometimes end up typing "C-t" which transposes characters.
> This is handy when you need to do this and embarrassing when you don't.
>
> Parse error or DDL error relating to objects with many partitions?
>
>
> The ORA-600 were parse errors when a particular report would run that
> required that index. It's possible that they no longer happen since they
> did eventually upgrade to 18c.
>
> partial cleaning to stop - a bit like an "alter index coalesce" (Have you
> done any experiments with "alter index coalesce cleanup" to see if uses one
> transaction or many, and if it can be aborted without rolling back and
> without locking the table.)
>
>
> Yes, and the coalesce cleanup commands run with a "parallel 32" for each
> statement (about 30 indexes need to be handled this way). But it is true,
> if a transaction is killed before completing, Oracle doesn't roll those
> back so it will start where it left off. The default maintenance job that
> the OP referenced earlier behaves similarly. Meaning, by the third or
> fourth day of running the maintenance job, some of the _huge_ indexes would
> eventually complete and move on to the next one, but because of the shear
> number of indexes that needed to be cleaned Oracle was never able to keep
> up with the job alone. One of the 12.1 PSUs included functionality to
> configure parallelism on that procedure, but it didn't help so we wrote our
> own, which Support conceded was the correct approach for our situation.
>
> Andy K
>
>

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