Re: Question on global index maintenance

Andy,
>> "ORA-00600: internal error code, arguments: [GKL-heap-size-exceeded]"
Was that a typo for "KGL..."

Parse error or DDL error relating to objects with many partitions? Maybe something that was happening because the clean-ups didn't end and the global index ended up with a large number of orphaned partitions contributing to the partition count for the index. Error possibly fixed by 12.2.0.1, though the underlying nuisance of not being able to clean out the orphans in a timely fashion is another issue.

I wonder if Oracle has addressed the granularity issue since you first encountered problems with cleaning out orphans. The current procedure in dbms_part only allows you to supply a table reference, so you can't clean each index in turn if you have many on the same table - I could envisage Oracle enhancing the process to specify one index at a time, and then (for globally partitioned indexes) one partition at a time; and even allowing 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.)

I have to admit that I haven't played with the feature since it came out in 12c, so my memory of what's possible and what the locking andn concurrency threats might be is a little vague.

Regards
Jonathan Lewis

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

> Hi,
>
> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
> On Saturday, January 8th, 2022 at 1:52 AM, Lok P <loknath.73_at_gmail.com>
> wrote:
>
> When you said "*And worthy of note (though kind of obvious), if the clean
> jobs aren't able to complete then those segments are continuously growing*.",
> Do you mean that if the delayed index maintenance/cleanup job is not
> getting completed but still we keep on dropping partitions in subsequent
> days. This can cause the global index to grow substantially and then it can
> impact the performance of the queries those are using this index access
> path in them?
>
>
> Yes, the segments will grow and because the space that would otherwise be
> available after the orphaned entries are cleaned out will stick around. And
> even after the clean, the index doesn't get smaller, just the space is now
> available to be used by the index. That said, I saw virtually no impact to
> query performance on these _bloated_ indexes. However, there were cases
> when some indexes would grow beyond a threshold and would throw "ORA-00600:
> internal error code, arguments: [GKL-heap-size-exceeded]", so we had to
> build an elaborate system which would monitor size (from
> gv$db_object_cache), and when an index exceeded a certain size, then the
> modules would pause activity while the index was either cleaned or rebuilt
> (I think we ended up just doing a rebuild since it was ultimately faster).
>
> As you mentioned , there are cases in which this index cleanup job can
> block the other sessions , which looks scary on a 24/7 system. And even
> for unique indexes we are being asked/forced to have the partition key
> included in the index keys so as to make it unique and local. So ,overall
> It means we are no better even on the higher/recent versions of Oracle for
> considering creation of global indexes on partitioned tables. Correct me if
> I'm wrong.
>
>
> I saw that Jonathan had a great reply about the partition key bit and will
> let this branch of the discussion follow there. Though I do understand your
> frustration, it's very clear that Oracle doesn't want us to live on 11g
> forever :)
>
> Andy K
>

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