OraFAQ Forum: Performance Tuning » Is there any way to have the DB articulate why PDML is blocked

Home » RDBMS Server » Performance Tuning » Is there any way to have the DB articulate why PDML is blocked (11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is there any way to have the DB articulate why PDML is blocked [message #584243]

Fri, 10 May 2013 08:03

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

All,

I think the answer is "no", but I figure you guys will know if it can be done.

The question is, per title, simple:

Can I have the database show why it cannot perform parallel DML (update) to a given object?

I've checked the basics in accordance with the documentation i.e. triggers/partitions/self referencing FKs etc - I'd like to say I know what I'm doing with this but this eludes me.

The only vague reference in the documentation is "replication is not supported" but there is no expansion to this.

I was hoping to find out what I'm missing that is blocking the PDML.

I have tried a 10053 trace to check the hint section - but of course the hint is accepted anyway as even if the PDML cant be done, it still parallelizes the underlying object scan Sad

I CAN take a copy of the DDL and get PDML to work on that copy - my only suspect, really, is goldengate replication blocking it. You may think this is covered by "replication is not supported" but in my brief foray into that, I was pretty sure it sat well "under" the optimizer so I'd have been surprised (though not shocked) if that was the culprit.

Cheers

Report message to a moderator

Re: Is there any way to have the DB articulate why PDML is blocked [message #584248 is a reply to message #584243]

Fri, 10 May 2013 09:10

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What are your parallel session/instance parameters (show parameter parallel)?

Regards
Michel

Report message to a moderator

Re: Is there any way to have the DB articulate why PDML is blocked [message #584251 is a reply to message #584248]

Fri, 10 May 2013 09:33

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Code: [Select all] [Show/ hide]

15:31:15 SQL>sho parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     FALSE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     512
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     512
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

Code: [Select all] [Show/ hide]

15:32:32 SQL>l
  1  select PDML_STATUS from v$session
  2* where username = USER
15:32:33 SQL>/

PDML_STA
--------
ENABLED
ENABLED

I /can/ get PDL working on some tables but not all. One of the troubled tables has a trigger, but disabling it doesnt help.

As mentioned, no self referencing constraints, just a PK and not nulls/checks. No LOBs /distributed tnxs either, not IOT/cluser. It's basic of the basic as table structures go.

[Updated on: Fri, 10 May 2013 09:41]

Report message to a moderator

Re: Is there any way to have the DB articulate why PDML is blocked [message #584260 is a reply to message #584251]

Fri, 10 May 2013 11:29

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The problem may be:

Code: [Select all] [Show/ hide]

parallel_min_time_threshold          string      AUTO

Are the DML that do not take the parallel way the fastest ones (in serial)?

Regards
Michel

Report message to a moderator

Re: Is there any way to have the DB articulate why PDML is blocked [message #584327 is a reply to message #584260]

Mon, 13 May 2013 01:58

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I can't tell - I cant get it to display a parallel plan to see the estimate. I shouldn't think so - these are big (in the context of PX being worse than serial) (7-10ish gb) tables.

Plus when I regenerate the tables using their own DDL, I can force a parallel plan, just not on the "live" tables.

[Updated on: Mon, 13 May 2013 02:06]

Report message to a moderator

Re: Is there any way to have the DB articulate why PDML is blocked [message #584328 is a reply to message #584327]

Mon, 13 May 2013 02:07

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As parallel_degree_policy is MANUAL, parallel_min_time_threshold is ignored and internally set to its default value of 30 seconds.

Regards
Michel

Report message to a moderator

Re: Is there any way to have the DB articulate why PDML is blocked [message #584330 is a reply to message #584328]

Mon, 13 May 2013 02:29

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

For the two objects I know of which give me trouble the plans estimate 36 and 66 seconds respectively, in serial.

Report message to a moderator

Re: Is there any way to have the DB articulate why PDML is blocked [message #584336 is a reply to message #584330]

Mon, 13 May 2013 03:12

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Sorry, no more idea for the moment.
Maybe someone else?

regards
Michel

Report message to a moderator

Re: Is there any way to have the DB articulate why PDML is blocked [message #584338 is a reply to message #584336]

Mon, 13 May 2013 03:16

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Yeah, I've hit the limits of my understanding too - as I say, golden gate is my last remaining suspect which is why I was hoping there was a way I could have the DB tell me what the issue around using it was.

Report message to a moderator

Re: Is there any way to have the DB articulate why PDML is blocked [message #584545 is a reply to message #584338]

Wed, 15 May 2013 05:01

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi,

I am not sure if it makes any help, not sure if you are forcing a parallel hint.

Quote:

The parallel() hint does not tell Oracle to run a query in parallel, it tells the optimizer to use a certain scale factor in certain areas of its calculation. But if a path is still too expensive, even after scaling, that path will not be chosen.

Jonthan has written here

Regards,
Pointers

Report message to a moderator

Re: Is there any way to have the DB articulate why PDML is blocked [message #584554 is a reply to message #584545]

Wed, 15 May 2013 06:17

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Thanks.

I am hinting, yes. It *is* taking the hint - observed in both 10053 trace and the fact that even when PDML is not possible it still scans the underlying object in parallel if you requested PDML. I'm seeing that scan happen in parallel.

It's just the PDML part it is not doing. Unfortunately I've been a bit busy this week so haven't had the chance to dig further into it Sad

[Updated on: Wed, 15 May 2013 06:18]

Report message to a moderator

Previous Topic:	why this query is utilizing my undo so rapidly?
Next Topic:	Oracle generates different execution plans for test and prod environments

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 02:38:25 CDT 2025