1 2 Previous Next 20 Replies Latest reply on Mar 14, 2014 2:31 PM by Mohamed Houri Go to original post
      • 15. Re: Append mode
        padders

        What happened when you tested it?

        • 16. Re: Append mode
          Mohamed Houri

          Hermant

           

          SQL> select * from v$version;

           

          BANNER

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

          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

          PL/SQL Release 11.2.0.3.0 - Production

          CORE    11.2.0.3.0      Production

          TNS for Linux: Version 11.2.0.3.0 - Production

          NLSRTL Version 11.2.0.3.0 - Production

           

          SQL> create table t1 as select rownum n1 from dual connect by level <=1e5;

          Table created.

           

          SQL> create table t2 as select * from t1 where 0 = 1;

          Table created.

           

          SQL> insert /*+ append */ into t2 select * from t1;

          100000 rows created.

           

          SQL> select * from table(dbms_xplan.display_cursor);

           

          SQL_ID  aa4m8sqdakp8u, child number 0

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

          insert /*+ append */ into t2 select * from t1

           

          Plan hash value: 1069440229

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

          | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

          |   0 | INSERT STATEMENT   |      |       |       |    47 (100)|          |

          |   1 |  LOAD AS SELECT    |      |       |       |            |          |

          |   2 |   TABLE ACCESS FULL| T1   |   100K|  1269K|    47   (7)| 00:00:01 |

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

          Note

          -----

             - dynamic sampling used for this statement (level=2)

           

          --> No parallel insert

           

           

          SQL> rollback;

          Rollback complete.

           

          SQL> alter session enable parallel dml;

          Session altered.

           

          SQL> insert /*+ append */ into t2 select * from t1;

          100000 rows created.

           

          SQL> select * from table(dbms_xplan.display_cursor);

           

          SQL_ID  aa4m8sqdakp8u, child number 1

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

          insert /*+ append */ into t2 select * from t1

           

          Plan hash value: 1069440229

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

          | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

          |   0 | INSERT STATEMENT   |      |       |       |    47 (100)|          |

          |   1 |  LOAD AS SELECT    |      |       |       |            |          |

          |   2 |   TABLE ACCESS FULL| T1   |   100K|  1269K|    47   (7)| 00:00:01 |

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

           

          Note

          -----

             - dynamic sampling used for this statement (level=2)

           

          --> Still no parallel insert

           

          SQL> rollback;

          Rollback complete.

           

          SQL> insert /*+ append parallel(t2) */ into t2 select * from t1;

           

          100000 rows created.

           

          SQL> select * from table(dbms_xplan.display_cursor);

           

          SQL_ID  gf2g85z8bp1zu, child number 0

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

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

           

          Plan hash value: 2315600204

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

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

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

          |   0 | INSERT STATEMENT        |          |       |       |    47 (100)|          |        |      |            |

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

          |   2 |   PX SEND QC (RANDOM)   | :TQ10001 |   100K|  1269K|    47   (7)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |

          |   3 |    LOAD AS SELECT       |          |       |       |            |          |  Q1,01 | PCWP |            |

          |   4 |     PX RECEIVE          |          |   100K|  1269K|    47   (7)| 00:00:01 |  Q1,01 | PCWP |            |

          |   5 |      PX SEND ROUND-ROBIN| :TQ10000 |   100K|  1269K|    47   (7)| 00:00:01 |        | S->P | RND-ROBIN  |

          |   6 |       TABLE ACCESS FULL | T1       |   100K|  1269K|    47   (7)| 00:00:01 |        |      |            |

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

           

          Note

          -----

             - dynamic sampling used for this statement (level=2)

           

          And now parallel insert kick in

           

          Best regards

          Mohamed Houri

          • 17. Re: Append mode
            padders

            I believe Hemant was stating that using PARALLEL hint without APPEND would result in APPEND behaviour, not the other way around (APPEND without PARALLEL resulting in PARALLEL behaviour).

             

            I believe reality is slightly more complicated than the documentation implies though.

             

            If you use a PARALLEL hint and the INSERT *is* parallelized then the INSERT will use direct path.

             

            If you use a PARALLEL hint on its own and the INSERT *is not* parallelized then the INSERT will use conventional path.

             

            If you use both APPEND and PARALLEL hints and the INSERT *is not* parallelized the INSERT will be done APPEND mode (albeit serially).

            • 18. Re: Append mode
              Mohamed Houri

              padders

               

              Yes it is. Thanks for that clarification. I have just tested it

               

              SQL> alter session enable parallel dml;

               

              SQL> insert /*+ parallel(t2) */ into t2 select * from t1;

              100000 rows created.

               

               

              SQL> select count(1) from t2;

              select count(1) from t2

                                   *

              ERROR at line 1:

              ORA-12838: cannot read/modify an object after modifying it in parallel

               

               

              SQL_ID  5npb49pus3wtr, child number 1

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

              insert /*+ parallel(t2) */ into t2 select * from t1

               

              Plan hash value: 2315600204

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

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

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

              |   0 | INSERT STATEMENT        |          |       |       |    47 (100)|          |        |      |            |

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

              |   2 |   PX SEND QC (RANDOM)   | :TQ10001 |   100K|  1269K|    47   (7)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |

              |   3 |    LOAD AS SELECT       |          |       |       |            |          |  Q1,01 | PCWP |            |

              |   4 |     PX RECEIVE          |          |   100K|  1269K|    47   (7)| 00:00:01 |  Q1,01 | PCWP |            |

              |   5 |      PX SEND ROUND-ROBIN| :TQ10000 |   100K|  1269K|    47   (7)| 00:00:01 |        | S->P | RND-ROBIN  |

              |   6 |       TABLE ACCESS FULL | T1       |   100K|  1269K|    47   (7)| 00:00:01 |        |      |            |

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

              Note

              -----

                 - dynamic sampling used for this statement (level=2)

               

               

              Not only the insert has been parallel but has also been direct "pathed"

               

              In this context I was intrigued then to see how the parallel insert would react in the presence of a trigger on the inserted table

               

              SQL>  create or replace trigger t2_trg

                2      before insert on t2

                3      for each row

                4      begin

                5       null;

                6      end;

                7      /

              Trigger created.

               

              SQL> insert /*+ parallel(t2) */ into t2 select * from t1;

              100000 rows created.

               

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

              SQL_ID  5npb49pus3wtr, child number 1

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

              insert /*+ parallel(t2) */ into t2 select * from t1

               

              Plan hash value: 3617692013

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

              | Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

              |   0 | INSERT STATEMENT         |      |       |       |    47 (100)|          |

              |   1 |  LOAD TABLE CONVENTIONAL |      |       |       |            |          |

              |   2 |   TABLE ACCESS FULL      | T1   |   100K|  1269K|    47   (7)| 00:00:01 |

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

               

              Note

              -----

                 - dynamic sampling used for this statement (level=2)

               

              SQL> select count(1) from t2;

                COUNT(1)

              ----------

                  200000

               

              So in presence of a trigger not only the insert has not been run in parallel but the direct path(hidden under the parallel hint) has been silently ignored.

               

              Best regards

              Mohamed Houri

              • 19. Re: Append mode
                Hemant K Chitale

                >I believe Hemant was stating that using PARALLEL hint without APPEND would result in APPEND behaviour

                YES.

                APPEND is Direct Path INSERT.

                PARALLEL is Direct Path INSERT (with parallelism additionally thrown in).  So PARALLEL does not need the APPEND Hint.

                 

                Hemant K Chitale

                • 20. Re: Append mode
                  Mohamed Houri

                  So PARALLEL does not need the APPEND Hint.

                   

                  Hemant K Chitale

                  Hermant and padders,

                   

                  Yes Parallel does not need the Append hint when the parallel run is choosen by the CBO.

                   

                  I have summarized my investigations here below

                   

                  parallel insert | Mohamed Houri’s Oracle Notes

                   

                  Best regards

                  Mohamed Houri

                  1 2 Previous Next