| Parallel DML problems (update) [message #629346] |
Mon, 08 December 2014 03:00  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Hi all.
Hit this problem recently and I figure it is a limitation which I've overlooked but my problem is thus.
I have to update a monster of a table, I dont have the undo (I'd need 0.5-1Tb of undo) to do it in one fell swoop so I need to loop it, which means pl/sql. I'm hitting major problems getting the update to work in parallel when I use plsql though.
I get a proper parallel plan in raw sql, so that rules out the usual blockers to PDML, however it doesnt work in pl/sql...
Why won't the update go parallel in the pl/sql?
create table system.t as select * from dba_objects
/
Table created.
explain plan for update /*+ parallel(8) */ system.t set object_name = object_name
/
Explained.
Select * from table(dbms_xplan.display(format=>'ADVANCED'))
/
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2037160838
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 28851 | 1859K| 14 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 28851 | 1859K| 14 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | T | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 28851 | 1859K| 14 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T | 28851 | 1859K| 14 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- Degree of Parallelism is 8 because of hint
begin
execute immediate q'@explain plan for
update /*+ parallel(8) */ system.t set object_name = object_name@'
;
end;
/
PL/SQL procedure successfully completed.
Select * from table(dbms_xplan.display(format=>'ADVANCED'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1378397380
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 28851 | 1859K| 14 (0)| 00:00:01 | | | |
| 1 | UPDATE | T | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 28851 | 1859K| 14 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 28851 | 1859K| 14 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T | 28851 | 1859K| 14 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- Degree of Parallelism is 8 because of hint
I'm not using execute immediate in the actual proc, this is just a test case - but the results are the same - no PDML when the update is wrapped in pl/sql.
Has anyone run into this before?
[Updated on: Mon, 08 December 2014 03:01] Report message to a moderator |
|
|
|
| Re: Parallel DML problems (update) [message #629376 is a reply to message #629346] |
Mon, 08 December 2014 04:28  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
How about a from-outside-the-box solution? Use dbms_redefinition.
You can do the DML in the COL_MAPPING parameter, and if the interim
table is NOLOGGING you get direct load with no redo or undo. I think.
Of course, you would need double the space for the table. And I do
not know if it can be parallelized.
|
|
|
|
| Re: Parallel DML problems (update) [message #629377 is a reply to message #629376] |
Mon, 08 December 2014 04:37 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Good idea, but unfortunately I'm working with a lot of artificial restrictions, otherwise I'd just CTAS and rename.
Basically they don't want the blocks materially moved around/affected. It's for perf test and they're a bit worried about index clustering etc and want as close to a mirror of the prod physical layout as possible, so I'm limited to plain DML.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
