This discussion is archived
9 Replies Latest reply: Jul 21, 2013 2:41 PM by sybrand_b RSS

insert query is slow

985871 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

  • 5. Re: insert query is slow
    sb92075 Guru
    Currently Being Moderated

    what can you do to speed up the SELECT?

  • 6. Re: insert query is slow
    985871 Newbie
    Currently Being Moderated

    I heard something about bulk collect ....

  • 7. Re: insert query is slow
    sb92075 Guru
    Currently Being Moderated

    PL/SQL will never be faster than plain SQL.

  • 8. Re: insert query is slow
    Tubby Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    You need to find out what it is waiting for.

    gv$session_events, gv$session_waits etc.

     

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

    Sybrand Bakker

    Senior Oracle DBA

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points