Please remember that there's one aspect of *any* parse call (hard, "soft", "softer", or otherwise) that cannot be avoided: the inter-dbcall latency that will show up in your extended SQL trace file as time consumed waiting for the event named 'SQL*Net message from client'. In many applications I see these days, the total latency from these events completely dominate the total end-user response time of the application. The ONLY way to fix this problem is to STOP THE APP from making unnecessary dbcalls. In this conversation, the topic at hand is parse calls. In many applications, using too small of an array fetch size or doing one-row-at-a-time inserts, updates, or deletes causes the same problem. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ryan Sent: Tuesday, March 02, 2004 8:30 PM To: oracle-l@xxxxxxxxxxxxx Subject: Re: RE: parse/execute ratio you got a link for bjorn ensig's article. I did a search and cant find it. ----- Original Message ----- From: <oracle-l-bounce@xxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Tuesday, March 02, 2004 10:31 AM Subject: RE: RE: parse/execute ratio > Ryan, > > This gets a bit hairy. I'll do my best, let me know if I lose you. (Others let me know if I put my foot in my mouth.) > > There are more than just soft and hard parses. First, if you have a brand new, totally unique SQL, that's definitely going to be a hard parse. > Once that's done, there are degrees of soft parses. First, perhaps the text of the SQL matches exactly, but it's submitted by a different user, > and so resolves to different objects. That's a different type of parse. Then, there are cases where the text matches, and everything resolves > to the same objects. If that keeps happening, you'll start getting session cursor cache hits. > > So, without getting into all the ugly details (which I'm not entirely sure I could totally elucidate without more digging and reading anyhow), > that's a bit of an oversimplification of how it can work in some cases. There are some good sources around for more detail, such as Steve Adams' book, > Bjorn Engsig's cursor sharing paper on OTN, etc. > > However, to answer your question: You can't absolutely definitively tell. Consider that V$SQL is at the child cursor level. So, AT MOST one of the > PARSE_CALLS was a full hard parse. The rest must have been some sort of hard parse. In fact, if the child_number = 0, then the very first parse was hard, > all others will some variant of soft parse. If child_number > 0, then the SQL was already there, so all parses attributed to this child must be some form > of soft parse. > > Hope that helps, > > -Mark > > -----Original Message----- > From: Bobak, Mark [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of > ryan.gaffuri@xxxxxxx > Sent: Tuesday, March 02, 2004 9:17 AM > To: oracle-l@xxxxxxxxxxxxx > Subject: Re: RE: parse/execute ratio > > > ok you lost me. soft parses get listed as parse_calls. How do we find out if there is only a soft parse and no hard parse? > > > > From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx> > > Date: 2004/03/02 Tue AM 09:12:52 EST > > To: <oracle-l@xxxxxxxxxxxxx> > > Subject: RE: parse/execute ratio > > > > Yes, that would be the ideal situation. > > > > But, don't forget, lots of apps will do one parse per execute. > > They will be soft parses, and perhaps even session cache cursor hits, > > but they still get counted as parses (or PARSE_CALLS). > > > > I'd say any child cursors whose EXECUTIONS is >1 and increasing is being > > reused. > > > > Hope that helps, > > > > -Mark > > > > > > -----Original Message----- > > From: ryan.gaffuri@xxxxxxx [mailto:ryan.gaffuri@xxxxxxx] > > Sent: Tue 3/2/2004 8:59 AM > > To: oracle-l@xxxxxxxxxxxxx > > Cc: > > Subject: parse/execute ratio > > To see if a query is being reused i can go to v$sql and look at parse_calls to executions right? parse_calls should be low and executions high? > > > > ---------------------------------------------------------------- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > ---------------------------------------------------------------- > > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > > ----------------------------------------------------------------- > > > > > > > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------