Direct path insert ignored

Abhisek Samanta

Hi Experts,

I'm having a table & it's same replica on two databases connected via a link.

insert /* APPEND PARALLEL(a,10)*/ into table a (select * from [email protected]_link);

The insert is supposed to insert 3,43,54,557 rows. Post insertion, index creation & stats gathering will be taking place on the target table.

However, when I'm looking at the explain plan, it's showing conventional insert; rather than direct path . I've tried append & parallel separately too.

The tables are not having any foreign key relationships.

Pls. advice...

Thanks in advance !

Jonathan Lewis

rp0428,

Would it ever cross your mind that other people do know what they're doing but sometimes can't see an obvious mistake because they're looking in the wrong direction.

Do you have a problem with being nice about offering corrections. Are you trying to ensure that people feel unwelcome in the forum ?

What was so difficult about phrasing your advice in a way that didn't imply the OP was an idle slob who couldn't be bothered to make an effort.

How about:

    "You've missed the '+' symbol in your hint."

as a starting point.

That way you've still left yourself scope for supplying the document reference and making the OP do some work to get the right answer if they were being lazy without coming across as arrogant, petty and vindictive.

Regards

Jonathan Lewis

Unknown

insert /* APPEND PARALLEL(a,10)*/ into table a (select * from [email protected]_link); 

That is NOT the format for a hint.

If you want to use a hint you need to specify it properly.

As often the case the Oracle docs explain it all.

https://docs.oracle.com/database/121/TGSQL/tgsql_influence.htm#TGSQL260

John Thorton

Abhisek Samanta wrote:Hi Experts,I'm having a table & it's same replica on two databases connected via a link.insert /* APPEND PARALLEL(a,10)*/ into table a (select * from [email protected]_link); Even after using the correct hint syntax;  it is inane to expect major performance from this hint.APPEND indicates  to add new rows to "top" of the existing rows.Only 1 row at a time can be added to the "top", so the PARALLEL is essentially moot.Besides, since the SELECT is only single threaded, the data will be throttled & gated by the rows returned by the SELECT & PARALLEL provides no benefit.

_Dylan_

John Thorton wrote:Even after using the correct hint syntax; it is inane to expect major performance from this hint.APPEND indicates to add new rows to "top" of the existing rows.Only 1 row at a time can be added to the "top", so the PARALLEL is essentially moot.Besides, since the SELECT is only single threaded, the data will be throttled & gated by the rows returned by the SELECT & PARALLEL provides no benefit.

I thought I read somewhere that parallel and append could be combined, and that under the covers Oracle allocates a different extent for each stream and then combines them when done. But I'm senile and can't remember where I read it, and can't find anything supporting it, so I'm probably wrong.

AndrewSayer

DylanB123 wrote:John Thorton wrote:Even after using the correct hint syntax; it is inane to expect major performance from this hint.APPEND indicates to add new rows to "top" of the existing rows.Only 1 row at a time can be added to the "top", so the PARALLEL is essentially moot.Besides, since the SELECT is only single threaded, the data will be throttled & gated by the rows returned by the SELECT & PARALLEL provides no benefit.I thought I read somewhere that parallel and append could be combined, and that under the covers Oracle allocates a different extent for each stream and then combines them when done. But I'm senile and can't remember where I read it, and can't find anything supporting it, so I'm probably wrong.

That is true, but the point about remote query will override that - your data is coming from a single thread (it has to over the DB link) so it's pretty much a waste of time to spread that out to multiple threads to insert it when the effort of insertion is not that large (unless you have many indexes, although that might have issues of it's own). You should certainly try in series first, if that's not good enough then try parallelizing but remember the rule - parallelism means work harder not smarter; sometimes working smarter is easier.

rp0428 hit the nail on the head though: you're not hinting, you're commenting. Once you've supplied the correct hint, then Oracle will be decent enough to tell you why it ignores direct path load in the notes section of the execution plan ( so long as you're on at least 12.1.0.1 IIRC).

John Thorton

DylanB123 wrote:John Thorton wrote:Even after using the correct hint syntax; it is inane to expect major performance from this hint.APPEND indicates to add new rows to "top" of the existing rows.Only 1 row at a time can be added to the "top", so the PARALLEL is essentially moot.Besides, since the SELECT is only single threaded, the data will be throttled & gated by the rows returned by the SELECT & PARALLEL provides no benefit.I thought I read somewhere that parallel and append could be combined, and that under the covers Oracle allocates a different extent for each stream and then combines them when done. But I'm senile and can't remember where I read it, and can't find anything supporting it, so I'm probably wrong.

