Re: Question on global index maintenance
- From: Andy Klock <andy@xxxxxxxxxxxxx>
- To: jlewisoracle@xxxxxxxxx
- Date: Sat, 08 Jan 2022 21:14:15 +0000
Hi Jonathan,
On Saturday, January 8th, 2022 at 12:39 PM, Jonathan Lewis
<jlewisoracle@xxxxxxxxx> 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
Other related posts:
