******************************************************************************************************************* ******************************************************************************************************************* File Name :testenv_ora_12168 (contents of file testenv_ora_13286 starts at the end of testenv_ora_12168) cursor_sharing=force ******************************************************************************************************************* ******************************************************************************************************************* TKPROF: Release 9.2.0.8.0 - Production on Wed Apr 9 13:28:38 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Trace file: testenv_ora_12168.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:(49.2889) 2008-04-09 12:55:24.295 ******************************************************************************** begin for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='IGB') loop null; end loop; end; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.09 5 63 0 0 Execute 1 0.47 0.37 0 37944 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.49 0.46 5 38007 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 128 (APPSYS) 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 79.02 79.02 ******************************************************************************** SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR= 2007 AND STRBRANDCD='IGB' 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 9182 1.31 1.31 0 37944 0 9181 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9184 1.32 1.31 0 37944 0 9181 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 128 (APPSYS) (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 9181 TABLE ACCESS BY INDEX ROWID FIN_VOU_HDR 9181 INDEX RANGE SCAN XPKFIN_VOU_HDR (object id 457245) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 9181 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'FIN_VOU_HDR' 9181 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'XPKFIN_VOU_HDR' (UNIQUE) -------------------------------------------------------------------------------- begin for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='SAH') loop null; end loop; end; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 136.08 146.41 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 136.09 146.41 0 0 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 128 (APPSYS) 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 ******************************************************************************** SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR= 2007 AND STRBRANDCD='SAH' 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 2961242 191.37 1239.52 2635808 5525155 50 2961241 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2961244 191.37 1239.52 2635808 5525155 50 2961241 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 128 (APPSYS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'FIN_VOU_HDR' Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file scattered read 171655 2.52 1125.92 db file sequential read 1522 0.71 1.52 db file parallel read 1 0.38 0.38 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.03 0.09 5 63 0 0 Execute 2 136.55 146.78 0 37944 0 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 136.58 146.88 5 38007 0 2 Misses in library cache during parse: 2 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 1 79.02 79.02 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 13 0.01 0.01 0 1 0 0 Execute 16 0.01 0.00 0 0 0 0 Fetch 2970454 192.69 1240.91 2635813 5563164 50 2970446 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2970483 192.71 1240.92 2635813 5563165 50 2970446 Misses in library cache during parse: 13 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 1527 0.71 1.59 db file scattered read 171655 2.52 1125.92 db file parallel read 1 0.38 0.38 4 user SQL statements in session. 11 internal SQL statements in session. 15 SQL statements in session. 2 statements EXPLAINed in this session. ******************************************************************************** Trace file: testenv_ora_12168.trc Trace file compatibility: 9.02.00 Sort options: default 1 session in tracefile. 4 user SQL statements in trace file. 11 internal SQL statements in trace file. 15 SQL statements in trace file. 15 unique SQL statements in trace file. 2 SQL statements EXPLAINed using schema: APPSYS.prof$plan_table Default table was used. Table was created. Table was dropped. 3143870 lines in trace file. ******************************************************************************************************************* ******************************************************************************************************************* File Name : testenv_ora_13286 cursor_sharing=similar ******************************************************************************************************************* ******************************************************************************************************************* TKPROF: Release 9.2.0.8.0 - Production on Wed Apr 9 13:39:41 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Trace file: testenv_ora_13286.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:(49.2891) 2008-04-09 13:36:08.263 ******************************************************************************** begin for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='IGB') loop null; end loop; end; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.02 4 34 0 0 Execute 1 0.49 0.43 0 37944 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.50 0.45 4 37978 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 128 (APPSYS) 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 22.37 22.37 ******************************************************************************** SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR= 2007 AND STRBRANDCD='IGB' 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 9182 1.33 1.34 0 37944 0 9181 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9184 1.33 1.34 0 37944 0 9181 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 128 (APPSYS) (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 9181 TABLE ACCESS BY INDEX ROWID FIN_VOU_HDR 9181 INDEX RANGE SCAN XPKFIN_VOU_HDR (object id 457245) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 9181 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'FIN_VOU_HDR' 9181 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'XPKFIN_VOU_HDR' (UNIQUE) -------------------------------------------------------------------------------- begin for x in(select * from fin_vou_hdr where STRVOUTYPE ='JV' and STRVOUSERIES='CLM' and NFISCALYEAR=2007 and strbrandcd='SAH') loop null; end loop; end; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 128 (APPSYS) ******************************************************************************** SELECT * FROM FIN_VOU_HDR WHERE STRVOUTYPE ='JV' AND STRVOUSERIES='CLM' AND NFISCALYEAR= 2007 AND STRBRANDCD='SAH' 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: 128 (APPSYS) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'FIN_VOU_HDR' Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file scattered read 23317 0.50 174.72 db file sequential read 50 0.03 0.11 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.01 0.02 4 34 0 0 Execute 1 0.49 0.43 0 37944 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.50 0.46 4 37978 0 1 Misses in library cache during parse: 2 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 22.37 22.37 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 7 0.00 0.00 0 0 0 0 Execute 10 0.01 0.00 0 0 0 0 Fetch 9195 1.33 1.34 4 37981 0 9191 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9212 1.34 1.35 4 37981 0 9191 Misses in library cache during parse: 7 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 54 0.03 0.11 db file scattered read 23317 0.50 174.72 4 user SQL statements in session. 5 internal SQL statements in session. 9 SQL statements in session. 2 statements EXPLAINed in this session. ******************************************************************************** Trace file: testenv_ora_13286.trc Trace file compatibility: 9.02.00 Sort options: default 1 session in tracefile. 4 user SQL statements in trace file. 5 internal SQL statements in trace file. 9 SQL statements in trace file. 9 unique SQL statements in trace file. 2 SQL statements EXPLAINed using schema: APPSYS.prof$plan_table Default table was used. Table was created. Table was dropped. 32722 lines in trace file. ************************************************************************************************************** **************************************************************************************************************