| 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 
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
|
|
|
|
|
|
| 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 |
|
|
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
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 #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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
