Home » RDBMS Server » Performance Tuning » Parallel direct path insert no quicker (merged 2 ) (Oracle 11)
Parallel direct path insert no quicker (merged 2 ) [message #633703] Tue, 24 February 2015 06:09 Go to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
I'm trying to insert 5 million rows from one table to another:

insert /*+ append */ into t_customers select * from u_customers;

I can see that it's doing a direct path insert as required from the explain plan. It takes 15 minutes to complete. There are approx 10 indexes and 25 constraints on the table.

Then I add parallel 8 and check the explain plan shows a reduced cost. Then I run it and check in v$session that 8 threads are created but it's not running any quicker, still takes 15 minutes.

insert /*+ append parallel(Cool */ into t_customers select * from u_customers; is the statement.

Does anyone know why the parallel is not speeding it up? The table is not locked or used by others during this process.

Many thanks
Re: Parallel direct path insert no quicker (merged 2 ) [message #633706 is a reply to message #633703] Tue, 24 February 2015 06:15 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Post the plans (and check for triggers)
Re: Parallel direct path insert no quicker (merged 2 ) [message #633707 is a reply to message #633703] Tue, 24 February 2015 06:18 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I thnik your statement attempts to parallelise only the INSERT, not the SELECT. Also, it isn't only triggers: if you have many constraints some of them (for example, a self-referencing foreign key) may prevent parallelism.
Re: Parallel direct path insert no quicker (merged 2 ) [message #633708 is a reply to message #633707] Tue, 24 February 2015 06:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 1218 times)

[Updated on: Tue, 24 February 2015 06:39]

Report message to a moderator

Re: Parallel direct path insert no quicker (merged 2 ) [message #633714 is a reply to message #633712] Tue, 24 February 2015 06:41 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Please do it as I did. And use the [code] tags, How to use [code] tags and make your code easier to read
All I can see so far is that you are not gatting any PDML
Re: Parallel direct path insert no quicker (merged 2 ) [message #633716 is a reply to message #633714] Tue, 24 February 2015 06:42 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I really wish the plan would tell us why you don't get the PDML. I mean, the thing KNOWS why it can't do it, but it wont tell us (sometimes figuring it out is less than obvious).
Re: Parallel direct path insert no quicker (merged 2 ) [message #633717 is a reply to message #633714] Tue, 24 February 2015 06:43 Go to previous messageGo to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member

Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |                 |  5925K|    11G|  5670   (2)|        |      |            |
|   1 |  LOAD AS SELECT       | T_ORGANISATIONS |       |       |            |        |      |            |
|   2 |   PX COORDINATOR      |                 |       |       |            |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000        |  5925K|    11G|  5670   (2)|  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |                 |  5925K|    11G|  5670   (2)|  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| U_ORGANISATIONS |  5925K|    11G|  5670   (2)|  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

Re: Parallel direct path insert no quicker (merged 2 ) [message #633718 is a reply to message #633716] Tue, 24 February 2015 06:43 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
My plan does - that was 12.1.0.2. Is that new? I can't remember.
Re: Parallel direct path insert no quicker (merged 2 ) [message #633720 is a reply to message #633718] Tue, 24 February 2015 06:44 Go to previous messageGo to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
explain plan for insert /*+ append parallel( 8 ) */ into t_organisations select * from u_organisations;

[Updated on: Tue, 24 February 2015 06:45]

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 Go to previous messageGo to next message
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 #633724 is a reply to message #633720] Tue, 24 February 2015 06:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Amd if you disable the consrtaints?
Re: Parallel direct path insert no quicker (merged 2 ) [message #633758 is a reply to message #633724] Tue, 24 February 2015 08:29 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: query to data dictionary tables taking long time
Next Topic: How to improve performance? (merged)
Goto Forum:
  


Current Time: Thu Mar 28 09:13:27 CDT 2024