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: =?iso-8859-1?q?Connor=20McDonald?=
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).
