Re: Oracle Caching

Matt,

I think a couple of architectural issues are getting confused.

The Oracle buffer cache caches the actual data. If the data you were trying to retrieve is still in the buffer cache, then that will return you the data more quickly. If it's not in the cache, then it needs to be read off disk.

The Oracle experts at your site are talking about the shared SQL area of the Oracle shared pool. It is more efficient to use identical SQL statements since they do not need to be re-parsed. It can also be efficient in avoiding aging SQL out of the shared pool.

For example, the following two queries must be parsed separately and will each take up space in the shared SQL area:

select sal from emp where empno = 1223;
select sal from emp where empno = 1224;

If you used a bind variable for the employee number, then there would only be one version of the query in the shared SQL area no matter how many times it was executed.

Keep in mind that SQL cannot be reused if there is any difference in the query being executed (spaces, capital letters, etc).

I have seen sites where there are 25 thousand slightly different versions of the same query in the shared SQL area. It created some nasty latch contention that put performance and scalability in the toilet.

Steve

"Matt Woodworth" <woodworth_at_acm.org> wrote in message news:a063f3c2.0107270645.2c5d0a5c_at_posting.google.com...
> One other thing.
>
> The oracle experts around here seem to think that the execute portion
> of the query will run faster if the same explain plan is followed.
> Even if different records are retrieved. Have you heard of that? Any
> thoughts?
Received on Fri Jul 27 2001 - 11:11:01 CDT