TKPROF: Release 10.2.0.1.0 - Production on Tue Aug 18 23:24:25 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Trace file: xe_ora_5485.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.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: 0 Optimizer mode: ALL_ROWS Parsing user id: 36 (FRAN) ******************************************************************************** BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 3 0 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 3 0 2 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 36 (FRAN) ******************************************************************************** declare v_start number ; v_hostalias hostname_mapping.hostalias%type ; v_enterprise_id hostname_mapping.enterprise_id%type ; v_hostname varchar2(255) ; Begin v_hostalias := 'BIS-INTERN' ; v_enterprise_id := 1 ; dbms_output.put_line('Beginn des Prozess NORMAL, der die Tabelle benutzt'); v_start := dbms_utility.get_time; for i in 1 .. 100000 loop v_hostname := bis_utils.get_host_url(v_hostalias, v_enterprise_id); end loop ; dbms_output.put_line('Ende des Prozess NORMAL. Zeit verbraucht : ' || round( (dbms_utility.get_time-v_start)/100, 2 ) || ' seconds' ); dbms_output.put_line('Beginn des Prozess NORMAL, der die Tabelle benutzt'); v_start := dbms_utility.get_time; for i in 1 .. 100000 loop v_hostname := bis_utils.get_host_url(v_hostalias, v_enterprise_id); end loop ; dbms_output.put_line('Ende des Prozess NORMAL. Zeit verbraucht : ' || round( (dbms_utility.get_time-v_start)/100, 2 ) || ' seconds' ); End ; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 10.91 21.02 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 10.91 21.02 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 36 (FRAN) ******************************************************************************** SELECT DECODE(H.PROTOCOL, NULL, '', '', '', H.PROTOCOL || '://') || H.HOSTNAME || DECODE(H.PORT, NULL, '', '', '', ':' || TO_CHAR(H.PORT)) FROM HOSTNAME_MAPPING H WHERE H.HOSTALIAS = UPPER(:B2 ) AND H.ENTERPRISE_ID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 200000 8.13 5.27 0 0 0 0 Fetch 200000 15.72 9.36 0 600000 0 200000 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 400001 23.85 14.63 0 600000 0 200000 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 36 (FRAN) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'HOSTNAME_MAPPING' (TABLE) ******************************************************************************** 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: 0 Optimizer mode: ALL_ROWS Parsing user id: 36 (FRAN) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 5 10.91 21.02 0 3 0 3 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 10.91 21.02 0 3 0 3 Misses in library cache during parse: 0 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 200000 8.13 5.27 0 0 0 0 Fetch 200000 15.72 9.36 0 600000 0 200000 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 400001 23.85 14.63 0 600000 0 200000 Misses in library cache during parse: 0 6 user SQL statements in session. 0 internal SQL statements in session. 6 SQL statements in session. 1 statement EXPLAINed in this session. ******************************************************************************** Trace file: xe_ora_5485.trc Trace file compatibility: 10.01.00 Sort options: default 1 session in tracefile. 6 user SQL statements in trace file. 0 internal SQL statements in trace file. 6 SQL statements in trace file. 5 unique SQL statements in trace file. 1 SQL statements EXPLAINed using schema: FRAN.prof$plan_table Default table was used. Table was created. Table was dropped. 400080 lines in trace file. 35 elapsed seconds in trace file.