| 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: dynamic/static sql in DB2/Oracle
A copy of this was sent to "Frank Leenders" <ftleenders_at_hotmail.com>
(if that email address didn't require changing)
On Tue, 9 Feb 1999 10:50:15 +0100, you wrote:
>Hello all,
>at the moment I work in a DB2 environment and the experts all talk about
>dynamic and static SQL. It seems that SQL has to go through a 'bind' proces
>(some kind of precompiling) to make it static in order to have good
>performance. Static SQL is discouraged.
db2 was designed to have precompiled SQL stored in the database. Thats the way it works. Dynamically parsing and optimizing a sql query at runtime goes against the way they designed it. You can do it but it performs poorly.
Oracle was designed to have SQL compiled on the fly. It built a feature into the database called Shared SQL that optimizes the reuse of queries plans. The first time a query is executed in the database, it is parse and optimized and stored in the shared sql area. as long as it doesn't get aged out, the next time that query is executed -- that plan is reused (making the second execution faster since it did not have to be parsed and executed). So, my session might be the first one to submit "select * from emp" and then your session submits it and reuses my work.
The upside to dynamic sql and the shared sql approach is that if an index is added, a table analyzed, etc, etc, etc, the query plans that are affected by this change are automatically flushed from the shared sql area and redone for you automatically. In a db2 world, you would have to manually rebuild the plans that are now out of date to make them realize new statistics or indexes.
In Oracle8i, you can if you want store query outlines which are stored access plans in the database. This allows you to parse and optimize a query once, save the plans into the database and reuse them over and over again (its called query plan stability -- when you don't want the plan to ever change even if a table is analyzed or whatnot)...
>In the Oracle world I never heard about such a difference and I never
>encountered the term during my Oracle work. Is it correct to assume that
>Oracle 'binds' at runtime (by the optimizer).
>Thanx
>Frank
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Feb 09 1999 - 09:51:25 CST
 |
 |