Forum Stats

  • 3,769,601 Users
  • 2,252,991 Discussions
  • 7,875,117 Comments

Discussions

Direct path insert ignored

Abhisek Samanta
Abhisek Samanta Member Posts: 50
edited Aug 30, 2018 12:30PM in SQL & PL/SQL

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 !

Tagged:
John ThortonWilliam RobertsonJonathan LewisBEDEDom Brooks_Dylan_Sven W.L. FernigriniAbhisek Samantajaramill

Best Answer

«1

Answers

  • Unknown
    edited Aug 28, 2018 4:23PM
    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 ThortonAbhisek SamantaAbhisek Samanta
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Aug 28, 2018 5:47PM
    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_
    _Dylan_ Member Posts: 587 Bronze Badge
    edited Aug 28, 2018 6:02PM
    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.

    Jonathan LewisAbhisek Samanta
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Aug 28, 2018 6:23PM
    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).

    _Dylan_Abhisek Samanta
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Aug 28, 2018 6:28PM
    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
    edited Aug 28, 2018 7:15PM
    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.

    William RobertsonAbhisek Samanta
  • BEDE
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy
    edited Aug 29, 2018 12:37AM
    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
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Aug 29, 2018 3:07AM
    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)

    William RobertsonAbhisek Samanta
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,786 Gold Crown
    edited Aug 29, 2018 4:52AM

    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

    BEDEDom BrooksSven W.L. Fernigrini
This discussion has been closed.