I am sure that the optimizer won't complain & may actually produce multiple parallel slaves.

I would like to see an test case that shows the resultant elapsed time duration reduction by doing so.

PARALLEL is NOT always faster.

Unknown

I thought I read somewhere that parallel and append could be combined

Huh?

Parallel operations are direct-path be default. So there wouldn't be much point in using APPEND if you are using PARALLEL.

BEDE

insert /* APPEND PARALLEL(a,10)*/ into table a (select * from [email protected]_link); 

A hint is marked as /*+ hint */. So, it should be /*+ APPEND */. And I'm not sure about (a,10) like youuse it.

I'd rather do like below:

insert /*+ APPEND */ into table a (select /*+ PARALLLEL(t, 10) */ * from [email protected]_link t);

AndrewSayer

BEDE wrote:insert /* APPEND PARALLEL(a,10)*/ into table a (select * from [email protected]_link); A hint is marked as /*+ hint */. So, it should be /*+ APPEND */. And I'm not sure about (a,10) like youuse it.I'd rather do like below:insert /*+ APPEND */ into table a (select /*+ PARALLLEL(t, 10) */ * from [email protected]_link t); 

OP is trying to do parallel insert, not select (which really wouldn’t work well for this).

Although that does remind me, parallel dml needs to be enabled by

alter session enable|force parallel dml

Or the parallel Dml enable hint that was new in 12.1 (I don’t think we know the actual version in use still)

Jonathan Lewis

John Thorton,

Please try to engage your brain before reciting dogma; and please consider a proper acknowledgement (and even apology for) your mistakes when you make them.

.

You should have known that your description of "only one top" was rubbish simply by analogy with a completely local parallel insert.

You could have considered that parallel inserts (a) write direct and have multiple writers, (b) append postpones and optimizes index maintenance - so  a parallel append EVEN AFTER a serialised fetch across a db_link can be faster than a serial insert.

Demonstration of effect - plan pulled from memory through dbms_xplan.display_cursor()

SQL_ID  fyrvkubr67qug, child number 0

-------------------------------------

insert /*+ append parallel(t1 2) */ into t1 select /*+ parallel(t2,2)

*/ * from [email protected] t2

Plan hash value: 2511483212

----------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time    | TQ/Ins |IN-OUT| PQ Distrib |

----------------------------------------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT                   |          |       |      |  6072 (100)|          |        |      |             |

|   1 |  PX COORDINATOR                    |          |       |      |            |          |        |      |             |

|   2 |   PX SEND QC (RANDOM)              | :TQ10001 |  2704K|  299M|  6072   (6)| 00:00:24 |  Q1,01 | P->S |  QC (RAND)  |

|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)|          |       |      |            |          |  Q1,01 | PCWP |             |

|   4 |     OPTIMIZER STATISTICS GATHERING |          |  2704K|  299M|  6072   (6)| 00:00:24 |  Q1,01 | PCWP |             |

|   5 |      PX RECEIVE                    |          |  2704K|  299M|  6072   (6)| 00:00:24 |  Q1,01 | PCWP |             |

|   6 |       PX SEND ROUND-ROBIN          | :TQ10000 |  2704K|  299M|  6072   (6)| 00:00:24 |        | S->P |  RND-ROBIN  |

|   7 |        REMOTE                      | T1       |  2704K|  299M|  6072   (6)| 00:00:24 |   OR32 | R->S |             |

----------------------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):

----------------------------------------------------

  7 - SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("T2",2) */ "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJ

      ECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE"

      ,"EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED" FROM "T1" "T2" (accessing 'OR32' )

Note

-----

  - Degree of Parallelism is 2 because of table property

Parallel execution time: 30.37 seconds

Serial execution time: 43.87 seconds

Jonathan Lewis

Jonathan Lewis

rp0428,

Would it ever cross your mind that other people do know what they're doing but sometimes can't see an obvious mistake because they're looking in the wrong direction.

Do you have a problem with being nice about offering corrections. Are you trying to ensure that people feel unwelcome in the forum ?

What was so difficult about phrasing your advice in a way that didn't imply the OP was an idle slob who couldn't be bothered to make an effort.

How about:

    "You've missed the '+' symbol in your hint."

as a starting point.

That way you've still left yourself scope for supplying the document reference and making the OP do some work to get the right answer if they were being lazy without coming across as arrogant, petty and vindictive.

Regards

Jonathan Lewis