TKPROF: Release 10.2.0.4.0 - Production on Tue Feb 8 12:53:58 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Trace file: dst1_ora_7369.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 -------------------------------------------------------------------------------- *** SESSION ID:(513.7406) 2011-02-08 12:53:03.213 ******************************************************************************** declare x number; begin for i in 1..4 loop select count(1) into x from tab1 where object_id=2331; --dbms_output.put_line(i); insert into tab2 values(i); commit; dbms_lock.sleep(6); end loop; end; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.01 0 0 0 0 Execute 1 0.07 23.52 1 1822 21 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.07 23.53 1 1822 21 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ PL/SQL lock timer 4 5.86 23.44 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 2.70 2.70 ******************************************************************************** SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TAB1") FULL("TAB1") NO_PARALLEL_INDEX("TAB1") */ :"SYS_B_2" AS C1, CASE WHEN "TAB1"."OBJECT_ID"= :"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "TAB1" SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7") SEED (:"SYS_B_8") "TAB1") SAMPLESUB 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 70 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 70 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 5 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=70 pr=0 pw=0 time=5467 us) 4803 TABLE ACCESS SAMPLE TAB1 (cr=70 pr=0 pw=0 time=4896 us) -------------------------------------------------------------------------------- SELECT COUNT(1) FROM TAB1 WHERE OBJECT_ID=2331 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 4 0.00 0.00 0 2 0 0 Fetch 4 0.03 0.03 0 1646 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.03 0.03 0 1649 0 4 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 5 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 2 SORT AGGREGATE (cr=4 pr=0 pw=0 time=73 us) 2 INDEX RANGE SCAN I (cr=4 pr=0 pw=0 time=47 us)(object id 4673976) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 6.84 6.84 -------------------------------------------------------------------------------- INSERT INTO TAB2 VALUES (:B1 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 1 17 4 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 1 17 4 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 5 (recursive depth: 1) -------------------------------------------------------------------------------- COMMIT call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 4 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 0 4 0 Misses in library cache during parse: 0 Parsing user id: 5 (recursive depth: 1) -------------------------------------------------------------------------------- SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TAB1") FULL("TAB1") NO_PARALLEL_INDEX("TAB1") */ :"SYS_B_2" AS C1, CASE WHEN "TAB1"."OBJECT_ID"= :"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "TAB1" SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7") SEED (:"SYS_B_8") "TAB1") SAMPLESUB 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 70 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 70 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 5 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=70 pr=0 pw=0 time=2376 us) 4803 TABLE ACCESS SAMPLE TAB1 (cr=70 pr=0 pw=0 time=4868 us) -------------------------------------------------------------------------------- SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), NVL(SUM(C3),:"SYS_B_2") FROM (SELECT /*+ NO_PARALLEL("TAB1") INDEX("TAB1" I) NO_PARALLEL_INDEX("TAB1") */ :"SYS_B_3" AS C1, :"SYS_B_4" AS C2, :"SYS_B_5" AS C3 FROM "TAB1" "TAB1" WHERE "TAB1"."OBJECT_ID"=:"SYS_B_6" AND ROWNUM <= :"SYS_B_7") SAMPLESUB 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 1 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 1 2 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 5 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=2 pr=1 pw=0 time=235 us) 1 VIEW (cr=2 pr=1 pw=0 time=219 us) 1 COUNT STOPKEY (cr=2 pr=1 pw=0 time=213 us) 1 INDEX RANGE SCAN I (cr=2 pr=1 pw=0 time=203 us)(object id 4673976) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 1 0.00 0.00 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.01 0 0 0 0 Execute 1 0.07 23.52 1 1822 21 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.07 23.53 1 1822 21 1 Misses in library cache during parse: 1 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 6.84 9.54 PL/SQL lock timer 4 5.86 23.44 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 14 0.00 0.00 0 1 0 0 Execute 27 0.01 0.03 0 3 21 4 Fetch 20 0.04 0.04 1 1821 0 16 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 61 0.07 0.08 1 1825 21 20 Misses in library cache during parse: 9 Misses in library cache during execute: 9 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 1 0.00 0.00 7 user SQL statements in session. 12 internal SQL statements in session. 19 SQL statements in session. ******************************************************************************** Trace file: dst1_ora_7369.trc Trace file compatibility: 10.01.00 Sort options: default 1 session in tracefile. 7 user SQL statements in trace file. 12 internal SQL statements in trace file. 19 SQL statements in trace file. 14 unique SQL statements in trace file. 524 lines in trace file. 23 elapsed seconds in trace file.