| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> high tkprof parse counts == real # of hard parses?
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).Received on Thu Feb 07 2002 - 22:47:18 CST
 |
 |