** Look at this SQL... select * from test1 where c1 like 'a' and c2= :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.07 0.07 0 321 0 0 Execute 3 0.00 0.00 0 11 0 0 Fetch 3 0.00 0.00 0 21 0 7 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.07 0.08 0 353 0 7 Misses in library cache during parse: 1 It was listed one once. It was soft parsed 3 times, hard parsed only once. ** TKPROF: Release 9.2.0.1.0 - Production on Thu Jul 3 09:19:22 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Trace file: athena_ora_1184.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** alter session set sql_trace=true call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 37 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.01 0.01 0 37 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer goal: CHOOSE Parsing user id: 61 ******************************************************************************** BEGIN :x:=test_func1_('like '||''''||'a'||'''',1); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.04 0.04 0 444 0 0 Execute 1 0.03 0.02 0 441 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.07 0.06 0 885 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 ******************************************************************************** BEGIN :x:=test_func1_('like '||''''||'a'||'''',2); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.01 0 0 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 ******************************************************************************** select * from test1 where c1 like 'a' and c2= :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.07 0.07 0 321 0 0 Execute 3 0.00 0.00 0 11 0 0 Fetch 3 0.00 0.00 0 21 0 7 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.07 0.08 0 353 0 7 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS FULL TEST1 ******************************************************************************** BEGIN :x:=test_func1_('like '||''''||'a'||'''',3); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 ******************************************************************************** BEGIN :x:=test_func1_('='||''''||'b'||'''',2); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 ******************************************************************************** select * from test1 where c1 ='b' and c2= :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 7 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 7 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS FULL TEST1 ******************************************************************************** BEGIN :x:=test_func1_('='||''''||'a'||'''',2); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.00 0 0 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 ******************************************************************************** select * from test1 where c1 ='a' and c2= :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 7 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 7 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS FULL TEST1 ******************************************************************************** BEGIN :x:=test_func1_('in('||''''||'a'||''''||','||''''||'b'||''''||')',2); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.02 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.02 0 0 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 ******************************************************************************** select * from test1 where c1 in('a','b') and c2= :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.01 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 7 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.03 0.01 0 7 0 4 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 4 TABLE ACCESS FULL TEST1 ******************************************************************************** alter session set sql_trace=false call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 61 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 7 0.04 0.04 0 444 0 0 Execute 8 0.09 0.09 0 478 0 6 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 15 0.14 0.14 0 922 0 6 Misses in library cache during parse: 7 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 82 0.21 0.22 0 562 0 0 Execute 217 0.03 0.04 0 11 0 0 Fetch 464 0.04 0.02 0 913 0 369 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 763 0.29 0.29 0 1486 0 369 Misses in library cache during parse: 30 14 user SQL statements in session. 76 internal SQL statements in session. 90 SQL statements in session. ******************************************************************************** Trace file: athena_ora_1184.trc Trace file compatibility: 9.00.01 Sort options: default 1 session in tracefile. 14 user SQL statements in trace file. 76 internal SQL statements in trace file. 90 SQL statements in trace file. 38 unique SQL statements in trace file. 1312 lines in trace file.