|
|
|
|
|
|
| Re: Parallel direct path insert no quicker (merged 2 ) [message #633708 is a reply to message #633707] |
Tue, 24 February 2015 06:21   |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
That'll push to the select, normally. It often catches people out because even if PDML is disabled, they see slaves and a parallel plan - so they think it's working. Explain plan is the only way we'll be able to tell.
Triggers was more a general thing as opposed to blocking parallel, those things are the devil and unless you're auditing you're doing it wrong at some level.
[Updated on: Tue, 24 February 2015 06:22] Report message to a moderator |
|
|
|
| Re: Parallel direct path insert no quicker (merged 2 ) [message #633710 is a reply to message #633708] |
Tue, 24 February 2015 06:27 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Thank you for that, RC. Yes,both are indeed parallel:
orclz>
orclz> create table t1 as select * from emp where 1=2;
Table created.
orclz> alter session enable parallel dml;
Session altered.
orclz> explain plan for insert /*+ parallel(2) */ into t1 select * from emp;
Explained.
orclz> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4200853112
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 14 | 546 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 14 | 546 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| T1 | | | | | Q1,00 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 14 | 546 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 14 | 546 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL | EMP | 14 | 546 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 2 because of hint
17 rows selected.
orclz>
@brown_zzz, can you show what is happening for you, in the same way that I did?
|
|
|
|
| Re: Parallel direct path insert no quicker (merged 2 ) [message #633711 is a reply to message #633710] |
Tue, 24 February 2015 06:31 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
And once I add a trigger, no more PDML:
orclz> create trigger trig1 after insert on t1 for each row begin
2 null;
3 end;
4 /
Trigger created.
orclz> explain plan for insert /*+ parallel(2) */ into t1 select * from emp;
Explained.
orclz> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 2873591275
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 14 | 546 | 2 (0)| 00:00:01 | | | |
| 1 | LOAD TABLE CONVENTIONAL | T1 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 14 | 546 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 14 | 546 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | EMP | 14 | 546 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 2 because of hint
- PDML disabled because triggers are defined
- Direct Load disabled because triggers are defined
18 rows selected.
orclz>
|
|
|
|
| Re: Parallel direct path insert no quicker (merged 2 ) [message #633712 is a reply to message #633710] |
Tue, 24 February 2015 06:32 |
 |
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
|
Member |
|
|
I've disabled all triggers before running it. I do seem to get parallel threads in v$session.
Sure:
OPERATION OBJECT_NAME OPTIONS COST
========= =========== ======= ====
5670
- INSERT STATEMENT
..- LOAD AS SELECT T_ORGANISATIONS
....- PX COORDINATOR
......- PX SEND :TQ10000 QC(RANDOM) 5670
........- PX BLOCK ITERATOR 5670
..........- TABLE ACCESS U_ORGANISATIONS FULL
-
Attachment: ep.jpg
(Size: 30.40KB, Downloaded 1766 times)
[Updated on: Tue, 24 February 2015 06:39] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Parallel direct path insert no quicker (merged 2 ) [message #633723 is a reply to message #633720] |
Tue, 24 February 2015 06:57 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Must be new in 12. I missed it on yours.
brown_zzz you're not getting parallel DML - if you were you'd see a step for index maintenance.
Have you enabled it at the session level?
You might find better performance disabling and then rebuilding indexes at the end. Are LOBS involved?
|
|
|
|
|
|
| Re: Parallel direct path insert no quicker (merged 2 ) [message #633758 is a reply to message #633724] |
Tue, 24 February 2015 08:29 |
|
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
|
Member |
|
|
I tried ENABLE PARALLEL DML and got new plan
------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 5925K| 11G| 5670 (2)| | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 5925K| 11G| 5670 (2)| Q1,01 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | T_ORGANISATIONS | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5925K| 11G| 5670 (2)| Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 5925K| 11G| 5670 (2)| Q1,00 | P->P | RANGE |
| 6 | LOAD AS SELECT | T_ORGANISATIONS | | | | Q1,00 | PCWP | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
| 7 | PX BLOCK ITERATOR | | 5925K| 11G| 5670 (2)| Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| U_ORGANISATIONS | 5925K| 11G| 5670 (2)| Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
I now have 16 sessions instead of 8, (though it takes same time to complete). Must be the large number of constraints and indexes, although these it's too slow rebuilding/re-enabling these. Good someone please explain why I had 8 threads before and now I have 16? Is it 8 for each of the select and insert parts? - I seemed to get 3 different explain plans:
1) For the first statement without the parallel hint
2) For the same statement with the parallel hint (and 8 threads ran during it)
3) One with index maint step included after "alter session enable parallel dml;" (and 16 threads ran during it)
Edit: Code tags added. Please do this.
[Updated on: Tue, 24 February 2015 08:35] by Moderator Report message to a moderator |
|
|
|
| Re: Parallel direct path insert no quicker (merged 2 ) [message #633760 is a reply to message #633758] |
Tue, 24 February 2015 08:39 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
1) Original plan - no explanation necessary I think
2) Your parallel hint initially just made it scan the source table in parallel. Plan changes to show the slave work. 8 sets of slaves as the coordinator does the rest of the work.
3) With PDML enabled, you have 8 slaves to scan (produce) and 8 slaves to write (consume from the original 8). It's expected behaviour.
https://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm
It is likely the level of indexing/constraints going on. To test, create an empty table with none of these things and load into that.
So...psuedocode:
create table empty_dest as select * from dest where 1=2;
insert /*+ parallel(8) append) */ into empty_dest .....
If THAT still takes a while, maybe you're looking at too many slaves/IO overload. But my money is on the indexing.
[Updated on: Tue, 24 February 2015 08:40] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
*/ into t_customers select * from u_customers; is the statement.
