‐‐‐‐‐‐‐ 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
Received on Sat Jan 08 2022 - 16:50:03 CET
