| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Execute Immediate and updates
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
set dname = nvl( p_dname, dname ),
loc = nvl( p_loc, loc )
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 - 11:46:12 CST
 |
 |