Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Execute Immediate and updates

Re: Execute Immediate and updates

From: Chris Weiss <weisschr_at_home.com>
Date: Tue, 28 Dec 1999 19:20:08 GMT
Message-ID: <38690D66.492CBCF1@home.com>


Thank you for the excellent recommendations. Using bind variables reduced the performance hit to 5%. I am going to experiment further.

Thanks Again,
Chris

Thomas Kyte wrote:

> A copy of this was sent to Chris Weiss <weisschr_at_home.com>
> (if that email address didn't require changing)
> On Tue, 28 Dec 1999 16:09:49 GMT, you wrote:
>
> >Hi,
> >
> >We are building Oracle 8i application from the ground up. We are using
> >a PL/SQL stored procedure wrapper for all transactions to the
> >applications database to control consistency, security, etc. Our
> >internal benchmarks show that stored procedures beat straight SQL
> >submitted by an external process for performance.
> >
> >Our wrapper model works in a very straight forward manner for inserts
> >and deletes, but we are having trouble coming up with a manageable model
> >for updates. Since there are 2^n - 1 possible update statements, a
> >parameter driven decision tree seems out of the question. Even
> >restricting updates to non-primary key fields does not provide enough of
> >a simplification. Hence, I see no other option than to use dynamic SQL
> >using the EXECUTE IMMEDIATE command in 8i.
> >
> >Unfortumately, EXECUTE IMMEDIATE statements seem to operate
> >approximately 2x slower than static SQL. Both forms of the queries were
> >executed in stored proceures.
> >
> >Does anyone have any tips or suggests for how to improve the performance
> >of EXECUTE IMMEDIATE statements? We are hitting the indexs, etc., but
> >the performance hit is substantial.
> >
>
> how are you doing the execute immediates? you are probably NOT using bind
> variables (else you are back to the 2^n problem again). by not using bind
> variables -- you are spending perhaps as much as much as 90% of your execution
> time parsing queries -- not executing them. I'm guessing your update looks
> like:
>
> string := 'update emp set ename = ''KING'' where empno = 1234';
> execute immediate string;
>
> and not like:
>
> string := 'update emp set ename = :1 where empno = :2';
> execute immediate string using p_ename, p_empno;
>
> You could try using DECODE to use static sql when possible. For example, take
> the DEPT table. You do not permit updates to the primary key and could write a
> procedure like:
>
> create or replace procedure update_dept
> ( p_deptno in dept.deptno%type,
> p_dname in dept.dname%type default NULL,
> p_loc in dept.loc%type default NULL )
> as
> begin
> update dept
> set dname = nvl( p_dname, dname ),
> loc = nvl( p_loc, loc )
> where deptno = p_deptno;
> end;
>
> That will update the dept row and will only update th dname column to a new
> value IF p_dname is not null. Likewise with loc. Now I can call:
>
> tkyte_at_8.0> select * from dept where deptno = 10;
>
> DEPTNO DNAME LOC
> ---------- -------------- -------------
> 10 ACCOUNTING NEW YORK
>
> tkyte_at_8.0>
> tkyte_at_8.0> exec update_dept( p_deptno => 10, p_dname => 'Something' );
>
> PL/SQL procedure successfully completed.
>
> tkyte_at_8.0> exec update_dept( p_deptno => 10, p_loc => 'SomeWhere' );
>
> PL/SQL procedure successfully completed.
>
> tkyte_at_8.0>
> tkyte_at_8.0> select * from dept where deptno = 10;
>
> DEPTNO DNAME LOC
> ---------- -------------- -------------
> 10 Something SomeWhere
>
> tkyte_at_8.0>
> tkyte_at_8.0> exec update_dept( p_deptno => 10, p_dname => 'X', p_loc => 'Y' );
>
> PL/SQL procedure successfully completed.
>
> tkyte_at_8.0> select * from dept where deptno = 10;
>
> DEPTNO DNAME LOC
> ---------- -------------- -------------
> 10 X Y
>
> will something like that work.
>
> >Our benchmarks were performed on an NT box, and our target platform will
> >be a Unix box. NT seems to do some things poorly when compared to Unix,
> >but I can't imagine this much of a performance difference.
> >
> >Thanks,
> >Chris Weiss
> >Intralect Solutions
> >chris_at_intralect.com
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Tue Dec 28 1999 - 13:20:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US