Re: _online_pmo_global_index_method

Thanks Jonathan.
Yes I read that article - it’s excellent, no surprise.

BTW I believe when you leave it to defer you can change the parallelism on the built-in job via a job attribute.

So as I’m 19.16 I’m clearly misinterpreting what the note says about when the param is available.

Cheers
Dominic

Sent from my iPhone

On 6 Feb 2023, at 17:11, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:



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<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fjonathanlewis.wordpress.com%2F2022%2F08%2F05%2Fdrop-partition%2F%23background&data=05%7C01%7C%7Cba0671a1db0648eb25c408db08652b2d%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638113002911067478%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=091aDhS85Jvppj9lkpfaME7afE1DKH%2BQhBYGUf%2BOaak%3D&reserved=0> it may give you some clues.

Regards
Jonathan Lewis

On Mon, 6 Feb 2023 at 15:42, Dominic Brooks <dombrooks_at_hotmail.com<mailto: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