Re: Question on global index maintenance

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