Execute Immediate and updates

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.

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 Received on Tue Dec 28 1999 - 10:09:49 CST