9 Replies Latest reply: Jul 21, 2013 4:41 PM by sybrand_b RSS

    insert query is slow

    985871

      Hi Gurus ,

       

      I am on 11.2.0.3 rac database .

       

      i am pulling data by logging into a schema of another database thru a dblink & running the below query to get the data from another schema of that database .

       

      {SQL> select bytes/1024/1024/1024 from user_segments where segment_name = 'S_EVT_ACT';

       

      BYTES/1024/1024/1024

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

                39.8095703}

       

      i have dropped the above table and i am using the below script to get the data into this table from another schema which is taking too much time .


      insert into /*+ APPEND parallel (32) */ siebel1.S_EVT_ACT

      select b.ROW_ID       ,  b.CREATED       , b.LAST_UPD       , b.ACTIVITY_UID    , 

      b.APPT_START_DT   ,b.APPT_START_TM   ,  b.EVT_STAT_CD     ,  b.NAME            , 

      b.ORDER_ITEM_ID   ,  b.REF_NUM         ,  b.SRA_SR_ID       ,  b.TARGET_OU_ID    , 

      b.TODO_CD         ,  b.X_SUB_TYPE      , b.X_PORTAL_FLAG   ,  b.X_APPNT_MSG_CODE ,

      ACT_CREATED_DT,      systimestamp PROCESS_TIMESTAMP from siebel.S_EVT_ACT b;

       

      Request you to help me make it run faster .

       

      Regards,

      DBApps

        • 2. Re: insert query is slow
          985871

          below was the size of the table before drop

          \

          SQL> select bytes/1024/1024/1024 from user_segments where segment_name = 'S_EVT_ACT';

           

          BYTES/1024/1024/1024

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

                    39.8095703

          \

           

          and this is the query

          \

           

          SQL> insert into /*+ APPEND parallel (32) */ siebel1.S_EVT_ACT

          select

          b.ROW_ID       ,

            2    3    4  b.CREATED       ,

            5  b.LAST_UPD       ,

            6  b.ACTIVITY_UID    ,

            7  b.APPT_START_DT   ,

            8  b.APPT_START_TM   ,

            9  b.EVT_STAT_CD     ,

          b.NAME            ,

          10   11  b.ORDER_ITEM_ID   ,

          12  b.REF_NUM         ,

          b.SRA_SR_ID       ,

          13   14  b.TARGET_OU_ID    ,

          15  b.TODO_CD         ,

          16  b.X_SUB_TYPE      ,

          b.X_PORTAL_FLAG   ,

          17   18  b.X_APPNT_MSG_CODE ,

          19  ACT_CREATED_DT,

          systimestamp PROCESS_TIMESTAMP

          20   21  from siebel.S_EVT_ACT b;

          \

          here is the explain plan

           

          \

          SQL> explain plan for

            2  insert into /*+ APPEND parallel (32) */ siebel1.S_EVT_ACT

            3  select

          b.ROW_ID       ,

            4    5  b.CREATED       ,

            6  b.LAST_UPD       ,

            7  b.ACTIVITY_UID    ,

            8  b.APPT_START_DT   ,

          b.APPT_START_TM   ,

            9   10  b.EVT_STAT_CD     ,

          11  b.NAME            ,

          b.ORDER_ITEM_ID   ,

          12   13  b.REF_NUM         ,

          14  b.SRA_SR_ID       ,

          b.TARGET_OU_ID    ,

          15   16  b.TODO_CD         ,

          17  b.X_SUB_TYPE      ,

          b.X_PORTAL_FLAG   ,

          18   19  b.X_APPNT_MSG_CODE ,

          20  ACT_CREATED_DT,

          systimestamp PROCESS_TIMESTAMP

          21   22  from siebel.S_EVT_ACT b;

           

           

          Explained.

           

           

          SQL>

          SQL> set linesize 400

          set pagesize 999

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

           

           

          PLAN_TABLE_OUTPUT

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

          Plan hash value: 4199372896

           

           

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

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

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

          |   0 | INSERT STATEMENT  |           |    21M|  2929M|  1877K (28)| 00:15:39 |

          |   1 |  TABLE ACCESS FULL| S_EVT_ACT |    21M|  2929M|  1877K (28)| 00:15:39 |

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

           

           

          8 rows selected.

          \

          • 3. Re: insert query is slow
            sb92075

            since no WHERE clause, only a FULL TABLE SCAN can be done.

            IMO, the slowness is on the SELECT side & having PARALLEL(32) on the INSERT adds NOTHING (as shown by the EXPLAIN PLAN which shows no PX slaves).

             

            You can't push a string!

            • 4. Re: insert query is slow
              985871

              So is there anything that i can do to make it faster ..

              • 5. Re: insert query is slow
                sb92075

                what can you do to speed up the SELECT?

                • 6. Re: insert query is slow
                  985871

                  I heard something about bulk collect ....

                  • 7. Re: insert query is slow
                    sb92075

                    PL/SQL will never be faster than plain SQL.

                    • 8. Re: insert query is slow
                      Tubby

                      sb92075 wrote:

                       

                      PL/SQL will never be faster than plain SQL.

                      Never is such a very long time.

                       

                      http://allthingsoracle.com/can-plsql-be-faster-than-sql/

                       

                      Cheers,

                      • 9. Re: insert query is slow
                        sybrand_b

                        You need to find out what it is waiting for.

                        gv$session_events, gv$session_waits etc.

                         

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

                        Sybrand Bakker

                        Senior Oracle DBA