Re: _online_pmo_global_index_method

The parameter is available in 21.3 with a default value of CLEANUP ONLY. I tried altering it at the session level using my usual lazy trick:

SQL> alter session set "_online_pmo_global_index_method" = 'fred'; ERROR:
ORA-00096: invalid value fred for parameter _online_pmo_global_index_method, must be from among CLEANUP, CLEANUP ONLY, DEFER So that suggests it should be possible to change it, and gives you the three possible values. You can't do anything in the parameter about parallelism. For some notes on cleanup vs. cleanup only I wrote something about the costs of dropping partitions and global index maintenance a little while ago. (The one on exchange and/or move which have to delete and insert is still in draft).
https://jonathanlewis.wordpress.com/2022/08/05/drop-partition/#background it may give you some clues.

Regards
Jonathan Lewis

On Mon, 6 Feb 2023 at 15:42, Dominic Brooks <dombrooks_at_hotmail.com> wrote:

> Does anyone have any experience of using parameter
> _online_pmo_global_index_method?
>
>
>
> I’ve got some partition maintenance operations (with update indexes) which
> are going crazy slow since I added online and parallel.
>
>
>
> One bad example of is from 40 minutes to 16 hours.
>
> Driving sessions are all waiting on PX Deq: Index Merge Reply. Slaves are
> mostly on CPU or doing single block IO.
>
>
>
> According to bug *29810012 - Performance of "ALTER TABLE MOVE PARTITION
> ONLINE" May Be Slow When All Partitions Are Moved*, this param was first
> included in 20.1.0 and 19.13.0.0 DBRU OCT 2021.
>
>
>
> So I wanted to do some tests in a lower environment with this parameter.
>
> I’m on 19.16 and assumed it should be available in that release
>
>
>
> But I certainly can’t set it at ALTER SESSION level – ORA: 02248: invalid
> option for ALTER SESSION.
>
>
>
> I don’t have DBA access but the fact that an attempt to set it at system
> level comes back with an ORA-02065: illegal option for ALTER SYSTEM rather
> than an ORA-01031: insufficient privileges suggest that it is my assumption
> - that this would be in 19.16 – that is wrong.
>
>
>
> Cheers,
>
> Dominic
>
>
>
>
>
>
>

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