RE: cursor_sharing in 8.1.7.4 - good or bad

Good stuff to know! In the specific case I'm thinking of, the query would always look for a column value of "N". This should only be about 5-10 rows at a time out of over 2M. Not the best design for scalability, but that's another thread.

It'll be interesting to see what'll happen in testing 9i and the opti-peeker because that column will not have any values of "N" at the time it's analyzed.

Thanks for the heads-up!
Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA

> -----Original Message-----
> From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]
> Sent: Tuesday, August 19, 2003 11:59 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: cursor_sharing in 8.1.7.4 - good or bad
>
>
> 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).