TKPROF: Release 9.2.0.4.0 - Production on Wed Jun 18 10:01:30 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Trace file: /opt/oracle/duo017a/admin/udump/duo017a_ora_28085.trc 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 max(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 tpm1.asset_id = tpm2.asset_id AND tpm1.pricing_pt_id = tpm2.pricing_pt_id AND tpm2.prc_dt < tpm1.prc_dt AND tpm2.accept_flg = 'Y' AND tpm1.accept_flg = 'Y' AND EXISTS (SELECT 1 FROM t_temp_prcmov WHERE pca_flg = 'P' AND tpm1.pricing_pt_id = prc_pt_cntry_atyp) GROUP BY tpm2.asset_id, tpm2.pricing_pt_id,tpm1.prc_dt call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 226.01 317.50 1980173 4915655 805927 780544 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 226.01 317.51 1980173 4915655 805927 780544 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 98 (PRSDBO) Rows Row Source Operation ------- --------------------------------------------------- 780544 SORT GROUP BY (cr=4915236 r=1980165 w=0 time=312751120 us) 40416453 NESTED LOOPS (cr=4915236 r=1980165 w=0 time=245408132 us) 783459 NESTED LOOPS (cr=956325 r=92781 w=0 time=17974163 us) 55 TABLE ACCESS FULL T_TEMP_PRCMOV (cr=3 r=0 w=0 time=406 us) 783459 TABLE ACCESS BY INDEX ROWID T_PRC_MASTER (cr=956322 r=92781 w=0 time=17782856 us) 784161 INDEX RANGE SCAN PRC_DT_ASSET_ID (cr=412062 r=69776 w=0 time=14136725 us)(object id 450059) 40416453 INDEX RANGE SCAN ASSET_DT_ACCEPT_FLG (cr=3958911 r=1887384 w=0 time=217215303 us)(object id 450055) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT GOAL: CHOOSE 780544 SORT (GROUP BY) 40416453 NESTED LOOPS 783459 NESTED LOOPS 55 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T_TEMP_PRCMOV' 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) 40416453 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ASSET_DT_ACCEPT_FLG' (UNIQUE) ******************************************************************************** select file# from file$ where ts#=:1 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 0 Fetch 24 0.00 0.00 2 42 0 18 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 36 0.00 0.00 2 42 0 18 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=216 us) 3 INDEX RANGE SCAN I_FILE2 (cr=4 r=1 w=0 time=159 us)(object id 42) ******************************************************************************** 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.02 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.02 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 98 (PRSDBO) ******************************************************************************** BEGIN DBMS_OUTPUT.GET_LINE(:Buffer, :Status); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.00 0.00 0 0 0 3 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 0 0 3 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 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 goal: CHOOSE Parsing user id: 98 (PRSDBO) ******************************************************************************** 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 goal: CHOOSE Parsing user id: 98 (PRSDBO) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 6 0.00 0.00 0 0 0 0 Execute 7 226.01 317.52 1980173 4915655 805927 780547 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 13 226.01 317.53 1980173 4915655 805927 780547 Misses in library cache during parse: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS 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 0 Fetch 24 0.00 0.00 2 42 0 18 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 36 0.00 0.00 2 42 0 18 Misses in library cache during parse: 0 7 user SQL statements in session. 6 internal SQL statements in session. 13 SQL statements in session. 1 statement EXPLAINed in this session. ******************************************************************************** Trace file: /opt/oracle/duo017a/admin/udump/duo017a_ora_28085.trc Trace file compatibility: 9.00.01 Sort options: exedsk fchdsk 1 session in tracefile. 7 user SQL statements in trace file. 6 internal SQL statements in trace file. 13 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. 156 lines in trace file.