Oracle-L: RE: Cursor Sharing .... Continued

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Mon, 29 Jul 2002 07:18:30 -0800
Message-ID: <F001.004A498C.20020729071830@fatcity.com>

I believe that you need OPTIMIZER_MODE=CHOOSE as well. From the bug:

SQL> alter session set cursor_sharing=force; .
Session altered.
.
SQL> create table tb1 (f1 number(4));
.
Table created.
.
SQL> insert into tb1 values (1999);
.
1 row created.
.
SQL> insert into tb1 values (2000);
.
1 row created.
.
SQL> insert into tb1 values (2001);
.
1 row created.
.
SQL> insert into tb1 values (2002);
.
1 row created.
.
SQL> commit;
.
Commit complete.
.
SQL> select * from tb1;
.

.
SQL> analyze table tb1 compute statistics; .
Table analyzed.
.
SQL> select f1 from tb1 where f1 between 2000 and 2000; .

2000
.
SQL> select f1 from tb1 where f1 between 2000 and 2001; .

2000
.
SQL> select f1 from tb1 where f1 between 2001 and 2000; .

2001

HTH!

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Binley Lim [mailto:Binley.Lim_at_ird.govt.nz]
> Sent: Sunday, July 28, 2002 8:48 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Cursor Sharing .... Continued
>
>
>
> Rich, you don't mind providing more info on 2225065 ? You
> mean its not even fixed in 8.1.7.4 ?
>
> Can you provide a bit more on the circumstances under which it occurs?
>
> >>> Rich.Jesse_at_qtiworld.com 07/27/02 04:09a.m. >>>
> ************************************************
> * * * HAPPY DBA/SA APPRECIATION DAY!!! * * *
> ************************************************
>
> Beware of ORA-600, ORA-7445, and incorrect results w/CS=F on
> 8i! I've got
> it on 8.1.7.2 and we live with the possibilities. I get the
> most ORA-7445s
> when doing queries on the DD. This supposedly gets better
> with 8.1.7.3 and
> .4.
>
> We've also got one app that consistently returned WRONG
> results with CS=F
> (BUG 2225065). Tbe only workaround available is to turn it
> off for that app
> or upgrade to 9i.
>
> HTH!
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International,
> Sussex, WI USA
>
>
> > -----Original Message-----
> > From: Johnson, Michael [mailto:Michael.Johnson_at_oln-afmc.af.mil]
> > Sent: Thursday, July 25, 2002 7:09 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Cursor Sharing .... Continued
> >
> >
> > Setting it to Force worked well as the
> > pool has been cleared on many queries
> > using literals any many of those hard
> > parses went away. So far ... So good.
> >
> > I aint sayin nothin to the developers
> > although some have already noticed an
> > improvement.
> >
> > I am going to put the hammer down on
> > them to rewrite those literals.
> >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

Received on Mon Jul 29 2002 - 10:18:30 CDT