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