DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Long running process !!

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    Long running process !!

    I AM TRYING TO INSERT 120 million rows into a table and forgot to disable a index. The insert has been running for 24 hours.v$session_longops is not giving me the information as of how much work already completed. When I run a selec on the table after 1hr 30 min it has not given the output. Any suggestion whether I should kill the process and start over after disabling the index ?? Could it take as much time to rollback even tho I have frequent commits ?

    When I moved the data to the temp table(changed a column from number to varchar) without index it completed in 8 hours. Now after making the change in the original table I am moving the data back and it has been running for 24 hours.

    ANY SUGGESTION ?
    =============================
    declare
    Cursor C_IVR_FACT is
    select TIME_KEY,ORIG_CALL_ID,CURR_CALL_ID,GEOGRAPHY_KEY,CUSTOMER_KEY,HOUSE_KEY,SEQNUM,CURRENT_NODE_KEY,PREV IOUS_NODE_KEY,NEXT_NODE_KEY,DIALED_NUMBER_KEY,EMPLOYEE_KEY,ROUTER
    _CALL_KEY_ID,ROUTER_CALL_DAY,PROMPT,RESPONSE,DTMF,ACCOUNT_NBR,WO_NBR,SUB_TYPE,VAR1,VAR2,VAR3,DB_DATE _TIME,CREATE_DT,LAST_UPDATE_DT,MILLISECONDS,APPLICATION,EXITPOINT
    from EDW.C2O_IVR_FACT_TEMP;
    i_count integer;
    i_nbr_inserted integer;
    BEGIN
    i_nbr_inserted := 0;
    i_nbr_inserted := 0;
    for C_IVR_FACT_REC in C_IVR_FACT loop
    INSERT INTO EDW.C2O_IVR_FACT(TIME_KEY,ORIG_CALL_ID,CURR_CALL_ID,GEOGRAPHY_KEY,CUSTOMER_KEY,HOUSE_KEY,SEQNUM,CURR ENT_NODE_KEY,PREVIOUS_NODE_KEY,NEXT_NODE_KEY,DIALED_NUMBER_K
    EY,EMPLOYEE_KEY,ROUTER_CALL_KEY_ID,ROUTER_CALL_DAY,PROMPT,RESPONSE,DTMF,ACCOUNT_NBR,WO_NBR,SUB_TYPE, VAR1,VAR2,VAR3,DB_DATE_TIME,CREATE_DT,LAST_UPDATE_DT,MILLISECONDS,APPLIC
    ATION,EXITPOINT)
    values
    (C_IVR_FACT_REC.TIME_KEY,C_IVR_FACT_REC.ORIG_CALL_ID,C_IVR_FACT_REC.CURR_CALL_ID,C_IVR_FACT_REC.GEOG RAPHY_KEY,C_IVR_FACT_REC.CUSTOMER_KEY,C_IVR_FACT_REC.HOUSE_KEY,C_IVR_FAC
    T_REC.SEQNUM,C_IVR_FACT_REC.CURRENT_NODE_KEY,C_IVR_FACT_REC.PREVIOUS_NODE_KEY,C_IVR_FACT_REC.NEXT_NO DE_KEY,C_IVR_FACT_REC.DIALED_NUMBER_KEY,C_IVR_FACT_REC.EMPLOYEE_KEY,C_IV
    R_FACT_REC.ROUTER_CALL_KEY_ID,C_IVR_FACT_REC.ROUTER_CALL_DAY,C_IVR_FACT_REC.PROMPT,C_IVR_FACT_REC.RE SPONSE,C_IVR_FACT_REC.DTMF,C_IVR_FACT_REC.ACCOUNT_NBR,C_IVR_FACT_REC.WO_
    NBR,C_IVR_FACT_REC.SUB_TYPE,C_IVR_FACT_REC.VAR1,C_IVR_FACT_REC.VAR2,C_IVR_FACT_REC.VAR3,C_IVR_FACT_R EC.DB_DATE_TIME,C_IVR_FACT_REC.CREATE_DT,C_IVR_FACT_REC.LAST_UPDATE_DT,C
    _IVR_FACT_REC.MILLISECONDS,C_IVR_FACT_REC.APPLICATION,C_IVR_FACT_REC.EXITPOINT);
    i_nbr_inserted := i_nbr_inserted + 1;
    i_count := i_count + 1;
    if i_count = 15000 then
    commit;
    i_count := 0;
    end if;

    end loop;
    commit;

    end;
    /

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Rollback would affect just the current transaction.
    Your choice.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jun 2005
    Location
    Florida
    Posts
    41
    Since its your real table, you can also make sure no application/user process has locked this table. Also check v$session_wait for waits events along with system_wait
    You can verify the trace by :
    EXEC DBMS_System.Set_Ev(sid, serial#, event, level, name); to generate the trace file to see where its waiting. and so on ...
    Milind
    -----------------------------
    www.milinds.com
    My Private World on the Web !

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    with all due respect, poster already knows why is taking so long... did you get the part about the forgotten index?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jun 2005
    Location
    Florida
    Posts
    41
    its my bad ..
    Milind
    -----------------------------
    www.milinds.com
    My Private World on the Web !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width