Re: cursor_sharing in 8.1.7.4 - good or bad

I'm resending my reply, because it appears to be lost. Sorry if you get a duplicate few days later (sigh).

Tanel.

> Hi!
>
> How did you check which execution plan was chosen? Explain plan and
> autotrace lie, use 10046 or v$sql_plan instead.
> For num_buckets, dba_tab_cols view substracts one from real bucket count
for
> some reason. To get real value go to base tables and check row_cnt from
> sys.hist_head$ or just count(*) appropriate rows from sys.histgrm$.
>
> But yes, the execution plan is chosen once per session, if first one took
> FTS based on one value, then if second one picks index access, another
> version of query execution plan is generated for the same SQL statement -
> only soft parse is required.
>
> Tanel.
>
> ----- Original Message -----
> From: "Daniel Fink" <Daniel.Fink_at_Sun.COM>
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, August 21, 2003 7:54 PM
> Subject: Re: cursor_sharing in 8.1.7.4 - good or bad
>
>
> > I was curious about your statement "the kernel only peeks once per
> > session". I wondered if another session that executed the same statement
> > would be impacted by the peeking or would the optimizer reparse the
> > statement.
> >
> > So I set up a test. Million row table with 2 columns. c1 - number, c2 -
> > date. C1 contains 2 distinct values 0 (999 rows) and 1 (999001 rows). I
> > analyzed the table and 'tried' to generate a 2 bucket histogram.
However,
> > the num_buckets is still 1 on the column. When I run the query with a
bind
> > variable, it always selects a FTS, regardless of the first value I place
> in
> > the variable.
> >
> > I am obviously missing something, but I've run out of ideas. I 'think'
the
> > problem is the number of buckets, but the analyze table column command
> > seems to think 1 bucket is enough.
> >
> > Daniel
> >
> >
> > Cary Millsap wrote:
> >
> > > And don't count too heavily upon 9i's "bind value peek" capability.
> > > Here's a simple test that Jonathan Lewis conjured up last year:
> > >
> > > PREPARATION. Create data for "select c1, c2 from t1 where c1 =
:bind1",
> > > where different bind1 values could have different paths if we used
> > > literals.
> > >
> > > 1. flush shared pool
> > > set bind1 = 5
> > > execute query, notice an indexed access (optimal plan for bind1=5)
> > > set bind1 = 70
> > > execute query, notice that kernel still uses the index
> > >
> > > 2. flush shared pool
> > > set bind1 = 70
> > > execute query, notice a table scan (optimal plan for bind1=70)
> > > set bind1 = 5
> > > execute query, notice that kernel still uses the table scan
> > >
> > > The moral: the kernel only peeks once per session.
> > >
> > > Cary Millsap
> > > Hotsos Enterprises, Ltd.
> > > http://www.hotsos.com
> > >
> > > Upcoming events:
> > > - Hotsos Clinic 101 in Sydney
> > > - Hotsos Symposium 2004, March 7-10 Dallas
> > > - Visit www.hotsos.com for schedule details...
> > >
> > > -----Original Message-----
> > > Jesse, Rich
> > > Sent: Tuesday, August 19, 2003 3:09 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > > True, but using binds isn't always an option, like when running a 3rd
> > > party
> > > product. I also could not justify weeks of recoding homegrown
software
> > > for
> > > the relatively minor gains we would have using binds. And scalability
> > > (or
> > > lack thereof) didn't seem to be argument enough.
> > >
> > > We're using CS=F on 8.1.7.4 on HP/UX 11.0. We haven't yet had a
single
> > > issue attributable to it in the 7 months we've been at the .4 release.
> > > There were some nasty issues with 8.1.7.2 (ORA-600 and silently
> > > returning
> > > WRONG data), but like I said, .4 seems to have cleaned that up. YMMV.
> > >
> > > Remember that under 8i, you may see some performance DROP by using
> > > binds.
> > > The case that comes to mind is if you're using histograms with ANALYZE
> > > or
> > > DBMS_STATS. Under 8i, the optimizer can no longer "see" the values
and
> > > therefore can't use the histograms. Under 9i (R2 only?), you can tell
> > > the
> > > optimizer to peek at the bind variables' values in order to make use
of
> > > histograms. We plan on doing this when we get to 9i in order to use a
> > > very
> > > low cardinality index, where less than 0.0001% of the rows is what we
> > > need
> > > to fetch, but 8i won't use the index because of the cardinality.
> > >
> > > Your best bet, of course, is to test! HTH! GL! :)
> > >
> > > Rich
> > >
> > > Rich Jesse System/Database Administrator
> > > rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA
> > >
> > > -----Original Message-----
> > > Sent: Tuesday, August 19, 2003 2:49 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > > Hi,
> > > No. I recommend that you do not change cursor_sharing if you are
doing
> > > it
> > > just to get rid of 4031. There could be serious problems. ora-600 etc.
> > > Maybe
> > > Oracle has fixed everything in latest 817 but I suggest not to take
that
> > > chance.
> > > For 4031 you can do the following : pin pl/sql sql preferably at
> > > startup,
> > > use bind variables and periodically check for sql not using bind
> > > variables.
> > > Goodluck.
> > >
> > > "Fedock, John (KAM.RHQ)" <John.Fedock_at_us.kline.com> wrote:
> > >
> > > Does anyone have any experience using cursor_sharing in 8.1.7.4? I
> > > feel I
> > > need to use it due to literal SQL statements being used. We have had
> > > misc
> > > 4031 errors off and on and I feel this is my last hope. I have a very
> > > busy
> > > OPFS server (HP-UX).
> > >
> > > I did try using cursor_sharing in 8.1.7.2, and I remember incorrect
> > > sorting
> > > and other unexpected results happening.
> > >
> > > Thanks for any input.
> > >
> > >
> > > John
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Jesse, Rich
> > > INET: Rich.Jesse_at_qtiworld.com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Cary Millsap
> > > INET: cary.millsap_at_hotsos.com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> >
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).