Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Very useful parameter - or did I dream it?

Re: Very useful parameter - or did I dream it?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 10 Oct 2000 20:33:31 +0100
Message-ID: <971206761.24505.1.nnrp-12.9e984b29@news.demon.co.uk>

And it's only in 8.1.6 which is why you may be unable to find it right now.

BTW
>> eg.
>> select xxx...yyy from zzz where columna = 'A' and columnb = :b1;
>> select xxx...yyy from zzz where columna = 'A' and columnb = :b1;
>> select xxx...yyy from zzz where columna = 'A' and columnb = :b1;
>>

In this example the second and third executions will reuse the path created on the first execution as the literals match.

select xxx...yyy from zzz where columna = 'A' and columnb = :b1;
select xxx...yyy from zzz where columna = 'B' and columnb = :b1;
select xxx...yyy from zzz where columna = 'C' and columnb = :b1;

It's only in the above that you could get the benefit of cursor_sharing; Oracle would rewrite this as (something like):

select xxx...yyy from zzz where columna = :SYSBIND01 and columnb = :b1;

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

James Lorenzen wrote in message <8rvmci$gf1$1_at_nnrp1.deja.com>...

>It is "cursor_sharing"
>HTH
> James
>
>In article <971197492.7637.0.nnrp-01.c30bdde2_at_news.demon.co.uk>,
> "Andrew Williamson" <andrewweb_at_my-deja.com> wrote:
>> Hi
>>
>> I seem to remember while trawling through the Oracle8 init.ora
parameters,
>> that I came across something that helps SQL parse/execute ratios up by
>> taking a slightly more 'relaxed' view as to whether a statement is in
the
>> cache or not..?
>>
>> I think its main area of help is where statements are repeatedly
called with
>> the same literal and thus we see the same statement in the cache
multiple
>> times for users.
>>
>> eg.
>> select xxx...yyy from zzz where columna = 'A' and columnb = :b1;
>> select xxx...yyy from zzz where columna = 'A' and columnb = :b1;
>> select xxx...yyy from zzz where columna = 'A' and columnb = :b1;
>>
>> As it stands, because of the literal, this would be parsed each time,
right?
>> I thought I saw a parameter that helped ensure it wouldn't be?
>>
>> [We didn't write the code, it's supplied to us so and we can't change
it so
>> for us, bind variables are out in this instance.]
>>
>> Needless to say, I've had a *damn* good look through the
documentation/net
>> again, but I can't find hide-nor-hair of it.
>>
>> Did I dream it - or perhaps it wasn't in the init.ora?
>>
>> Anyone?
>>
>> Andrew
>>
>>
>
>--
>Life is complex; it has real and imaginary parts.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Tue Oct 10 2000 - 14:33:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US