| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Caching on PREPARE ?
A copy of this was sent to "sbe" <sbe_at_comx.fr>
(if that email address didn't require changing)
On Tue, 12 May 1998 13:40:25 +0200, you wrote:
>Does any body know if Oracle(v7) provides some kind of caching for PREPAREd
>statements ?
>In other words, can it recognize a statement that's already been parsed and
>improve the way it's processed ?
>
>Thanks in advance
>
> Stephane.
>
>
Yes, the SHARED_SQL_AREA does this caching of statements. As long as a statement matches fully another statement (both textually and in the base objects referenced) the parsed query plan will be reused from the prior parse.
so, If I execute "select * from scott.emp" and you issue "select * from scott.emp" after me, you will benefit from the parsing and optimizing that I did.
If I execute "select * from scott.emp" and you issue "Select * From Scott.Emp" you will not reuse my plan ( the case of the queries is different)
If I execute "select * from scott.emp where ename = 'KING'" and you issue "select * from scott.emp where ename = 'BLAKE'" that will be 2 different queries as well.
On the other hand, if I issue "select * from scott.emp where ename = :x" and run it with the value 'KING' bound to X and you do the same but bind 'BLAKE' to X, we WILL reuse the same plans and miss the overhead of the full parse and optimize (bind variables are good things for the shared sql area, they maximize reuse of parsed queries).
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
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 May 12 1998 - 08:29:57 CDT
 |
 |