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: Daniel Fink
  INET: Daniel.Fink_at_Sun.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).



text/x-vcard attachment: Card for Daniel Fink