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

Jared Still wrote:

>Re the soft parse: this is essentially hashing the SQL
>and getting a hit in the lib cache. These can't be
>avoided.
>

Well, properly coded applications, that execute the same SQL statement over and over again are code, such that there are no - hard or soft - parses. Avoiding soft and hard parses is a very important step if you want your OLTP type application to scale.

I suggest you read the white paper I wrote while at Oracle on bind variables and cursor_sharing, which is found on http://otn.oracle.com/deploy/performance

Thanks, Bjørn.

>
>
>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: =?ISO-8859-1?Q?Bj=F8rn?= Engsig
  INET: bengsig_at_yahoo.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).