TKPROF: Release 11.2.0.4.0 - Development on Tue Dec 10 19:53:10 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Trace file: icareprod_ora_36.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 ******************************************************************************** SQL ID: 4kvc1pjyh5n7x Plan Hash: 0 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 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 90 ******************************************************************************** EXPLAIN PLAN FOR SELECT DISTINCT ( SELECT DESCRIPTION FROM DX_MST_ORGANISATION_LANG WHERE ORGCODE = DX_MST_FILE.CORRORGCODE AND LANGCODE = 'EN' ) AS CORRORNAME , T.CORRORGCODE , CURCODES , NVL(ATYPE,'N')ATYPE, CASE WHEN ATYPE = 'Y' THEN 'TT' WHEN ATYPE = 'N' THEN 'DD/CREDITCARD PAYMENT' END TYPE FROM DX_MST_FILE , ( SELECT O.CORRORGCODE FROM DX_TRNS_OUTGOING_DETAILS O, DX_TRNS_REMITTANCE R WHERE O.CORRORGCODE = R.CORRORGCODE AND R.SERVCODE NOT IN ( SELECT servcode FROM dx_mst_service WHERE categcode ='00010' AND servcode= R.SERVCODE ) AND O.REFNO = R.REFNO AND R.AUTHFLG = 'Y' AND R.CANCELIND = '0' AND R.TRANTYPE IN ('0','R') AND R.REFNO NOT IN ( SELECT REFNO FROM DX_TRNS_REJECTED_ENTRY WHERE TRANDATE BETWEEN TO_DATE('19/Nov/2019 00:00:00','dd/Mon/yyyy hh24:mi:ss') AND TO_DATE('19/Nov/2019 23:59:59','dd/Mon/yyyy hh24:mi:ss') ) AND O.TRANDATE BETWEEN TO_DATE('19/Nov/2019 00:00:00','dd/Mon/yyyy hh24:mi:ss') AND TO_DATE('19/Nov/2019 23:59:59','dd/Mon/yyyy hh24:mi:ss') AND O.DESPATCHFLAG = 'Y' AND O.FILEFLAG = 'N' AND O.CORRORGCODE IN ( SELECT CORRORGCODE FROM DX_MST_FILE WHERE ATYPE = 'Y' AND CORRORGCODE= O.CORRORGCODE ) ) T WHERE ACTIVEFLG = 'Y' AND DX_MST_FILE.CORRORGCODE = T.CORRORGCODE AND ORGCODE = '00001' ORDER BY CORRORNAME 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 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.01 0 0 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 90 ******************************************************************************** SQL ID: 99qa3zyarxvms Plan Hash: 0 insert into plan_table (statement_id, timestamp, operation, options, object_node, object_owner, object_name, object_instance, object_type, search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, object_alias, plan_id, depth, remarks, other_xml ) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21, :22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 18 0.00 0.00 0 7 24 18 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 19 0.00 0.00 0 7 24 18 Misses in library cache during parse: 1 Misses in library cache during execute: 6 Optimizer mode: ALL_ROWS Parsing user id: 90 (recursive depth: 1) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=62 us) ******************************************************************************** SQL ID: 15knr3nbjkrcw Plan Hash: 2501920895 SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL 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 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 0 0 1 Misses in library cache during parse: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 SEQUENCE ORA_PLAN_ID_SEQ$ (cr=0 pr=0 pw=0 time=11 us) 1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=1 us cost=2 size=0 card=1) ******************************************************************************** SQL ID: dbmdavb6ydwb4 Plan Hash: 0 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: 1 Optimizer mode: ALL_ROWS Parsing user id: 90 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.01 0.00 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.01 0.01 0 0 0 0 Misses in library cache during parse: 2 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 19 0.00 0.00 0 7 24 18 Fetch 1 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 22 0.00 0.00 0 7 24 19 Misses in library cache during parse: 2 Misses in library cache during execute: 6 4 user SQL statements in session. 1 internal SQL statements in session. 5 SQL statements in session. ******************************************************************************** Trace file: icareprod_ora_36.trc Trace file compatibility: 11.1.0.7 Sort options: default 1 session in tracefile. 4 user SQL statements in trace file. 1 internal SQL statements in trace file. 5 SQL statements in trace file. 5 unique SQL statements in trace file. 133 lines in trace file. 51 elapsed seconds in trace file.