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| Soft Parsing
I'll admit I'm gullible, and I do look to you as more knowledgeable than me about internals. On the other hand, I'm a born and bred New York City girl and I've never bought a bridge yet. guess I'll have to live with because as the answer Get on with you :) Rachel --- Connor McDonald [EMAIL PROTECTED] wrote: Its three because the mapping of the memory structures to the pga is most efficient when the hashing function involved uses the lowest prime that is not a power of two. (long pause) ...and if you believe that, you'll believe anything :-) hee hee hee Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: Connor, Well, technically that answers the why... with Oracle's because I said so :) the REAL question is why 3 and not 2 or 4 or 10 or. ? Rachel --- Connor McDonald [EMAIL PROTECTED] wrote: RTM :-) Performance Guide 9.2 Caching Session Cursors (blah blah blah) Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. (more blah blah blah) hth connor --- [EMAIL PROTECTED] wrote: I wasn't aware of it requiring three calls before being useful. Why is that? Jared Cary Millsap [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/25/2002 07:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Cursor Sharing| Soft Parsing Well, three times, right? I think it takes three parse calls before session_cached_cursors begins to help. But 3 is still O(1). Once per call is O(#executions). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 23-25 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas -Original Message- Still Sent: Thursday, July 25, 2002 2:38 AM To: Multiple recipients of list ORACLE-L On Wednesday 24 July 2002 22:08, MacGregor, Ian A. wrote: Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. No, code that uses bind variables need only parse SQL statements once if session_cached_cursors is set. Further executions of the same SQL don't require a hard or soft parse. Jared When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:23 PM To: Multiple recipients of list ORACLE-L Mike, Kirti, Try page 441 CURSOR_SHARING=FORCE does improve badly written applications that use lots of literals. However coding should be done using bind variables in almost all occasions. CURSOR_SHARING=FORCE reduces the hard parsing. What CURSOR_SHARING=FORCE does is rewrites all queries to use bind variables before parsing. eg. select ename from emp where empno = 10; rewritten as select ename from emp where empno =:SYS_B_0 or in 8.1.6 , 8.1.7 select name from emp where empno =:SYS_B_0 So it substitutes the literal with bind variables but incurs the cost of soft parsing the statement. Soft Parsing too frequently limits the scalability of applications and sacrifices optimal performance which could have been achieved in the first place if written using bind variables. Parse once and execute as many times as we like. Also check out Bjorn's paper on bind variables and cursor sharing at http://technet.oracle.com/deploy/performance/pdf/cursor.pdf So CURSOR sharing is not the silver bullet as one may expect. Regards Suhen === message truncated === __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Cursor Sharing| Soft Parsing
OK, I think this explains a lot with the introduction of the new and highly technical term, softer parse. So we have the hard parse, the soft parse, and the softer parse. You can get or derive stats for all three: 1) hard parses = parse count (hard) 2) soft parses (AKA parse calls) = parse count (total) minus parse count (hard) 3) softer parses = session cursor cache hits I guess soft parses also include softer parses in 2 above. Whew... it's like you have to be a lawyer to parse the Oracle documentation and reconcile it with the facts in the v$ tables. Let just call this the parse farce episode in our chronicles of the pursuit of Oracular truth. Someone please tell me it's Friday. -Original Message- Sent: Friday, July 26, 2002 10:15 AM To: Multiple recipients of list ORACLE-L * * * HAPPY DBA/SA APPRECIATION DAY!!! * * * Mr. Kyte's parsing explanation is also in this iss of OraMag. For the papyrus-impaired: http://www.oramag.com/oramag/oracle/02-jul/index.html?o42asktom.html (Be safe: Download page to laptop or PDA before taking to bathroom) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: MacGregor, Ian A. [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 25, 2002 4:58 PM To: Multiple recipients of list ORACLE-L Subject: RE: Cursor Sharing| Soft Parsing I checked the Tom Kyte site. A soft parse comprises two -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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).
RE: Cursor Sharing| Soft Parsing
Thanks for sharing your interesting research Ian. If cached cursors obviate the need for a parse call then why do they increment parse count (total)? Despite all the list contributions it does not appear that this question has been answered. As Cary Millsap pointed out, there's a problem with definitions. AND... maybe Oracle statistics data is out of sync with the definitions and the shared pool/cached cursor features. By definition a cursor is created by a parse (hard) of a valid SQL statement so if a cursor is cached then it follows that a parse is unnecessary. But the results of your query to v$sesstat contradict that. To set the stage, here's some definitional material from the Oracle docs: Concepts Manual: Oracle parses a SQL statement only if a shared SQL area for an identical SQL statement does not exist in the shared pool. In this case, a new shared SQL area is allocated and the statement is parsed. Note the difference between an application making a parse call for a SQL statement and Oracle actually parsing the statement. A parse call by the application associates a SQL statement with a private SQL area. After a statement has been associated with a private SQL area, it can be executed repeatedly without your application making a parse call. A parse operation by Oracle allocates a shared SQL area for a SQL statement. Once a shared SQL area has been allocated for a statement, it can be executed repeatedly without being reparsed. Design/Tuning Manual: Oracle uses the shared SQL area to determine whether more than three parse requests have been issued on a given statement. If so, Oracle assumes the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache. It seems like there are three things going on here but only two Oracle stats. There's a parse (hard), there's a parse call (soft, and there are 3 kinds of soft parses according to Morle), and a parse request which may not result in any parse. It's like parse requests are incrementing parse count (total) whether or not a parse of any kind is actually being performed. Obviously I'm just guessing here. So the unanswered question remains, if Oracle claims that a cached cursor hit obviates the need for either a parse operation OR a parse call, then why is parse count (total) incremented in v$sesstat Like Ian, I await an explanation! Steve Orr -Original Message- Sent: Thursday, July 25, 2002 8:28 PM To: Multiple recipients of list ORACLE-L Importance: High I didn't consider the invalidation possibilities. But here's more proof about Oracle still soft parsing with session_cached_cursors The following was run directly after session_cached_cursors was set to 10. select a.name, b.value from v$sysstat a, v$sesstat b where a.statistic# = b.statistic# and a.statistic# in (179, 180, 181, 191) and b.sid =16 / NAME VALUE - parse count (total) 12 parse count (hard) 0 execute count 12 session cursor cache hits0 --- The following SQL was executed select empno, ename, sal from scott.emp where empno = :v_empno; and the session stats showed NAME VALUE - parse count (total) 25 parse count (hard) 2 execute count 27 session cursor cache hits0 interate (2nd use of cursor) NAME VALUE - parse count (total) 26 parse count (hard) 2 execute count 28 session cursor cache hits0 note hard parsing has stopped. iterate (third use of cursor) NAME VALUE - parse count (total) 27 parse count (hard) 2
RE: Cursor Sharing| Soft Parsing
* * * HAPPY DBA/SA APPRECIATION DAY!!! * * * Mr. Kyte's parsing explanation is also in this iss of OraMag. For the papyrus-impaired: http://www.oramag.com/oramag/oracle/02-jul/index.html?o42asktom.html (Be safe: Download page to laptop or PDA before taking to bathroom) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: MacGregor, Ian A. [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 25, 2002 4:58 PM To: Multiple recipients of list ORACLE-L Subject: RE: Cursor Sharing| Soft Parsing I checked the Tom Kyte site. A soft parse comprises two ... -- 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| Soft Parsing
Connor, Well, technically that answers the why... with Oracle's because I said so :) the REAL question is why 3 and not 2 or 4 or 10 or. ? Rachel --- Connor McDonald [EMAIL PROTECTED] wrote: RTM :-) Performance Guide 9.2 Caching Session Cursors (blah blah blah) Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. (more blah blah blah) hth connor --- [EMAIL PROTECTED] wrote: I wasn't aware of it requiring three calls before being useful. Why is that? Jared Cary Millsap [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/25/2002 07:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Cursor Sharing| Soft Parsing Well, three times, right? I think it takes three parse calls before session_cached_cursors begins to help. But 3 is still O(1). Once per call is O(#executions). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 23-25 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas -Original Message- Still Sent: Thursday, July 25, 2002 2:38 AM To: Multiple recipients of list ORACLE-L On Wednesday 24 July 2002 22:08, MacGregor, Ian A. wrote: Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. No, code that uses bind variables need only parse SQL statements once if session_cached_cursors is set. Further executions of the same SQL don't require a hard or soft parse. Jared When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:23 PM To: Multiple recipients of list ORACLE-L Mike, Kirti, Try page 441 CURSOR_SHARING=FORCE does improve badly written applications that use lots of literals. However coding should be done using bind variables in almost all occasions. CURSOR_SHARING=FORCE reduces the hard parsing. What CURSOR_SHARING=FORCE does is rewrites all queries to use bind variables before parsing. eg. select ename from emp where empno = 10; rewritten as select ename from emp where empno =:SYS_B_0 or in 8.1.6 , 8.1.7 select name from emp where empno =:SYS_B_0 So it substitutes the literal with bind variables but incurs the cost of soft parsing the statement. Soft Parsing too frequently limits the scalability of applications and sacrifices optimal performance which could have been achieved in the first place if written using bind variables. Parse once and execute as many times as we like. Also check out Bjorn's paper on bind variables and cursor sharing at http://technet.oracle.com/deploy/performance/pdf/cursor.pdf So CURSOR sharing is not the silver bullet as one may expect. Regards Suhen On Thu, 25 Jul 2002 10:23, you wrote: Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists === message truncated === __ Do You Yahoo!? Yahoo! Health - Feel better, live better http
Re: Cursor Sharing| Soft Parsing
On Wednesday 24 July 2002 22:08, MacGregor, Ian A. wrote: Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. No, code that uses bind variables need only parse SQL statements once if session_cached_cursors is set. Further executions of the same SQL don't require a hard or soft parse. Jared When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:23 PM To: Multiple recipients of list ORACLE-L Mike, Kirti, Try page 441 CURSOR_SHARING=FORCE does improve badly written applications that use lots of literals. However coding should be done using bind variables in almost all occasions. CURSOR_SHARING=FORCE reduces the hard parsing. What CURSOR_SHARING=FORCE does is rewrites all queries to use bind variables before parsing. eg. select ename from emp where empno = 10; rewritten as select ename from emp where empno =:SYS_B_0 or in 8.1.6 , 8.1.7 select name from emp where empno =:SYS_B_0 So it substitutes the literal with bind variables but incurs the cost of soft parsing the statement. Soft Parsing too frequently limits the scalability of applications and sacrifices optimal performance which could have been achieved in the first place if written using bind variables. Parse once and execute as many times as we like. Also check out Bjorn's paper on bind variables and cursor sharing at http://technet.oracle.com/deploy/performance/pdf/cursor.pdf So CURSOR sharing is not the silver bullet as one may expect. Regards Suhen On Thu, 25 Jul 2002 10:23, you wrote: Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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: Jared Still 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
Don, Sounds like you have a VP there who is willing to suffer pain to cure the disease. Many places don't have high-level people like that. Last place I worked I used CURSOR_SHARING=FORCE because the programmers didn't want to code prepared statements (most of the statements were select... where username = 'literal string'. I PROVED to them that they were the cause of the shared pool allocation errors by pulling the statements out of the sql area. Didn't matter, it would have put them behind schedule. VP agreed with the programmers, I put cursor sharing on. Solved the problem Of course, the complicated stuff the DBAs wrote worked slower but no one seemed to care Rachel --- Don Granaman [EMAIL PROTECTED] wrote: Actually, CURSOR_SHARING=FORCE is in the index. ;-) The short (?) story is that it is a crutch. It can provide some relief from applications that pour out tons of nearly identical SQL -varying only in literal values, but it can also cause some significant problems. Also, there are a lot of bugs with it in all the versions I've used it in (8.1.6.x and 8.1.7.x). My experience is that it (a) works and (b) helps significantly in about 30% of the system where I've tried it. Bugs include things like: 1) ... where col1='X'and col2='Y' (in 8.1.6.x, it substitutes wrong and generates an error. Fixed in 8.1.7) 2) If the first value in a bind list is a null, it can generate a process crash with an ORA-07445 (in 8.1.7.1.? at least) 3) Java thin clients can return wrong results (hearsay - from Stan Yelliot) --- Moral of the story - test it *very thoroughly*! It can also cause some significant problems, even when it works correctly. For example: *) It cures the most obvious symptoms, but not the disease. Developers often like to think otherwise and simply continue bad coding practices. CURSOR_SHARING still doesn't avoid a soft parse. Applications with lots of literals typically don't reuse cursors either. Partially masking serious design and coding flaws with magic bullets like CURSOR_SHARING=FORCE doesn't actually solve the much larger systemic problems. It is likely to buy you some time and fewer headaches with thrashing in the shared pool, but it still isn't very scalable in the long run. *) *ALL* literals get substituted. This can throw the optimizer off. Examples: (1) where ... and 1 = 2 (Oh! I don't really need to fetch any rows! isn't obvious.) (2) When a literal causes the optimizer to use histograms well. STATUS_CODE has possible values of 'OPEN and 'CLOSED'. 99% of all records have 'CLOSED', but 99% of all queries are for 'OPEN'. DBAs would gladly suffer an extra statement with literals rather than suffer a poor execution plan for 99% of the executions. I consider using CURSOR_SHARING=FORCE like I would consider using a tourniquet - its preferrable to sudden death, but it isn't applicable in every case and is rarely a great long term solution. About three days ago, I had this discussion with a (very technical) VP. *Everything* is written with literals. Every literal statement is prepare()ed. I explained the basic issues to him and his preference was not to use it. He wants to force a resolution of the deeper issues by letting the situation become so bad soon that it forces a better, more permanent solution - before the rapidly increasing transaction volume REALLY hits the fan. The CURSOR_SHARING=FORCE safety valve is something we are reserving as a last, temporary resort. Don Granaman [OraSaurus] BTW: Hi Mike! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 24, 2002 7:23 PM Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. 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
RE: Cursor Sharing| Soft Parsing
Well, three times, right? I think it takes three parse calls before session_cached_cursors begins to help. But 3 is still O(1). Once per call is O(#executions). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 23-25 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas -Original Message- Still Sent: Thursday, July 25, 2002 2:38 AM To: Multiple recipients of list ORACLE-L On Wednesday 24 July 2002 22:08, MacGregor, Ian A. wrote: Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. No, code that uses bind variables need only parse SQL statements once if session_cached_cursors is set. Further executions of the same SQL don't require a hard or soft parse. Jared When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:23 PM To: Multiple recipients of list ORACLE-L Mike, Kirti, Try page 441 CURSOR_SHARING=FORCE does improve badly written applications that use lots of literals. However coding should be done using bind variables in almost all occasions. CURSOR_SHARING=FORCE reduces the hard parsing. What CURSOR_SHARING=FORCE does is rewrites all queries to use bind variables before parsing. eg. select ename from emp where empno = 10; rewritten as select ename from emp where empno =:SYS_B_0 or in 8.1.6 , 8.1.7 select name from emp where empno =:SYS_B_0 So it substitutes the literal with bind variables but incurs the cost of soft parsing the statement. Soft Parsing too frequently limits the scalability of applications and sacrifices optimal performance which could have been achieved in the first place if written using bind variables. Parse once and execute as many times as we like. Also check out Bjorn's paper on bind variables and cursor sharing at http://technet.oracle.com/deploy/performance/pdf/cursor.pdf So CURSOR sharing is not the silver bullet as one may expect. Regards Suhen On Thu, 25 Jul 2002 10:23, you wrote: Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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: Jared Still 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: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Cursor Sharing| Soft Parsing
One thing is for sure . I will not be telling any developers about the Cursor_sharing parameter and I will continue to insist that they rewrite their application properly with bind variables. Sometimes these developers piss me off though. They are like lazy kids whose parents always bail them out. FWIW. Mike -Original Message- Sent: Thursday, July 25, 2002 7:58 AM To: Multiple recipients of list ORACLE-L Well, three times, right? I think it takes three parse calls before session_cached_cursors begins to help. But 3 is still O(1). Once per call is O(#executions). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 23-25 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas -Original Message- Still Sent: Thursday, July 25, 2002 2:38 AM To: Multiple recipients of list ORACLE-L On Wednesday 24 July 2002 22:08, MacGregor, Ian A. wrote: Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. No, code that uses bind variables need only parse SQL statements once if session_cached_cursors is set. Further executions of the same SQL don't require a hard or soft parse. Jared When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:23 PM To: Multiple recipients of list ORACLE-L Mike, Kirti, Try page 441 CURSOR_SHARING=FORCE does improve badly written applications that use lots of literals. However coding should be done using bind variables in almost all occasions. CURSOR_SHARING=FORCE reduces the hard parsing. What CURSOR_SHARING=FORCE does is rewrites all queries to use bind variables before parsing. eg. select ename from emp where empno = 10; rewritten as select ename from emp where empno =:SYS_B_0 or in 8.1.6 , 8.1.7 select name from emp where empno =:SYS_B_0 So it substitutes the literal with bind variables but incurs the cost of soft parsing the statement. Soft Parsing too frequently limits the scalability of applications and sacrifices optimal performance which could have been achieved in the first place if written using bind variables. Parse once and execute as many times as we like. Also check out Bjorn's paper on bind variables and cursor sharing at http://technet.oracle.com/deploy/performance/pdf/cursor.pdf So CURSOR sharing is not the silver bullet as one may expect. Regards Suhen On Thu, 25 Jul 2002 10:23, you wrote: Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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: Jared Still 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
RE: Cursor Sharing| Soft Parsing
Possibly burying myself deeper: Parsing is done at the open call. If a cursor needs to be re-opened, Oracle will check for permissions whether cursors are cached or not. Some experiments. First just using bind variables in the statement. SQL alter session set session_cached_cursors = 10; Session altered. SQL VARIABLE V_EMPNO NUMBER BEGIN :V_EMPNO := 7934; END; / SQL select ename from scott.emp where empno = :v_empno; ENAME -- MILL As this is the first statement. I would expect hard and soft parsing to be taking place. SQL BEGIN 2 :V_EMPNO := 7782; 3 END; 4 / PL/SQL procedure successfully completed. SQL select ename from scott.emp where empno = :v_empno; ENAME -- CLARK What type of parsing is done here. The statement is in the buffer pool - If scott revokes privileges and the above statement is rerun SQL / select ename from scott.emp where empno = :v_empno * ERROR at line 1: ORA-01031: insufficient privileges -- Scott restores privileges ... SQL variable my_select refcursor; SQL BEGIN 2 OPEN :my_select FOR SELECT ename from s 3 END; 4 / PL/SQL procedure successfully completed. SQL print my_select ENAME -- SMITH ALLEN WARD JONES MARTIN BLAKE SCOTT KING TURNER ADAMS JAMES FORD MILL CLARK 14 rows selected. -- Print closes the cursor. If scott revokes permisssions at this point. SQL BEGIN 2 OPEN :my_select FOR SELECT ename from scott.emp; 3 END; 4 / OPEN :my_select FOR SELECT ename from scott.emp; * ERROR at line 2: ORA-06550: line 2, column 45: PLS-00904: insufficient privilege to access object SCOTT.EMP ORA-06550: line 2, column 21: PL/SQL: SQL Statement ignored --- However if scott restores permissions SQL BEGIN 2 OPEN :my_select FOR SELECT ename from scott.emp; 3 END; 4 / PL/SQL procedure successfully completed. and now revokes them here. The print statement will still work SQL print my_sele ENAME -- SMITH ALLEN WARD JONES MARTIN BLAKE SCOTT KING TURNER ADAMS JAMES FORD MILL CLARK 14 rows selected. So how does one keep such cursors open. Given a cursor such as BEGIN OPEN :my_select FOR SELECT ename from scott.emp where empno = :v_empno; END; How does one display the information, change the value of :v_empno, and display the infromation again without re-opening the cursor. In the distant past when I was writing a lot of Pro*C I'd get the occaisional fetch out of sequence error when I would change the value of a bind variable and try to fetch without first opening the cursor. Doesn't one have to re-opne to rebind. N.B. mail sent in haste -- late for an appointment. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, July 25, 2002 12:38 AM To: Multiple recipients of list ORACLE-L On Wednesday 24 July 2002 22:08, MacGregor, Ian A. wrote: Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. No, code that uses bind variables need only parse SQL statements once if session_cached_cursors is set. Further executions of the same SQL don't require a hard or soft parse. Jared When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:23 PM To: Multiple recipients of list ORACLE-L Mike, Kirti, Try page 441 CURSOR_SHARING=FORCE does improve badly written applications that use lots of literals. However coding should be done using bind variables in almost all occasions. CURSOR_SHARING=FORCE reduces the hard parsing. What CURSOR_SHARING=FORCE does is rewrites all queries to use bind variables before parsing. eg. select ename from emp where empno = 10; rewritten as select ename from emp where empno =:SYS_B_0 or in 8.1.6 , 8.1.7 select name from emp where empno =:SYS_B_0 So it substitutes the literal with bind variables but incurs the cost of soft parsing the statement. Soft Parsing too frequently limits the scalability of applications and sacrifices optimal performance which could have been achieved in the first place if written using bind variables. Parse once and
RE: Cursor Sharing| Soft Parsing
No, code that uses bind variables need only parse SQL statements once if session_cached_cursors is set. Further executions of the same SQL don't require a hard or soft parse. Hmm read somewhere (James Morle?) that this may not apply if the (subsequent) bind variable sizes differ vastly from the initial. I would check that Jared (I know that you have JM's book and have actually read it!) I don't remember if this changes with session_cached_cursors. I ask because Apps is notorious for using bind variables that vastly differ (read: flexfields). John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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| Soft Parsing
When a SQL execution is requested the Shared SQL is first examined to see if the statement is in memory. The first time SQL is processed it goes through a hard parse, the most expensive parsing operation. A hard parse performs the following: checking syntax; validating all database objects referenced, (tables and columns); naming translation, (synonyms); authenticating user privileges on all tables and columns; producing a SQL execution plan via the optimizer; hashing and storing the parsed statement in the Shared SQL Area. If the SQL statement is found in the Shared Pool then a soft parse may be performed in an attempt to use a shareable cursor. There are three types of soft parses: 1) The first time a SQL statement is found in the shared pool Oracle performs name translation, user authentication, and adds the user to the authentication list. 2) On the second soft parse name translation does not need to be performed but user authentication does just in case user privileges were changed since the last execution.; 3) An entry is created for the session's cursor cache and future cursor CLOSEs are ignored. Once in the session cursor cache the SQL statement does not need to be reparsed. This gives a significant performance boost! Giving credit where due: The above was inspired from pages 277-280 in Scaling Oracle8i by James Morle. Steve Orr Bozeman, Montana -Original Message- Sent: Wednesday, July 24, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Importance: High Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. I thought the processing of the statement to check permissions to be soft parsing. But, perhaps I'm misinformed. When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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| Soft Parsing
we have a lot of delphi and forms3 apps and Session_Cached_Cursors is zero for my database (7.3.4). I am thinking of setting the above parameter to 50. Also would monitor the stat 'session cursor cache hits' before and after setting the parameter. Do i need to increase/decrease any other parameter with this change? btw found this bug [BUG:931820] Direct Load Fails When Session_Cached_Cursors is larger than 0 Pls advise if i am on a wrong track. Thanks Mandar -Original Message- Sent: Thursday, July 25, 2002 11:49 AM To: Multiple recipients of list ORACLE-L When a SQL execution is requested the Shared SQL is first examined to see if the statement is in memory. The first time SQL is processed it goes through a hard parse, the most expensive parsing operation. A hard parse performs the following: checking syntax; validating all database objects referenced, (tables and columns); naming translation, (synonyms); authenticating user privileges on all tables and columns; producing a SQL execution plan via the optimizer; hashing and storing the parsed statement in the Shared SQL Area. If the SQL statement is found in the Shared Pool then a soft parse may be performed in an attempt to use a shareable cursor. There are three types of soft parses: 1) The first time a SQL statement is found in the shared pool Oracle performs name translation, user authentication, and adds the user to the authentication list. 2) On the second soft parse name translation does not need to be performed but user authentication does just in case user privileges were changed since the last execution.; 3) An entry is created for the session's cursor cache and future cursor CLOSEs are ignored. Once in the session cursor cache the SQL statement does not need to be reparsed. This gives a significant performance boost! Giving credit where due: The above was inspired from pages 277-280 in Scaling Oracle8i by James Morle. Steve Orr Bozeman, Montana -Original Message- Sent: Wednesday, July 24, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Importance: High Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. I thought the processing of the statement to check permissions to be soft parsing. But, perhaps I'm misinformed. When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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: Mandar A. Ghosalkar 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| Soft Parsing
I checked the Tom Kyte site. A soft parse comprises two operations. One is a simple syntax check; e.g. select from dual; would fail this soft parse as it is missing a column list or a literal. The other portion of a soft parse what he calls a semantics check is checking to see if the tables and columns exist, that the person has the proper permissions, that there are no ambiguities. select deptno from emp, dept where emp.deptno = dept.deptno / would fail this type of parse. My Kyte's definition of a soft parse jibes nicely with the one I used earlier. I didn't include the syntactical error portion as the statements in question are all valid SQL. However it is just as important. Semantic and syntactical checks are done; i.e.., a soft parse is done before the cache is checked. Quoting from the article The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session. If it has ? we may be in luck here, we can skip the next two steps in the process, that of optimization and row source generation. If we can skip these next two steps in the process, we have done what is known as a Soft Parse. While writing this it has suddenly dawned on me what Suhen was talking about when said cursor_sharing = 'FORCE' avoids a hard parse at the cost of a soft. If this is set select * from emp where ename = 'KING'; will be soft parsed. It will be changed to select * from emp where ename = :bind_variable; This statement will undergo soft parsing again. If the statement can be found in cache; then no hard parsing is needed. The generation of the second SQL statement replacing the literal with a bind variables increases the likelihood of not having to hard parse. -- Now about session_cached_cursors. First checking the hits 1 select a.name, b.value 2 from v$sysstat a, v$sesstat b 3 where a.statistic# = b.statistic# 4 and a.statistic# = 191 5* and b.sid = 8 SQL / NAME VALUE - session cursor cache hits 10 running the statement 1* select ename from scott.emp where empno = :v_empno SQL / ENAME -- MILL If I run the query to ge the session cached cursors statement. I see it has been incremented. NAME VALUE - session cursor cache hits 11 now if I revoke the permissions on the table. I get SQL / select ename from scott.emp where empno = :v_empno * ERROR at line 1: ORA-01031: insufficient privileges --- Sure seems like the statement is undergoing a semantics check despite the availability of a cached cursor. The article posted by Tom Kyte, does not state that session_cached_cursors avoids soft parses. It says they make finding the cursor less expensive. Particularly the expense of latching the shared pool and the library cache. He runs a query 1000 times. Once without it being cached and again with it being cached and finds NAME RUN1 RUN2 DIFF -- -- -- LATCH.shared pool 2142 1097 -1045 LATCH.library cache 17361 2388 -14973 == The lesser latch count is for the query using session_cached cursors. Session_Cached_Cursors do save on resources and are important to scalability. But I have yet to see something which proves they stop soft parsing. I saw Steve' Orr's contribution An entry is created for the session's cursor cache and future cursor CLOSEs are ignored. Once in the session cursor cache the SQL statement does not need to be reparsed. This gives a significant performance boost! Giving credit where due: The above was inspired from pages 277-280 in Scaling Oracle8i by James Morle. I have posted material which refutes the above. Again how does one avoid the soft parsing? -Original Message- Sent: Wednesday, July 24, 2002 11:43 PM To: Multiple recipients of list ORACLE-L Ian, When coding you should parse once and
RE: Cursor Sharing| Soft Parsing
Interesting. Sometimes you've got to test things and not just believe what you read. now if I revoke the permissions on the table. Hmmm... if you modify a table all the associated shared SQL area is invalidated. I wonder if something like that is going on when you alter user privileges? Maybe the cached cursor is nolonger available? Sometimes trying to figure out what Oracle is doing is like smashing sub-atomic particles together at the speed of light. You deduce the way it was put together by the way it broke into pieces. Kind of crude but what else can you do without the source code of the creator? Steve Orr -Original Message- Sent: Thursday, July 25, 2002 3:58 PM To: Multiple recipients of list ORACLE-L Importance: High I checked the Tom Kyte site. A soft parse comprises two operations. One is a simple syntax check; e.g. select from dual; would fail this soft parse as it is missing a column list or a literal. The other portion of a soft parse what he calls a semantics check is checking to see if the tables and columns exist, that the person has the proper permissions, that there are no ambiguities. select deptno from emp, dept where emp.deptno = dept.deptno / would fail this type of parse. My Kyte's definition of a soft parse jibes nicely with the one I used earlier. I didn't include the syntactical error portion as the statements in question are all valid SQL. However it is just as important. Semantic and syntactical checks are done; i.e.., a soft parse is done before the cache is checked. Quoting from the article The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session. If it has ? we may be in luck here, we can skip the next two steps in the process, that of optimization and row source generation. If we can skip these next two steps in the process, we have done what is known as a Soft Parse. While writing this it has suddenly dawned on me what Suhen was talking about when said cursor_sharing = 'FORCE' avoids a hard parse at the cost of a soft. If this is set select * from emp where ename = 'KING'; will be soft parsed. It will be changed to select * from emp where ename = :bind_variable; This statement will undergo soft parsing again. If the statement can be found in cache; then no hard parsing is needed. The generation of the second SQL statement replacing the literal with a bind variables increases the likelihood of not having to hard parse. -- Now about session_cached_cursors. First checking the hits 1 select a.name, b.value 2 from v$sysstat a, v$sesstat b 3 where a.statistic# = b.statistic# 4 and a.statistic# = 191 5* and b.sid = 8 SQL / NAME VALUE - session cursor cache hits 10 running the statement 1* select ename from scott.emp where empno = :v_empno SQL / ENAME -- MILL If I run the query to ge the session cached cursors statement. I see it has been incremented. NAME VALUE - session cursor cache hits 11 now if I revoke the permissions on the table. I get SQL / select ename from scott.emp where empno = :v_empno * ERROR at line 1: ORA-01031: insufficient privileges --- Sure seems like the statement is undergoing a semantics check despite the availability of a cached cursor. The article posted by Tom Kyte, does not state that session_cached_cursors avoids soft parses. It says they make finding the cursor less expensive. Particularly the expense of latching the shared pool and the library cache. He runs a query 1000 times. Once without it being cached and again with it being cached and finds NAME RUN1 RUN2 DIFF -- -- -- LATCH.shared pool 2142 1097 -1045 LATCH.library cache 17361 2388 -14973 == The lesser latch count is for the query using session_cached cursors. Session_Cached_Cursors do
RE: Cursor Sharing| Soft Parsing
I didn't consider the invalidation possibilities. But here's more proof about Oracle still soft parsing with session_cached_cursors The following was run directly after session_cached_cursors was set to 10. select a.name, b.value from v$sysstat a, v$sesstat b where a.statistic# = b.statistic# and a.statistic# in (179, 180, 181, 191) and b.sid =16 / NAME VALUE - parse count (total) 12 parse count (hard) 0 execute count 12 session cursor cache hits0 --- The following SQL was executed select empno, ename, sal from scott.emp where empno = :v_empno; and the session stats showed NAME VALUE - parse count (total) 25 parse count (hard) 2 execute count 27 session cursor cache hits0 interate (2nd use of cursor) NAME VALUE - parse count (total) 26 parse count (hard) 2 execute count 28 session cursor cache hits0 note hard parsing has stopped. iterate (third use of cursor) NAME VALUE - parse count (total) 27 parse count (hard) 2 execute count 29 session cursor cache hits0 interate (4th use of cursor) SQL / NAME VALUE - parse count (total) 28 parse count (hard) 2 execute count 30 session cursor cache hits1 Hurray we finally got a cache cursor hit interate (5th use of cursor) NAME VALUE - parse count (total) 29 parse count (hard) 2 execute count 31 session cursor cache hits2 parse count is still increasing one last try interate twice (7th use of cursor) NAME VALUE - parse count (total) 31 parse count (hard) 2 execute count 33 session cursor cache hits4 At first I was ready to state that session_cached_cursors do not stop soft parsing, then after my initial experiment I was ready to assert. I now proclaim it. I also proclaim, A statement is always soft parsed before any attempt in made to find it in cache. Using session_cached_cursors greatly reduces the cost of this search. It does not however stop soft parsing. Again I await the proof to refute this proclamation. Ian MacGregor Stanford Linear Acclerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, July 25, 2002 4:43 PM To: Multiple recipients of list ORACLE-L Interesting. Sometimes you've got to test things and not just believe what you read. now if I revoke the permissions on the table. Hmmm... if you modify a table all the associated shared SQL area is invalidated. I wonder if something like that is going on when you
RE: Cursor Sharing| Soft Parsing
I wasn't aware of it requiring three calls before being useful. Why is that? Jared Cary Millsap [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/25/2002 07:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Cursor Sharing| Soft Parsing Well, three times, right? I think it takes three parse calls before session_cached_cursors begins to help. But 3 is still O(1). Once per call is O(#executions). Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 23-25 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas -Original Message- Still Sent: Thursday, July 25, 2002 2:38 AM To: Multiple recipients of list ORACLE-L On Wednesday 24 July 2002 22:08, MacGregor, Ian A. wrote: Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. No, code that uses bind variables need only parse SQL statements once if session_cached_cursors is set. Further executions of the same SQL don't require a hard or soft parse. Jared When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:23 PM To: Multiple recipients of list ORACLE-L Mike, Kirti, Try page 441 CURSOR_SHARING=FORCE does improve badly written applications that use lots of literals. However coding should be done using bind variables in almost all occasions. CURSOR_SHARING=FORCE reduces the hard parsing. What CURSOR_SHARING=FORCE does is rewrites all queries to use bind variables before parsing. eg. select ename from emp where empno = 10; rewritten as select ename from emp where empno =:SYS_B_0 or in 8.1.6 , 8.1.7 select name from emp where empno =:SYS_B_0 So it substitutes the literal with bind variables but incurs the cost of soft parsing the statement. Soft Parsing too frequently limits the scalability of applications and sacrifices optimal performance which could have been achieved in the first place if written using bind variables. Parse once and execute as many times as we like. Also check out Bjorn's paper on bind variables and cursor sharing at http://technet.oracle.com/deploy/performance/pdf/cursor.pdf So CURSOR sharing is not the silver bullet as one may expect. Regards Suhen On Thu, 25 Jul 2002 10:23, you wrote: Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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: Jared Still 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
RE: Cursor Sharing
Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. 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: Deshpande, Kirti 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
Mike, Kirti, Try page 441 CURSOR_SHARING=FORCE does improve badly written applications that use lots of literals. However coding should be done using bind variables in almost all occasions. CURSOR_SHARING=FORCE reduces the hard parsing. What CURSOR_SHARING=FORCE does is rewrites all queries to use bind variables before parsing. eg. select ename from emp where empno = 10; rewritten as select ename from emp where empno =:SYS_B_0 or in 8.1.6 , 8.1.7 select name from emp where empno =:SYS_B_0 So it substitutes the literal with bind variables but incurs the cost of soft parsing the statement. Soft Parsing too frequently limits the scalability of applications and sacrifices optimal performance which could have been achieved in the first place if written using bind variables. Parse once and execute as many times as we like. Also check out Bjorn's paper on bind variables and cursor sharing at http://technet.oracle.com/deploy/performance/pdf/cursor.pdf So CURSOR sharing is not the silver bullet as one may expect. Regards Suhen On Thu, 25 Jul 2002 10:23, you wrote: Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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
Kirti / Mike page 441 Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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| Soft Parsing
Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. I thought the processing of the statement to check permissions to be soft parsing. But, perhaps I'm misinformed. When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:23 PM To: Multiple recipients of list ORACLE-L Mike, Kirti, Try page 441 CURSOR_SHARING=FORCE does improve badly written applications that use lots of literals. However coding should be done using bind variables in almost all occasions. CURSOR_SHARING=FORCE reduces the hard parsing. What CURSOR_SHARING=FORCE does is rewrites all queries to use bind variables before parsing. eg. select ename from emp where empno = 10; rewritten as select ename from emp where empno =:SYS_B_0 or in 8.1.6 , 8.1.7 select name from emp where empno =:SYS_B_0 So it substitutes the literal with bind variables but incurs the cost of soft parsing the statement. Soft Parsing too frequently limits the scalability of applications and sacrifices optimal performance which could have been achieved in the first place if written using bind variables. Parse once and execute as many times as we like. Also check out Bjorn's paper on bind variables and cursor sharing at http://technet.oracle.com/deploy/performance/pdf/cursor.pdf So CURSOR sharing is not the silver bullet as one may expect. Regards Suhen On Thu, 25 Jul 2002 10:23, you wrote: Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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: MacGregor, Ian A. 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
Actually, CURSOR_SHARING=FORCE is in the index. ;-) The short (?) story is that it is a crutch. It can provide some relief from applications that pour out tons of nearly identical SQL -varying only in literal values, but it can also cause some significant problems. Also, there are a lot of bugs with it in all the versions I've used it in (8.1.6.x and 8.1.7.x). My experience is that it (a) works and (b) helps significantly in about 30% of the system where I've tried it. Bugs include things like: 1) ... where col1='X'and col2='Y' (in 8.1.6.x, it substitutes wrong and generates an error. Fixed in 8.1.7) 2) If the first value in a bind list is a null, it can generate a process crash with an ORA-07445 (in 8.1.7.1.? at least) 3) Java thin clients can return wrong results (hearsay - from Stan Yelliot) --- Moral of the story - test it *very thoroughly*! It can also cause some significant problems, even when it works correctly. For example: *) It cures the most obvious symptoms, but not the disease. Developers often like to think otherwise and simply continue bad coding practices. CURSOR_SHARING still doesn't avoid a soft parse. Applications with lots of literals typically don't reuse cursors either. Partially masking serious design and coding flaws with magic bullets like CURSOR_SHARING=FORCE doesn't actually solve the much larger systemic problems. It is likely to buy you some time and fewer headaches with thrashing in the shared pool, but it still isn't very scalable in the long run. *) *ALL* literals get substituted. This can throw the optimizer off. Examples: (1) where ... and 1 = 2 (Oh! I don't really need to fetch any rows! isn't obvious.) (2) When a literal causes the optimizer to use histograms well. STATUS_CODE has possible values of 'OPEN and 'CLOSED'. 99% of all records have 'CLOSED', but 99% of all queries are for 'OPEN'. DBAs would gladly suffer an extra statement with literals rather than suffer a poor execution plan for 99% of the executions. I consider using CURSOR_SHARING=FORCE like I would consider using a tourniquet - its preferrable to sudden death, but it isn't applicable in every case and is rarely a great long term solution. About three days ago, I had this discussion with a (very technical) VP. *Everything* is written with literals. Every literal statement is prepare()ed. I explained the basic issues to him and his preference was not to use it. He wants to force a resolution of the deeper issues by letting the situation become so bad soon that it forces a better, more permanent solution - before the rapidly increasing transaction volume REALLY hits the fan. The CURSOR_SHARING=FORCE safety valve is something we are reserving as a last, temporary resort. Don Granaman [OraSaurus] BTW: Hi Mike! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 24, 2002 7:23 PM Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. 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: Deshpande, Kirti 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
Re: Cursor Sharing
In 8.1.7 the best setting of CURSOR_SHARING is FARCE. Also, make sure that the hash sign is the first character on the line on which the parameter is set. On 2002.07.24 20:23 Deshpande, Kirti wrote: Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. 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: Deshpande, Kirti 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). -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala 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| Soft Parsing
Ian, When coding you should parse once and execute the query many times rather than loop parse bind execute close end; It can be seen that a parse operation is done on each iteration through the loop. You may have avoided hard parsing but the program is still soft parsing. It has to check the shared pool for the query executed each time. When coding u should rather parse loop bind execute end; close; So you would be parsing once and executing the query several times. Therefore reduction on latch contention which makes your application more scalable and hence better performance. Check out http://asktom.oracle.com/pls/ask/f?p=4950:8:1092060::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2588723819082,%7Bsoft%7D%20and%20%7Bparsing%7D Also see Bjorn's paper on bind variables Cheers Suhen Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. I thought the processing of the statement to check permissions to be soft parsing. But, perhaps I'm misinformed. When cursor-sharing converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:23 PM To: Multiple recipients of list ORACLE-L Mike, Kirti, Try page 441 CURSOR_SHARING=FORCE does improve badly written applications that use lots of literals. However coding should be done using bind variables in almost all occasions. CURSOR_SHARING=FORCE reduces the hard parsing. What CURSOR_SHARING=FORCE does is rewrites all queries to use bind variables before parsing. eg. select ename from emp where empno = 10; rewritten as select ename from emp where empno =:SYS_B_0 or in 8.1.6 , 8.1.7 select name from emp where empno =:SYS_B_0 So it substitutes the literal with bind variables but incurs the cost of soft parsing the statement. Soft Parsing too frequently limits the scalability of applications and sacrifices optimal performance which could have been achieved in the first place if written using bind variables. Parse once and execute as many times as we like. Also check out Bjorn's paper on bind variables and cursor sharing at http://technet.oracle.com/deploy/performance/pdf/cursor.pdf So CURSOR sharing is not the silver bullet as one may expect. Regards Suhen On Thu, 25 Jul 2002 10:23, you wrote: Mike, What is the version of the database? Some versions of 8.1.7 had a few bugs when this parameter was set to FORCE. I suggest searching Metalink. But it does work as advertised in later releases. I would also recommend reviewing Tom Kytes' book to read about his views in using this parameter at the instance level (my boss is reading my copy, so I can't give you page #s). - Kirti -Original Message- Sent: Wednesday, July 24, 2002 6:08 PM To: Multiple recipients of list ORACLE-L Has anyone set Cursor Sharing to Force ? I have a new system that we have to support and there is alot literals filling up the pool.I have never changed this parameter from the default as many seemed to think the jury was still out on it. However, due to my situation, I figured I would try it out. If anyone has any experience with this one I would be curious to know what happened. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather 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).
