Re: high tkprof parse counts == real # of hard parses?

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_at_cust> col name format a30
> sys_at_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 <jmm_at_sublogic.com> 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 <jmm_at_sublogic.com>
> > 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: jmm_at_sublogic.com
> >
> > 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: ListGuru_at_fatcity.com (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: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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).