RE: Question On High Parse to execute ratio
Title: RE: Question On High Parse to execute ratio If you are using 9202 and intermedia, intermedia will choke on anything other than exact for cursor_sharing parameter ... and yes, they finally have a patch that works. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 3:39 PM To: Multiple recipients of list ORACLE-L Subject: Re: Question On High Parse to execute ratio Success with using CURSOR_SHARING depends on your Oracle version. In earlier versions it's buggy. Igor Neyman, OCP DBA [EMAIL PROTECTED] *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Question On High Parse to execute ratio
Success with using CURSOR_SHARING depends on your Oracle version. In earlier versions it's buggy. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 13, 2003 3:00 PM Hello: I have a third party application which seems to have alot of unbound SQL statements, several of which do not seem to be very effective queries. Unfortunatelly the code is locked. My Parse to execute ratio is around 72% and I was wondering if anyone has any ideas on how I can decrease this? I figure that I might start with using the parameter CURSOR_SHARING. Any ideas would be great. Thanks in advance, Jay _ Help STOP SPAM: Try the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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: Question On High Parse to execute ratio
Jay - Here are the possibilities: 1. The vendor is stupid. Or doesn't understand Oracle. 2. The product was incorrectly installed. Some products are sophisticated enough to have a configurable interface, which may be misconfigured or need tuning over time. 3. You are missing a key piece of knowledge. My point is, learn what you can about alternatives 2 and 3. Read the vendor documentation on the Oracle interface. Place a support call with the vendor. Try to locate a knowledgeable resource at the vendor. Find a support list. But if that fails, then you may be forced to fall back on an Oracle-only solution. CURSOR_SHARING is a high-risk solution that I would try after exhausting the simpler solutions. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Hello: I have a third party application which seems to have alot of unbound SQL statements, several of which do not seem to be very effective queries. Unfortunatelly the code is locked. My Parse to execute ratio is around 72% and I was wondering if anyone has any ideas on how I can decrease this? I figure that I might start with using the parameter CURSOR_SHARING. Any ideas would be great. Thanks in advance, Jay _ Help STOP SPAM: Try the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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: Question On High Parse to execute ratio
My colleagues call it CURSER_SHARING :))) Igor Neyman ineyman@perceTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ptron.comcc: Sent by: Subject: Re: Question On High Parse to execute ratio [EMAIL PROTECTED] om January 13, 2003 03:38 PM Please respond to ORACLE-L Success with using CURSOR_SHARING depends on your Oracle version. In earlier versions it's buggy. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 13, 2003 3:00 PM Hello: I have a third party application which seems to have alot of unbound SQL statements, several of which do not seem to be very effective queries. Unfortunatelly the code is locked. My Parse to execute ratio is around 72% and I was wondering if anyone has any ideas on how I can decrease this? I figure that I might start with using the parameter CURSOR_SHARING. Any ideas would be great. Thanks in advance, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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: Question On High Parse to execute ratio
Jay, Take a look into using stored outlines. If the problem is with the vendor's code not using bind variables, I don't know that this will help, but you may find some instances where it might be useful. I heard recently that a book should be written with just one chapter on bind variables.Chapter 1, Page 1: Use them! The End. Karen -Original Message- Sent: Monday, January 13, 2003 3:00 PM To: Multiple recipients of list ORACLE-L Hello: I have a third party application which seems to have alot of unbound SQL statements, several of which do not seem to be very effective queries. Unfortunatelly the code is locked. My Parse to execute ratio is around 72% and I was wondering if anyone has any ideas on how I can decrease this? I figure that I might start with using the parameter CURSOR_SHARING. Any ideas would be great. Thanks in advance, Jay _ Help STOP SPAM: Try the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karen Morton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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: Question On High Parse to execute ratio
In fact, in 8.1.7.2, it can cause *incorrect* results in certain queries without reporting an error! 8.1.7.4 is supposed to be a little more stable (applying that minipack to production as this is typed), and 9iR2 is reported to be even better. For us, it was CS=F or die (dead server, die trying to revamp 10Ks of lines of code, etc). We chose CS=F. It definitely is not as good as good code, but it is a pretty good stopgap, if you are aware of the risks! See Metalink for the warnings, especially in the BUG area. GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] Sent: Monday, January 13, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Subject: Re: Question On High Parse to execute ratio Success with using CURSOR_SHARING depends on your Oracle version. In earlier versions it's buggy. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 13, 2003 3:00 PM Hello: I have a third party application which seems to have alot of unbound SQL statements, several of which do not seem to be very effective queries. Unfortunatelly the code is locked. My Parse to execute ratio is around 72% and I was wondering if anyone has any ideas on how I can decrease this? I figure that I might start with using the parameter CURSOR_SHARING. Any ideas would be great. Thanks in advance, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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: Question On High Parse to execute ratio
CURSOR_SHARING won't reduce the parse count (total) count; only eliminating parse calls from your application can do that. Using CURSOR_SHARING will reduce the parse count (hard) count (and maybe some shared pool latch contention), but at the expense of more user-mode CPU consumed during each parse. CURSOR_SHARING is an okay workaround for the interim time while you're fixing an application, but to really fix a parsing problem in a permanent way (if you have a parsing problem that needs fixing) requires changing the application--either by changing its source code, or by intercepting its db parse calls and changing their behavior. Hello, Anjo? For more information, hit asktom.oracle.com. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas -Original Message- Sent: Monday, January 13, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Hello: I have a third party application which seems to have alot of unbound SQL statements, several of which do not seem to be very effective queries. Unfortunatelly the code is locked. My Parse to execute ratio is around 72% and I was wondering if anyone has any ideas on how I can decrease this? I figure that I might start with using the parameter CURSOR_SHARING. Any ideas would be great. Thanks in advance, Jay _ Help STOP SPAM: Try the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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: Question On High Parse to execute ratio
And/or get Bjorn Engsigs article (I think it's on MiracleAS.dk under Technical Information - called cursor.pdf) about some interesting results regarding cursor sharing. Mogens Cary Millsap wrote: CURSOR_SHARING won't reduce the parse count (total) count; only eliminating parse calls from your application can do that. Using CURSOR_SHARING will reduce the parse count (hard) count (and maybe some shared pool latch contention), but at the expense of more user-mode CPU consumed during each parse. CURSOR_SHARING is an okay workaround for the interim time while you're fixing an application, but to really fix a parsing problem in a permanent way (if you have a parsing problem that needs fixing) requires changing the application--either by changing its source code, or by intercepting its db parse calls and changing their behavior. Hello, Anjo? For more information, hit asktom.oracle.com. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas -Original Message- Sent: Monday, January 13, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Hello: I have a third party application which seems to have alot of unbound SQL statements, several of which do not seem to be very effective queries. Unfortunatelly the code is locked. My Parse to execute ratio is around 72% and I was wondering if anyone has any ideas on how I can decrease this? I figure that I might start with using the parameter CURSOR_SHARING. Any ideas would be great. Thanks in advance, Jay _ Help STOP SPAM: Try the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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: Question On High Parse to execute ratio
Thank you all for your help. I will have to review the metalink data as you suggested. We have asked that they Bind their SQL Statement but where given no patch date so it looks like we are out in the cold for awhile. Once again thanks for all your help. Jay - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 13, 2003 3:38 PM Jay - Here are the possibilities: 1. The vendor is stupid. Or doesn't understand Oracle. 2. The product was incorrectly installed. Some products are sophisticated enough to have a configurable interface, which may be misconfigured or need tuning over time. 3. You are missing a key piece of knowledge. My point is, learn what you can about alternatives 2 and 3. Read the vendor documentation on the Oracle interface. Place a support call with the vendor. Try to locate a knowledgeable resource at the vendor. Find a support list. But if that fails, then you may be forced to fall back on an Oracle-only solution. CURSOR_SHARING is a high-risk solution that I would try after exhausting the simpler solutions. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Hello: I have a third party application which seems to have alot of unbound SQL statements, several of which do not seem to be very effective queries. Unfortunatelly the code is locked. My Parse to execute ratio is around 72% and I was wondering if anyone has any ideas on how I can decrease this? I figure that I might start with using the parameter CURSOR_SHARING. Any ideas would be great. Thanks in advance, Jay _ Help STOP SPAM: Try the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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.net -- Author: Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [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).
