Mark/Ryan, How about good ol' V$SYSSTAT and V$SESSTAT to get this information? Look at 'parse count (total)' and 'parse count (hard)' (resolve v$sesstat using v$statname). John Kanagaraj <>< DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-----Original Message----- >From: oracle-l-bounce@xxxxxxxxxxxxx >[mailto:oracle-l-bounce@xxxxxxxxxxxxx] >Sent: Tuesday, March 02, 2004 7:32 AM >To: oracle-l@xxxxxxxxxxxxx >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 -----------------------------------------------------------------