handle multiple insert in a script in Oracle

Question

I have to write a script with multiple insert. I did that too, and ran the script in Toad. The script completed in roughly 35 - 40 minutes.

I then applied that script to autosys, and ran it again (that's how it's supposed to be used). And this time, it kept on running for more than 1.5 hours.

I have to manually kill that active session in Oracle.

Please see my script and let me know, why it didn't completed in intended 40 min timeframe and kept on running for more than 1.5 hours.

EDIT: Would like to mention that common commands like 'commit' is called in a parent script which is called by Autosys. My script is one of the child script which is been called in the parent script.

-- Begining of Consolidated queries for Slow performing Talend Jobs 
SET SERVEROUTPUT ON;
spool Consolidated.log;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
SET DEFINE OFF;

ALTER SESSION SET GLOBAL_NAMES=FALSE;

DECLARE
ExtractType         NUMBER(9);
RecordsExtracted    NUMBER(9);
CurStatus           NUMBER(9);
StartDate           date;
ErrorMessage        NVARCHAR2(1000);
LastExtrctTimestamp DATE;


-- TABLE1 
BEGIN
    StartDate := sysdate;
    ExtractType := 44;

    DELETE FROM Table1;

    INSERT INTO Table1
    SELECT  Statement....;
    RecordsExtracted := SQL%RowCount;

    DBMS_OUTPUT.put_line('Table1 Records Extracted:' || RecordsExtracted);       

    -- On Success
    CurStatus := 2;
    ErrorMessage := 'Table1 Complete';

    INSERT INTO ExtractRecords(ExtractType, RecordsExtracted, Status, ExtractTimestamp, StartDate, EndDate, ErrorMessage)
    VALUES (ExtractType, RecordsExtracted, CurStatus, SysDate, StartDate, SysDate, ErrorMessage);

    INSERT INTO LoadRecords (LoadType,Status,LoadTimestamp,StartDate,EndDate) 
    VALUES (ExtractType, CurStatus, SysDate, StartDate, SysDate);

-- TABLE2 RESULTS 

    StartDate := sysdate;
    ExtractType := 78;

    DELETE FROM Table2;

    INSERT INTO Table2
    SELECT  Statement....;
    RecordsExtracted := SQL%RowCount;       

    DBMS_OUTPUT.put_line('Table2 Records Extracted:' || RecordsExtracted);

    -- On Success
    CurStatus := 2;
    ErrorMessage := 'Table2 Complete';

    INSERT INTO ExtractRecords(ExtractType, RecordsExtracted, Status, ExtractTimestamp, StartDate, EndDate, ErrorMessage)
    VALUES (ExtractType, RecordsExtracted, CurStatus, SysDate, StartDate, SysDate, ErrorMessage);

    INSERT INTO LoadRecords (LoadType,Status,LoadTimestamp,StartDate,EndDate) 
    VALUES (ExtractType, CurStatus, SysDate, StartDate, SysDate);

-- TABLE3 

    StartDate := sysdate;
    ExtractType := 81;

    DELETE FROM Table3;

    INSERT INTO Table3
        SELECT  Statement....;
        RecordsExtracted := SQL%RowCount;       

    DBMS_OUTPUT.put_line('Table3 Records Extracted:' || RecordsExtracted);

    -- On Success
    CurStatus := 2;
    ErrorMessage := 'Table3 Complete';

    INSERT INTO ExtractRecords(ExtractType, RecordsExtracted, Status, ExtractTimestamp, StartDate, EndDate, ErrorMessage)
    VALUES (ExtractType, RecordsExtracted, CurStatus, SysDate, StartDate, SysDate, ErrorMessage);

    INSERT INTO LoadRecords (LoadType,Status,LoadTimestamp,StartDate,EndDate) 
    VALUES (ExtractType, CurStatus, SysDate, StartDate, SysDate);


-- TABLE4 

    StartDate := sysdate;
    ExtractType := 57;

    DELETE FROM Table4;

    INSERT INTO Table4
        SELECT  Statement....;
        RecordsExtracted := SQL%RowCount;       

    DBMS_OUTPUT.put_line('Table4 Records Extracted:' || RecordsExtracted);

    -- On Success
    CurStatus := 2;
    ErrorMessage := 'Table4 Complete';

    INSERT INTO ExtractRecords(ExtractType, RecordsExtracted, Status, ExtractTimestamp, StartDate, EndDate, ErrorMessage)
    VALUES (ExtractType, RecordsExtracted, CurStatus, SysDate, StartDate, SysDate, ErrorMessage);

    INSERT INTO LoadRecords (LoadType,Status,LoadTimestamp,StartDate,EndDate) 
    VALUES (ExtractType, CurStatus, SysDate, StartDate, SysDate);
END;

spool off;
exit;
/

How could we possibly tell? You haven't provided enough data. I suggest you trace your session, isolate the statement(s) that takes more time and update your question. Be concise and precise. — Vincent Malgrat
– Vincent Malgrat, Commented Feb 15, 2013 at 10:57
Hi Vincent, can you please tell me what more information is required by you? I have ran the queries individually before consolidating them in one query (as running them in one script is the original req) — Shikha
– Shikha, Commented Feb 18, 2013 at 3:57
Yeah, there's really not enough information here. I assume the time taken is at the INSERT INTO ... SELECT ... statements, but those could be inserting a couple of rows, or a couple of million rows. I would suggest stepping through each statement here, checking the execution plans as you go. — eaolson
– eaolson, Commented Feb 18, 2013 at 4:40
There are many potential issues here, you need to sit down and talk with a DBA or other Oracle programmer. First, the process needs better logging so you can easily track how long each step takes. Sit down with a DBA while the process runs and see what the statements are doing - look at plans, resources, blocking sessions, etc. And you need to define how important this data is. If the hardware fails, is it acceptable to lose this data and re-run to regenerate the results? If so, TRUNCATE instead of DELETE, and /*+ PARALLEL APPEND*/ hints may make this process run many times faster. — Jon Heller
– Jon Heller, Commented Feb 18, 2013 at 5:59

schurik · Accepted Answer · 2013-02-15 11:39:41Z

1

There is no COMMIT in your script. May be the other session (autosys), was waiting for the commit or rollback in first session (Toad)

answered Feb 15, 2013 at 11:39

schurik

7,9582 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shikha Over a year ago

Hi schurik, My bad, I forgot to mention, that the commit is called in the another script, which acts as a parent script. That parent script calls many other child scripts. This helps us, not to re-write some customs commands to be used again and again in child script (Commit being one of them).

Alex Poole · Accepted Answer · 2013-02-18 11:29:14Z

The end of your anonymous block is wrong:

END;

spool off;
exit;
/

... should probably be:

END;
/

spool off;
exit;

You've included the spool off and exit commands inside the block, which means that the block will error. However, this probably isn't right either; you said this is called from another script, which if you're relying on transaction control in the parent implies it's via a @ or similar; but having an exit in this will cause the parent script to exit when this child script completes, which isn't what you want to happen.

But that doesn't explain what you're seeing... @schurik is probably right. You said that the transaction control is in the parent script, but it sounds like you ran this single child script stand-alone in Toad, which means the parent wasn't run, and therefore whatever commit or rollback the parent has is irrelevant. You have to commit or rollback in Toad or the Autosys version will block, waiting forever for the Toad version to release any locks it is holding.

There may well be performance issues and tweaks, and jonearles has pointed out some starting points; but you have to make sure it's doing anything at all first.

Collectives™ on Stack Overflow

handle multiple insert in a script in Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related