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@xxxxxxxxxxxxx> wrote:
Hi Jonathan,
On Saturday, January 8th, 2022 at 12:39 PM, Jonathan Lewis <
jlewisoracle@xxxxxxxxx> wrote:
Was that a typo for "KGL...""ORA-00600: internal error code, arguments: [GKL-heap-size-exceeded]"
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
