Re: Oracle Caching

Good point, I forgot to mention that. I was just trying to expand on what coworkers were telling Matt though.

I am just starting to deploy 8.1.7 at one of my client sites and had been wondering how this feature would behave.

Sounds promising..

Steve

"Ford" <get4ked_at_yahoo.com> wrote in message news:b2b9197f.0107271357.1aa14b8c_at_posting.google.com...
> Checkout 817 which has the ability to transform the kind of SQL
> statements you describe such that they are shared ie convert the
> literals to binds without changing the application.
>
> Of course you are quite correct - in general for OLTP type systems you
> want to share as much SQL as possible - in some cases the literal
> value has a significant impact on the sql optimization eg when ther is
> significant data skew and you have column histograms which would
> enable the optimizer to choose a more appropriate access method
>
> "Steve Bourgeois" <sb299_at_netzero.net> wrote in message
 news:<pog87.3280$bm5.685499_at_typhoon.ne.mediaone.net>...
> > 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 Mon Jul 30 2001 - 08:22:49 CDT