RE: Cursor Sharing .... Continued
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; . F1 -- 1999 2000 2001 2002 . SQL analyze table tb1 compute statistics; . Table analyzed. . SQL select f1 from tb1 where f1 between 2000 and 2000; . F1 -- 2000 . SQL select f1 from tb1 where f1 between 2000 and 2001; . F1 -- 2000 . SQL select f1 from tb1 where f1 between 2001 and 2000; . F1 -- 2001 HTH! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Binley Lim [mailto:[EMAIL PROTECTED]] 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? [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED]] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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? [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED]] 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. FWIW. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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.com -- Author: Binley Lim INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Cursor Sharing .... Continued
yes im aware -Original Message- Sent: Friday, July 26, 2002 9:09 AM To: Multiple recipients of list ORACLE-L * * * 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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED]] 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. FWIW. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Cursor Sharing .... Continued
Take your case to management with that statistics of how long queries take with and without bind variables. Let them decide when to make the developers re-code their apps. If your management doesnt make them correct it then I dont know what to say from there.My management has said It will be fixed in a future version. The question is now when to do it. FWIW. Mike -Original Message- Sent: Friday, July 26, 2002 9:55 AM To: Multiple recipients of list ORACLE-L Michael - So . . . since you have a work-around that is relatively easy from the developer point of view, how do you plan to convince them to rewrite their programs? Or do you have a really big hammer? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, July 25, 2002 7:09 PM To: Multiple recipients of list ORACLE-L 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. FWIW. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Cursor Sharing .... Continued
Michael - So . . . since you have a work-around that is relatively easy from the developer point of view, how do you plan to convince them to rewrite their programs? Or do you have a really big hammer? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, July 25, 2002 7:09 PM To: Multiple recipients of list ORACLE-L 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. FWIW. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
