Re the soft parse: this is essentially hashing the SQL and getting a hit in the lib cache. These can't be avoided.
There may be more going on than that, but it's much less work than a hard parse. Jared On Friday 08 February 2002 00:58, you wrote: > v$sesstat splits this down into hard and soft parses. > > sys@cust> col name format a30 > sys@cust> select * from v$statname > 2 where name like '%parse%' > 3 / > > STATISTIC# NAME CLASS > ---------- ------------------------------ ---------- > 177 parse time cpu 64 > 178 parse time elapsed 64 > 179 parse count (total) 64 > 180 parse count (hard) 64 > > You'll probably find that most are soft parses - still > not great, but nowhere near as bad as hard ones. > > hth > connor > > --- James Manning <[EMAIL PROTECTED]> wrote: > I've > seen what looks to be conflicting evidence, so > > > I'm trying > > to get a handle on whether the tkprof output I'm > > seeing with the > > parse counts == execution counts is "real" or not: > > > > What gets me is that it even happens for "static" > > statements that > > are happening in triggers - like this statement > > that's part of > > a insert trigger for a table to set the PK from a > > seq. > > *************************************************************************** >***** > > > SELECT OBL_ID_SEQ.NEXTVAL > > FROM > > DUAL > > > > > > call count cpu elapsed disk > > query current rows > > ------- ------ -------- ---------- ---------- > > ---------- ---------- ---------- > > Parse 7953 2.21 2.17 0 > > 0 0 0 > > Execute 7953 1.18 1.24 0 > > 0 0 0 > > Fetch 7953 1.25 1.22 0 > > 7953 31821 7953 > > ------- ------ -------- ---------- ---------- > > ---------- ---------- ---------- > > total 23859 4.64 4.63 0 > > 7953 31821 7953 > > > > Misses in library cache during parse: 0 > > Optimizer goal: CHOOSE > > Parsing user id: 36 (PR_SCHEMA) (recursive depth: > > 2) > > > > Rows Execution Plan > > ------- > > --------------------------------------------------- > > 0 SELECT STATEMENT GOAL: CHOOSE > > 0 SEQUENCE OF 'OBL_ID_SEQ' > > 0 TABLE ACCESS (FULL) OF 'DUAL' > > *************************************************************************** >***** > > > I wanna believe the Parse count is wrong, but the > > elapsed and CPU time > > are way too high to believe that it's actually only > > be parsed once :( > > > > My shared pool is around 123MB at the moment - any > > way to definitely check > > whether that's the issue? seems like it shouldn't > > be given that size. > > > > Here's a statement called from a jdbc client that > > has the same problem > > (my sql traces show the vast majority of sql > > strings, called from clients > > or from inside pl/sql, are having this issue) > > *************************************************************************** >***** > > > select RANK_AVAIL_SECONDS > > from > > overall_stats where USR_LOGNAME=:1 > > > > > > call count cpu elapsed disk > > query current rows > > ------- ------ -------- ---------- ---------- > > ---------- ---------- ---------- > > Parse 464 0.09 0.11 0 > > 0 0 0 > > Execute 464 0.12 0.11 0 > > 0 0 0 > > Fetch 464 0.03 0.03 0 > > 1393 0 464 > > ------- ------ -------- ---------- ---------- > > ---------- ---------- ---------- > > total 1392 0.24 0.25 0 > > 1393 0 464 > > > > Misses in library cache during parse: 1 > > Optimizer goal: CHOOSE > > Parsing user id: 36 (PR_SCHEMA) > > > > Rows Row Source Operation > > ------- > > --------------------------------------------------- > > 1 TABLE ACCESS BY INDEX ROWID OVERALL_STATS > > 2 INDEX RANGE SCAN (object id 68692) > > > > > > Rows Execution Plan > > ------- > > --------------------------------------------------- > > 0 SELECT STATEMENT GOAL: CHOOSE > > 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX > > ROWID) OF > > 'OVERALL_STATS' > > 2 INDEX (RANGE SCAN) OF 'OSTATS_LOGN_IDX' > > (NON-UNIQUE) > > *************************************************************************** >***** > > > I certainly appreciate any pointers or insights that > > can be provided - I just > > don't get why all these reparse's are happening. > > > > In case it helps any, I *do* see these statements in > > the v$sql view - in fact > > the second one had high enough buffer_gets that I > > figured out a missing index :) > > > > Thanks! > > > > James > > -- > > James Manning <[EMAIL PROTECTED]> > > GPG Key fingerprint = B913 2FBD 14A9 CE18 B2B7 9C8E > > A0BF B026 EEBB F6E4 > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: James Manning > > 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). > > ===== > Connor McDonald > http://www.oracledba.co.uk (mirrored at > http://www.oradba.freeserve.co.uk) > > "Some days you're the pigeon, some days you're the statue" > > __________________________________________________ > Do You Yahoo!? > Everything you'll ever need on one web page > from News and Sport to Email and Music Charts > http://uk.my.yahoo.com -- 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).
