Home » SQL & PL/SQL » SQL & PL/SQL » Performance of execute immediate (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
Performance of execute immediate [message #326315] Wed, 11 June 2008 01:39 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi people,

I have 5 "execute immediate" statements in my package.

Does it have any effect in performance in terms of TIME ?


Re: Performance of execute immediate [message #326317 is a reply to message #326315] Wed, 11 June 2008 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It mostly depends on what you do inside these "execute immediate".
Of course, static statements, when possible, are better.

Regards
Michel
Re: Performance of execute immediate [message #326326 is a reply to message #326315] Wed, 11 June 2008 01:52 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I have the following execute immediate in my code
PROCEDURE P_COA_TABLES IS
    v_error_text            varchar2(2000);     -- Error text for logging purposes
    insert_DUP_TESTSUB_COAEXP_NRM varchar2(1000):=
                'insert into ADM.DUP_TESTSUB_COAEXP_NRM
                (SELECT DUP_TESTSUB.NORMALIZE_DASH(test_substance_code) test_substance_code,
                max(expiration_date) expiration_date
                FROM agdbadm.CRV_TEST_SUBST_EXPIRATION_DATE@DOCUM
                GROUP BY DUP_TESTSUB.NORMALIZE_DASH(test_substance_code))';

    insert_COAEX_OBJID_NRM varchar2(1000):=
                'insert into ADM.OBJID_NRM
                (SELECT DUP_TESTSUB.NORMALIZE_DASH(test_substance_code)
                test_substance_code,
                expiration_date, R_OBJECT_ID
                FROM agdbadm.CRV_TEST_SUBST_EXPIRATION_DATE@DOCUM)';

    delete_DUP_TESTSUB_COAEXP_NRM varchar2(500) := 'delete from ADM.DUP_TESTSUB_COAEXP_NRM';
    delete_OBJID_NRM varchar2(500)              := 'delete from ADM.OBJID_NRM';

BEGIN
    v_error_text := 'Entered Procedure P_COA_TABLES';
    ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);

    -- Deleting from temporary table ADM.DUP_TESTSUB_COAEXP_NRM
    begin
        execute immediate delete_DUP_TESTSUB_COAEXP_NRM;
    exception when others then
        v_error_text := 'Error in delete_DUP_TESTSUB_COAEXP_NRM '||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
    end;

    -- Deleteing from temporary table ADM.OBJID_NRM
    begin
        execute immediate delete_OBJID_NRM;
    exception when others then
        v_error_text := 'Error in delete_OBJID_NRM '||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
    end;

    -- inserting into ADM.DUP_TESTSUB_COAEXP_NRM table from TST schema
    begin
        execute immediate insert_DUP_TESTSUB_COAEXP_NRM;
    exception when others then
        v_error_text := 'Error in insert_DUP_TESTSUB_COAEXP_NRM '||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
    end;

    -- Inserting into ADM.DUP_TESTSUB_COAEX_OBJID_NRM table from TST schema
    begin
        execute immediate insert_COAEX_OBJID_NRM;
    exception when others then
        v_error_text := 'Error in insert_COAEX_OBJID_NRM '||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
    end;

    commit;

    v_error_text := 'Exiting Procedure P_COA_TABLES';
    ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);

Exception When Others Then
    v_error_text := 'Error in P_COA_TABLES '||SQLERRM;
    ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
    
END;



The 5th Execute Immediate is here in a function
    -- Use EXECUTE IMMEDIATE TO ENSURE NO REPARSING OF THE STATEMENT ...
    EXECUTE IMMEDIATE 'SELECT a.R_OBJECT_ID FROM ADM.OBJID_NRM a WHERE test_substance_code = :in_dash AND trunc(expiration_date) = :p_expiration_date'
    INTO objid
    USING in_dash, trunc(p_expiration_date);

    if (objid is not null) then
        url := coaurl||objid;
        return(url);
    else return(null);
    end if;



So any idea how the above would perform on time ?
Re: Performance of execute immediate [message #326333 is a reply to message #326326] Wed, 11 June 2008 02:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Get rid of the execute immediate. They add nothing to the functionality of the code, will make things run a little slower, and break the dependency model for procedures.

Execute Immediate is there for those rare times when you have no choice but to use dynamic code - if you don't NEED to use it, then don't use it.

You should find the developer who told you that this was a sensible way to code, and laugh at them.
Re: Performance of execute immediate [message #326340 is a reply to message #326315] Wed, 11 June 2008 02:33 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
The 1st 4 execute immediate is required because i am getting fresh entries across a Database through a DB link,
so I thought that creating a structure of the TABLE A which is present in the other database (accessed through the DB link) and then deleting old values and inserting the fresh ones into a local table TEMP of the same structure of A, would be good, what is your opinion ?

Can you please suggest another idea wherein i can access data faster and create a local copy of the data present on a different DB at runtime?
Re: Performance of execute immediate [message #326343 is a reply to message #326340] Wed, 11 June 2008 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The 1st 4 execute immediate is required because i am getting fresh entries across a Database through a DB link,

The first one is:
execute immediate delete_DUP_TESTSUB_COAEXP_NRM;
with
delete_DUP_TESTSUB_COAEXP_NRM varchar2(500) := 'delete from ADM.DUP_TESTSUB_COAEXP_NRM';
which is static, so "execute immediate" is not required.

In addition, getting data from a db link is NOT a reason to use dynamic SQL. Dynamic SQL is when the text of the SQL is not known at compile time.

(No comment on the whole procedure and its purpose.)

"When others" MUST end with "raise;".

Regards
Michel
Re: Performance of execute immediate [message #326345 is a reply to message #326340] Wed, 11 June 2008 02:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't need to use Execute Immediate at all. The SQL you run has no dynamic element to it at all - if you are convinced that you do need to use it, then I think you may have badly misunderstood what it does.

Try this code instead (I've jusr replaced your execute immediates with the SQL they were running:
PROCEDURE P_COA_TABLES IS
    v_error_text            varchar2(2000);     -- Error text for logging purposes

BEGIN
    v_error_text := 'Entered Procedure P_COA_TABLES';
    ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);

    -- Deleting from temporary table ADM.DUP_TESTSUB_COAEXP_NRM
    begin
        delete from ADM.DUP_TESTSUB_COAEXP_NRM;
    exception when others then
        v_error_text := 'Error in delete_DUP_TESTSUB_COAEXP_NRM '||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
    end;

    -- Deleteing from temporary table ADM.OBJID_NRM
    begin
        delete from ADM.OBJID_NRM;
    exception when others then
        v_error_text := 'Error in delete_OBJID_NRM '||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
    end;

    -- inserting into ADM.DUP_TESTSUB_COAEXP_NRM table from TST schema
    begin
        insert into ADM.DUP_TESTSUB_COAEXP_NRM
         (SELECT DUP_TESTSUB.NORMALIZE_DASH(test_substance_code) test_substance_code,
                 max(expiration_date) expiration_date
          FROM   agdbadm.CRV_TEST_SUBST_EXPIRATION_DATE@DOCUM
          GROUP BY DUP_TESTSUB.NORMALIZE_DASH(test_substance_code));
    exception when others then
        v_error_text := 'Error in insert_DUP_TESTSUB_COAEXP_NRM '||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
    end;

    -- Inserting into ADM.DUP_TESTSUB_COAEX_OBJID_NRM table from TST schema
    begin
        insert into ADM.OBJID_NRM
                (SELECT DUP_TESTSUB.NORMALIZE_DASH(test_substance_code) test_substance_code,
                        expiration_date,
                        R_OBJECT_ID
                FROM    agdbadm.CRV_TEST_SUBST_EXPIRATION_DATE@DOCUM);
    exception when others then
        v_error_text := 'Error in insert_COAEX_OBJID_NRM '||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
    end;

    commit;

    v_error_text := 'Exiting Procedure P_COA_TABLES';
    ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);

Exception When Others Then
    v_error_text := 'Error in P_COA_TABLES '||SQLERRM;
    ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
    
END;
Re: Performance of execute immediate [message #326350 is a reply to message #326315] Wed, 11 June 2008 02:56 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Ok cool, I shall do that, how about the 2nd block of code, it was taking extra time to run the 2nd block of code, so i used the execute immediate., do u think it is necessary or not ?
Re: Performance of execute immediate [message #326351 is a reply to message #326315] Wed, 11 June 2008 02:59 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I shall have lunch and come back in 45 minutes....please wait...
Re: Performance of execute immediate [message #326352 is a reply to message #326350] Wed, 11 June 2008 03:01 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
jagannathkiran wrote on Wed, 11 June 2008 09:56
it was taking extra time to run the 2nd block of code, so i used the execute immediate.

This does not make sense.
execute immediate has nothing to do with an immediate execution, as opposed to waiting a bit.

Dynamic sql is NOT faster then static. In most (all?) cases it requires more parsing efforts.
Previous Topic: ANYDATA datatype
Next Topic: help in sql
Goto Forum:
  


Current Time: Thu Dec 08 20:14:02 CST 2016

Total time taken to generate the page: 0.08701 seconds