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).
