1 2 Previous Next 17 Replies Latest reply on May 19, 2014 12:33 PM by Hoek

    200 plus Million insert from one table to another table

    2674629

      Hi All,

       

      We need to move 200 Plus million records from one table to another table (simple insert ) in Oracle 10g, Currently this operation is taking more that 3.5 hrs. We are trying optimize this process with parallel hint option. But hint is getting ignored by optimizer and no performance improvement. Can you please help me how to achieve this one.

       

      Many thanks,

      Yuvaraj

        • 1. Re: 200 plus Million insert from one table to another table
          Hoek

          Did you enable Parallel DML?

           

          i.e.:

          alter session enable parallel dml;

          then run the insert.

           

          http://docs.oracle.com/cd/E16655_01/server.121/e17613/parallel003.htm#VLDBG1438

          • 2. Re: 200 plus Million insert from one table to another table
            Manik

            Hint is not a magic wand

            CTAS (create table as select can be thought of in your scenario)

            Are you sure you are using the correct format /*+ parallel(tblnm)*/ 

             

            Show us the code (should include the create table script of the table) + plan

             

            Cheers,

            Manik.

            • 3. Re: 200 plus Million insert from one table to another table
              brunovroman

              Hello Yuvaraj,

               

              I guess that the table already contains many rows. Maybe it has also indexes and constraints? If you know for sure that the new rows satisfy the constraints, you might disable the constraints before the insert, as well as drop the indexes, and re-enable the constraints and rebuild the indexes after the insert.

               

              Best regards,

               

              Bruno

              • 4. Re: 200 plus Million insert from one table to another table
                michaelrozar17

                You did not tell us the query/method you followed to move the data - show us the query so that we would get a clear picture and provide better suggestions. You can consider datapump as well.

                • 5. Re: 200 plus Million insert from one table to another table
                  SomeoneElse

                  Try: insert /*+ append */ into target...

                  • 6. Re: 200 plus Million insert from one table to another table
                    2674629

                    Hi Guys,

                     

                    Thanks for your reply,

                     

                    Current query is create table T1 as select * from T2; table T2 contains 200 plus records. and we enabled/Forced the parallel DML session for new insert query insert into T1 select * from T1,but then hint is getting ignored.

                     

                    Please find below sample queries:

                     

                    existing query:


                     

                     

                    create table t1 as select * from t2;

                     

                     

                     

                    Modified and tested options;

                     

                     

                     

                    create table t1 as select * from t2 where 1 =2;

                     

                     

                     

                    ALTER SESSION FORCE PARALLEL/FORCE DML;

                     

                     

                     

                    INSERT /* + append parallel(b,20) */ INTO t1 b

                    SELECT /* + parallel(a,20) */ * FROM t2 a;

                     

                     

                     

                    INSERT /*+parallel(b,20) */ INTO t1 b SELECT  *

                    FROM t2 A;

                     

                    INSERT /*+parallel(b,20) */ INTO t1 B SELECT  /*+parallel(a,20) */ *

                    FROM t2 A;

                     

                    INSERT INTO t1 B SELECT  /*+parallel(a,20) */ *

                    FROM t2 A;

                     

                    Thanks,

                    Yuvaraj M

                    • 7. Re: 200 plus Million insert from one table to another table
                      2674629

                      Hi Thanks for your reply, I have replayed from my original query with example for your understanding.

                      • 8. Re: 200 plus Million insert from one table to another table
                        2674629

                        We inserting data into new table with no constraint on that table..

                        • 9. Re: 200 plus Million insert from one table to another table
                          SomeoneElse

                          > INSERT /* + append parallel(b,20) */ INTO t1 b

                          > SELECT /* + parallel(a,20) */ * FROM t2 a;

                           

                          If this statement is exactly as you ran it, these hints will not work.  You must not have a space between the asterisk and the plus sign.

                           

                          Have you tried just doing a CTAS with the parallel option?

                           

                          SQL> explain plan for create table b as select * from a;

                           

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

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

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

                          |   0 | CREATE TABLE STATEMENT |      | 72468 |  6864K|   466   (1)| 00:00:06 |

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

                          |   2 |   TABLE ACCESS FULL    | A    | 72468 |  6864K|   291   (2)| 00:00:04 |

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

                           

                          SQL> explain plan for create table b parallel as select * from a;

                           

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

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

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

                          |   0 | CREATE TABLE STATEMENT |          | 72468 |  6864K|    31   (0)| 00:00:01 |        |      |            |

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

                          |   2 |   PX SEND QC (RANDOM)  | :TQ10000 | 72468 |  6864K|    20   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

                          |   3 |    LOAD AS SELECT      | B        |       |       |            |          |  Q1,00 | PCWP |            |

                          |   4 |     PX BLOCK ITERATOR  |          | 72468 |  6864K|    20   (0)| 00:00:01 |  Q1,00 | PCWC |            |

                          |   5 |      TABLE ACCESS FULL | A        | 72468 |  6864K|    20   (0)| 00:00:01 |  Q1,00 | PCWP |            |

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

                          • 10. Re: 200 plus Million insert from one table to another table
                            Hoek

                            Besides the points made by SomeoneElse /*+ edit: corrected after SY's remark below */   (check your hint syntax, choose either APPEND or PARALLEL, no use in providing them both) APPEND will already run in parallel:

                             

                            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                            PL/SQL Release 11.2.0.1.0 - Production
                            CORE    11.2.0.1.0      Production
                            TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
                            NLSRTL Version 11.2.0.1.0 - Production

                             

                            Elapsed: 00:00:00.00
                            SQL> drop table t purge;

                             

                            Table dropped.

                             

                            Elapsed: 00:00:00.37
                            SQL> create table t (col number);

                             

                            Table created.

                             

                            Elapsed: 00:00:00.01
                            SQL> insert /*+ append */ into t
                              2  with gen1 as (select level col1 from dual connect by level <= 200)
                              3  ,    gen2 as (select level col2 from dual connect by level <= 1000)
                              4  ,    gen3 as (select level col3 from dual connect by level <= 1000)
                              5  select col3
                              6  from   gen1
                              7  ,      gen2
                              8  ,      gen3;

                             

                            200000000 rows created.

                             

                            Elapsed: 00:00:41.12
                            SQL> --just to prove the insert ran in parallel I did NOT commit immediatly:
                            SQL> select count(*)
                              2  from   t;
                            from   t
                                   *
                            ERROR at line 2:
                            ORA-12838: cannot read/modify an object after modifying it in parallel


                            So, 200M (small) rows in less than a minute....(on my Win8 laptop)


                            • 11. Re: 200 plus Million insert from one table to another table
                              rp0428
                              Besides the points made by SomeoneElse (check your hint syntax, choose either APPEND or PARALLEL, no use in providing them both) APPEND will already run in parallel:

                              Isn't it the other way around?

                               

                              Parallel will use append but append won't necessarily use parallel. Per the Data Warehousing doc:

                              http://docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm

                              If parallel DML is enabled and there is a PARALLEL hint or PARALLEL attribute set for the table in the data dictionary, then inserts are parallel and appended, unless a restriction applies. If either the PARALLEL hint or PARALLEL attribute is missing, the insert is performed serially.

                               

                              Parallel DML Tip 2: Direct-Path INSERT

                               

                              The append mode is the default during a parallel insert: data is always inserted into a new block which is allocated to the table. Therefore the APPEND hint is optional. You should use append mode to increase the speed of INSERT operations, but not when space utilization needs to be optimized. You can use NOAPPEND to override append mode.

                               

                              The APPEND hint applies to both serial and parallel insert: even serial inserts are faster if you use this hint. APPEND, however, does require more space and locking overhead.

                              The PARALLEL hint is explicit and can override default values for the table. But APPEND may not use parallel since parallel may be disabled for a particular table.

                              • 12. Re: 200 plus Million insert from one table to another table
                                Hoek

                                Yup, you're right, rp. Time for me to fresh up my memory, so many Oracle stuff to remember

                                Documented hint says, amongst others: "The decision whether the INSERT will go parallel or not is independent of the APPEND hint."

                                http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50901

                                • 13. Re: 200 plus Million insert from one table to another table
                                  Solomon Yakobson

                                  Hoek wrote:

                                   

                                  Besides the points made by SomeoneElse (check your hint syntax, choose either APPEND or PARALLEL, no use in providing them both) APPEND will already run in parallel:

                                   

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


                                   

                                   

                                  APPEND doesn't mean PARALLEL. Error message is misleading. All APPEND means is direct path insert which shows up in explain plan as LOAD AS SELECT. Direct path insert can be either serial or parallel. If it is done in serial or in parallel on a partitioned table Oracle simply writes data beyoud table/partition HWM. However, when insert completes Oracle can't move HWM until current session commits, since moving HWM would make inserted data visible to other sessions right away without commit. If direct path insert is done in parallel on a non-partitioned table each slave process allocates temporary segment and writes data there. And for same reason Oracle can't merge the temporary segments into the table. And since in both cases table can't be accessed by current session until commit/rollback is issued, Oracle raises same exception in both cases. So as I said, "cannot read/modify an object after modifying it in parallel" is misleading. And it order to do direct path insert in parallel you need to at least enable parallel dml.

                                   

                                  SY.

                                  • 14. Re: 200 plus Million insert from one table to another table
                                    Hoek

                                    APPEND doesn't mean PARALLEL. Error message is misleading.

                                    You're right (as always), Solomon, I ran some additional tests (too much to post here) and it wasn't until adding the parallel hint (and enabling parallel DML) that the execution plan showed that the insert ran in parallel.

                                    1 2 Previous Next