TKPROF: Release 9.2.0.4.0 - Production on Wed Jun 18 10:17:59 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Sort options: exedsk fchdsk ******************************************************************************** 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 ******************************************************************************** INSERT INTO TT_TEMP_MAINGUI_SP_PERCENT_MOV (prev_prc_dt,asset_id,pricing_pt_id,price_dt) SELECT tpm2.prc_dt, tpm2.asset_id , tpm2.pricing_pt_id , tpm1.prc_dt FROM t_prc_master tpm1, t_prc_master tpm2 WHERE tpm1.prc_dt = '19-Dec-07' AND tpm2.asset_id = tpm1.asset_id AND tpm2.pricing_pt_id = tpm1.pricing_pt_id AND tpm1.accept_flg = 'Y' AND tpm2.accept_flg = 'Y' AND tpm2.prc_dt = ( SELECT MAX(prc_dt) FROM t_prc_master tpm3 WHERE tpm3.asset_id = tpm1.asset_id AND tpm3.pricing_pt_id = tpm1.pricing_pt_id AND tpm3.prc_dt < tpm1.prc_dt AND tpm3.accept_flg = 'Y') and exists (select 1 from tt_temp_prcmov where pca_flg = 'P' and prc_pt_cntry_atyp = tpm1.pricing_pt_id) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 88.90 94.95 408813 6894747 805935 780544 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 88.91 94.96 408813 6894747 805935 780544 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 98 (PRSDBO) Rows Row Source Operation ------- --------------------------------------------------- 780544 HASH JOIN SEMI (cr=6894392 r=408805 w=7230 time=90095967 us) 780544 NESTED LOOPS (cr=6894388 r=401573 w=0 time=83121308 us) 783459 TABLE ACCESS BY INDEX ROWID T_PRC_MASTER (cr=593790 r=24953 w=0 time=6516069 us) 784161 INDEX RANGE SCAN PRC_DT_ASSET_ID (cr=7493 r=75 w=0 time=378990 us)(object id 450059) 780544 INDEX RANGE SCAN ASSET_DT_ACCEPT_FLG (cr=2341687 r=14 w=0 time=3129196 us)(object id 450055) 55 TABLE ACCESS FULL TT_TEMP_PRCMOV (cr=4 r=2 w=0 time=310 us) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE 780544 HASH JOIN (SEMI) 780544 NESTED LOOPS 783459 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'T_PRC_MASTER' 784161 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PRC_DT_ASSET_ID' (NON-UNIQUE) 780544 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ASSET_DT_ACCEPT_FLG' (UNIQUE) 0 SORT (AGGREGATE) 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ASSET_DT_ACCEPT_FLG' (UNIQUE) 55 TABLE ACCESS (FULL) OF 'TT_TEMP_PRCMOV' ******************************************************************************** select file# from file$ where ts#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 7 0.00 0.00 0 0 0 0 Execute 7 0.00 0.00 0 0 0 0 Fetch 28 0.00 0.00 2 49 0 21 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 42 0.00 0.00 2 49 0 21 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 3 TABLE ACCESS BY INDEX ROWID FILE$ (cr=7 r=2 w=0 time=241 us) 3 INDEX RANGE SCAN I_FILE2 (cr=4 r=1 w=0 time=187 us)(object id 42) ******************************************************************************** BEGIN DBMS_OUTPUT.GET_LINE(:Buffer, :Status); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 6 0.00 0.00 0 0 0 0 Execute 6 0.00 0.00 0 0 0 6 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.00 0 0 0 6 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 98 (PRSDBO) ******************************************************************************** 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 2 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 goal: CHOOSE Parsing user id: 98 (PRSDBO) ******************************************************************************** ALTER SESSION SET timed_statistics = FALSE 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 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 98 (PRSDBO) ******************************************************************************** ALTER SESSION SET sql_trace = FALSE 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 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 98 (PRSDBO) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 11 0.01 0.00 0 0 0 0 Execute 13 88.90 94.95 408813 6894747 805935 780550 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 24 88.91 94.96 408813 6894747 805935 780550 Misses in library cache during parse: 1 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 7 0.00 0.00 0 0 0 0 Fetch 28 0.00 0.00 2 49 0 21 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 42 0.00 0.00 2 49 0 21 Misses in library cache during parse: 0 13 user SQL statements in session. 7 internal SQL statements in session. 20 SQL statements in session. 1 statement EXPLAINed in this session. ******************************************************************************** Trace file: /opt/oracle/duo017a/admin/udump/duo017a_ora_21401.trc Trace file compatibility: 9.00.01 Sort options: exedsk fchdsk 1 session in tracefile. 13 user SQL statements in trace file. 7 internal SQL statements in trace file. 20 SQL statements in trace file. 6 unique SQL statements in trace file. 1 SQL statements EXPLAINed using schema: PRSDBO.prof$plan_table Default table was used. Table was created. Table was dropped. 208 lines in trace file.