-
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;
/
-
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.
-
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 !
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|