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?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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).
Received on Fri Feb 08 2002 - 03:15:20 CST
