TKPROF: Release 11.2.0.4.0 - Development on Sun Sep 13 21:59:24 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Trace file: xscccp1_ora_14063.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: 8ju2p2kp6zbtr 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: SYS ******************************************************************************** SQL ID: d71xq0c4psw3a Plan Hash: 61601697 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("GRUP") FULL("GRUP") NO_PARALLEL_INDEX("GRUP") */ 1 AS C1, CASE WHEN "GRUP"."I_GRUP"=8722127 THEN 1 ELSE 0 END AS C2 FROM "SFDC_STAGING"."STG_IND_SALI_GRUP" SAMPLE --More--(11%) BLOCK (0.170205 , 1) SEED (1) "GRUP") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 12 0.00 0.00 0 0 0 0 Execute 12 0.00 0.00 0 0 0 0 Fetch 12 0.02 0.03 0 2532 0 12 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 36 0.02 0.03 0 2532 0 12 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS 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 SORT AGGREGATE (cr=211 pr=0 pw=0 time=2887 us) 15313 15313 15313 TABLE ACCESS STORAGE SAMPLE STG_IND_SALI_GRUP (cr=211 pr=0 pw=0 time=1191 us cost=26 size=289314 card=16073) --More--(18%) ******************************************************************************** SQL ID: 0f7r3sm4x87zn Plan Hash: 3809235398 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("GRAG") FULL("GRAG") NO_PARALLEL_INDEX("GRAG") */ 1 AS C1, CASE WHEN "GRAG"."D_RELEASE" IS NOT NULL THEN 1 ELSE 0 END AS C2 FROM "SFDC_STAGING"."STG_IND_SALI_GRAG" SAMPLE BLOCK (0.235709 , 1) SEED (1) "GRAG") SAMPLESUB 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 6 0.01 0.01 0 1110 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 18 0.01 0.01 0 1110 0 6 Misses in library cache during parse: 0 --More--(25%) Optimizer mode: ALL_ROWS 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 SORT AGGREGATE (cr=185 pr=0 pw=0 time=2725 us) 16402 16402 16402 TABLE ACCESS STORAGE SAMPLE STG_IND_SALI_GRAG (cr=185 pr=0 pw=0 time=925 us cost=26 size=330440 card=16522) ******************************************************************************** SELECT DISTINCT ACCT.*, USR.T_NAME, GRUP.I_GRUP, EMAL.T_E_MAIL, CUS2.C_TBCCO_STA T --USR.T_NAME, CUGR.I_GRUP, EMAL.T_E_MAIL, CUS2.C_TBCCO_STAT FROM SFDC_STAGING.STG_IND_SALI_PERSON_ACCNT ACCT, SFDC_STAGING.STG_IND_SALI_GRAG GRAG, SFDC_STAGING.STG_IND_SALI_USER USR, --SFDC_STAGING.STG_IND_SALI_CUGR CUGR, SFDC_STAGING.STG_IND_SALI_CUST CUST, --SFDC_STAGING.STG_IND_SALI_ADDR ADR, SFDC_STAGING.STG_IND_SALI_EMAIL EMAL, SFDC_STAGING.STG_IND_SALI_CUS2 CUS2, SFDC_STAGING.STG_IND_SALI_GRUP GRUP --More--(31%) WHERE ACCT.I_CUST = CUS2.I_CUST(+) AND ACCT.I_CUST = CUST.I_CUST --AND ADR.I_GRUP = CGR.I_GRUP AND ACCT.I_CUST = EMAL.I_CUST(+) --AND ACCT.I_CUST = CUGR.I_CUST --AND CUST.I_CUST = CUS2.I_CUST(+) --AND CGR.I_GRUP = GRP.I_GRUP AND CUST.I_CURR_GRUP = GRUP.I_GRUP AND GRAG.I_GRUP = GRUP.I_GRUP AND GRAG.I_AGNT = USR.I_AGNT AND ACCT.I_CUST IN (SELECT DISTINCT MIN(CUST.I_CUST) FROM SFDC_STAGING.STG_IND_SALI_CUST CUST, SFDC_STAGING.STG_IND_SALI_ADDR ADDR, SFDC_STAGING.STG_IND_SALI_EMAIL EMAL, SFDC_STAGING.STG_IND_SALI_GRUP GRUP WHERE CUST.I_CURR_GRUP = GRUP.I_GRUP AND ADDR.I_GRUP = GRUP.I_GRUP AND CUST.I_CUST = EMAL.I_CUST(+) AND CUST.C_STAT = 'A' AND ADDR.C_STAT = 'A' AND (EMAL.C_STAT = 'A' OR EMAL.C_STAT IS NULL) AND GRUP.I_GRUP IN (SELECT DISTINCT(GRAG.I_GRUP) FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG, SFDC_STAGING.STG_IND_SALI_GRUP GRUP WHERE GRAG.I_GRUP = GRUP.I_GRUP AND GRAG.D_RELEASE IS NOT NULL --ONLY GET RECS WITH NO OWNERSHIP AND GRAG.D_ESTABLISH IN (SELECT DISTINCT MAX(GRAG.D_ESTABLISH) --GET REC WITHH MOST RECENT ESTABLISHED DT FROM SFDC_STAGING.STG_IND_SALI_GRAG GRAG, SFDC_STAGING.STG_IND_SALI_GRUP GRUP WHERE GRAG.I_GRUP = GRUP.I_GRUP AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831' AND GRAG.I_GRUP = 8722127 GROUP BY GRAG.I_GRUP ) AND GRAG.I_GRUP = GRUP.I_GRUP AND TO_CHAR(GRUP.D_UPDT, 'YYYYMMDD') > '20130831' ) GROUP BY GRUP.I_GRUP ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.04 0.03 0 18 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.01 14 94 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.05 0.05 14 112 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- ---------------------------------------------- ----- 1 1 1 HASH UNIQUE (cr=94 pr=14 pw=0 time=15073 us co st=17 size=630 card=3) 70 70 70 NESTED LOOPS (cr=94 pr=14 pw=0 time=15123 us cost=16 size=630 card=3) 70 70 70 NESTED LOOPS (cr=93 pr=13 pw=0 time=14471 u s cost=16 size=630 card=11) 6 6 6 NESTED LOOPS (cr=85 pr=12 pw=0 time=13961 us cost=15 size=188 card=1) 1 1 1 NESTED LOOPS OUTER (cr=76 pr=12 pw=0 time= 13955 us cost=14 size=175 card=1) 1 1 1 NESTED LOOPS (cr=73 pr=12 pw=0 time=1394 6 us cost=13 size=147 card=1) 1 1 1 NESTED LOOPS (cr=70 pr=12 pw=0 time=139 35 us cost=12 size=141 card=1) 1 1 1 NESTED LOOPS OUTER (cr=67 pr=10 pw=0 ti me=13020 us cost=11 size=129 card=1) 1 1 1 NESTED LOOPS (cr=64 pr=10 pw=0 time=1 3008 us cost=10 size=121 card=1) 1 1 1 VIEW VW_NSO_2 (cr=60 pr=8 pw=0 time= 10988 us cost=9 size=13 card=1) 1 1 1 HASH GROUP BY (cr=60 pr=8 pw=0 time= 10986 us cost=9 size=84 card=1) 15 15 15 NESTED LOOPS (cr=60 pr=8 pw=0 time =7190 us cost=8 size=84 card=1) 15 15 15 NESTED LOOPS (cr=45 pr=7 pw=0 tim e=7086 us cost=8 size=84 card=1) 3 3 3 FILTER (cr=37 pr=6 pw=0 time=556 7 us) 3 3 3 NESTED LOOPS OUTER (cr=37 pr=6 p w=0 time=5515 us cost=7 size=76 card=1) 3 3 3 NESTED LOOPS (cr=28 pr=4 pw=0 time=3867 us cost=6 size=68 card=1) 1 1 1 NESTED LOOPS (cr=22 pr=2 pw=0 time=2420 us cost=5 size=54 card=1) 1 1 1 NESTED LOOPS (cr=19 pr=2 pw= 0 time=2407 us cost=4 size=37 card=1) 1 1 1 NESTED LOOPS (cr=16 pr=2 pw =0 time=2394 us cost=3 size=31 card=1) 1 1 1 VIEW VW_NSO_1 (cr=12 pr=1 pw=0 time=791 us cost=2 size=9 card=1) 1 1 1 HASH GROUP BY (cr=12 pr=1 pw=0 time=789 us cost=2 size=31 card=1) 6 6 6 MERGE JOIN CARTESIAN (cr= 12 pr=1 pw=0 time=498 us cost=2 size=31 card=1) 1 1 1 INDEX RANGE SCAN STG_IND _SALI_GRUP_PK3 (cr=3 pr=1 pw=0 time=417 us cost=1 size=17 card=1)(object id 2079 623) 6 6 6 BUFFER SORT (cr=9 pr=0 p w=0 time=75 us cost=1 size=84 card=6) 6 6 6 TABLE ACCESS BY INDEX R OWID STG_IND_SALI_GRAG (cr=9 pr=0 pw=0 time=32 us cost=1 size=84 card=6) 6 6 6 INDEX RANGE SCAN STG_I ND_SALI_GRAG_PK (cr=3 pr=0 pw=0 time=17 us cost=1 size=0 card=6)(object id 20796 12) 1 1 1 TABLE ACCESS BY INDEX ROWID STG_IND_SALI_GRAG (cr=4 pr=1 pw=0 time=1601 us cost=1 size=22 card=1) 1 1 1 INDEX RANGE SCAN STG_IND_S ALI_GRAG_PK4 (cr=3 pr=1 pw=0 time=1593 us cost=1 size=0 card=1)(object id 207963 3) 1 1 1 INDEX RANGE SCAN STG_IND_SAL I_GRUP_PK (cr=3 pr=0 pw=0 time=11 us cost=1 size=6 card=1)(object id 2079613) 1 1 1 INDEX RANGE SCAN STG_IND_SALI _GRUP_PK3 (cr=3 pr=0 pw=0 time=13 us cost=1 size=17 card=1)(object id 2079623) 3 3 3 TABLE ACCESS BY INDEX ROWID ST G_IND_SALI_CUST (cr=6 pr=2 pw=0 time=1485 us cost=1 size=14 card=1) 4 4 4 INDEX RANGE SCAN STG_IND_SALI _CUST_PK2 (cr=3 pr=1 pw=0 time=1470 us cost=1 size=0 card=1)(object id 2079615) 1 1 1 TABLE ACCESS BY INDEX ROWID STG _IND_SALI_EMAIL (cr=9 pr=2 pw=0 time=2100 us cost=1 size=8 card=1) 1 1 1 INDEX RANGE SCAN STG_IND_SALI_ EMAIL_PK (cr=8 pr=2 pw=0 time=2094 us cost=1 size=0 card=1)(object id 2079617) 15 15 15 INDEX RANGE SCAN STG_IND_SALI_ADD R_PK (cr=8 pr=1 pw=0 time=1576 us cost=1 size=0 card=1)(object id 2079589) 15 15 15 TABLE ACCESS BY INDEX ROWID STG_IN D_SALI_ADDR (cr=15 pr=1 pw=0 time=1595 us cost=1 size=8 card=1) 1 1 1 TABLE ACCESS BY INDEX ROWID STG_IND_S ALI_PERSON_ACCNT (cr=4 pr=2 pw=0 time=2019 us cost=1 size=108 card=1) 1 1 1 INDEX RANGE SCAN STG_IND_SALI_PA1_PK (cr=3 pr=2 pw=0 time=2000 us cost=1 size=0 card=1)(object id 2079624) 0 0 0 TABLE ACCESS BY INDEX ROWID STG_IND_SA LI_CUS2 (cr=3 pr=0 pw=0 time=8 us cost=1 size=8 card=1) 0 0 0 INDEX UNIQUE SCAN STG_IND_SALI_CUS2_P K (cr=3 pr=0 pw=0 time=7 us cost=1 size=0 card=1)(object id 2079590) 1 1 1 INDEX RANGE SCAN STG_IND_SALI_CUST_PK3 (cr=3 pr=2 pw=0 time=912 us cost=1 size=12 card=1)(object id 2079616) 1 1 1 INDEX RANGE SCAN STG_IND_SALI_GRUP_PK (c r=3 pr=0 pw=0 time=10 us cost=1 size=6 card=1)(object id 2079613) 0 0 0 TABLE ACCESS BY INDEX ROWID STG_IND_SALI_ EMAIL (cr=3 pr=0 pw=0 time=7 us cost=1 size=28 card=1) 0 0 0 INDEX RANGE SCAN STG_IND_SALI_EMAIL_PK ( cr=3 pr=0 pw=0 time=7 us cost=1 size=0 card=1)(object id 2079617) 6 6 6 TABLE ACCESS BY INDEX ROWID STG_IND_SALI_G RAG (cr=9 pr=0 pw=0 time=48 us cost=1 size=13 card=1) 6 6 6 INDEX RANGE SCAN STG_IND_SALI_GRAG_PK (cr =3 pr=0 pw=0 time=14 us cost=1 size=0 card=1)(object id 2079612) 70 70 70 INDEX RANGE SCAN STG_IND_SALI_USER_PK (cr=8 pr=1 pw=0 time=459 us cost=1 size=0 card=11)(object id 2079620) 70 70 70 TABLE ACCESS BY INDEX ROWID STG_IND_SALI_USE R (cr=1 pr=1 pw=0 time=459 us cost=1 size=66 card=3) ******************************************************************************** SQL ID: 9pvn3wt5gy2yn 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: SYS ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.04 0.03 0 18 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.01 14 94 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.05 0.05 14 112 0 1 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 18 0.00 0.00 0 0 0 0 Execute 18 0.00 0.00 0 0 0 0 Fetch 18 0.04 0.04 0 3642 0 18 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 54 0.04 0.04 0 3642 0 18 Misses in library cache during parse: 0 3 user SQL statements in session. 2 internal SQL statements in session. 5 SQL statements in session. 0 statements EXPLAINed in this session. ******************************************************************************** Trace file: xscccp1_ora_14063.trc Trace file compatibility: 11.1.0.7 Sort options: default 1 session in tracefile. 3 user SQL statements in trace file. 2 internal SQL statements in trace file. 5 SQL statements in trace file. 5 unique SQL statements in trace file. 233 lines in trace file. 66 elapsed seconds in trace file. [oracle@va10px0201 trace]$ us cost=6 size=68 card=1) -bash: syntax error near unexpected token `)' [oracle@va10px0201 trace]$ 1 1 1 NES TED LOOPS (cr=22 pr=2 pw=0 -bash: syntax error near unexpected token `(' [oracle@va10px0201 trace]$ time=2420 us cost=5 size=54 card=1) -bash: syntax error near unexpected token `)' [oracle@va10px0201 trace]$ 1 1 1 NE STED LOOPS (cr=19 pr=2 pw= -bash: syntax error near unexpected token `(' [oracle@va10px0201 trace]$ 0 time=2407 us cost=4 size=37 card=1) -bash: syntax error near unexpected token `)' [oracle@va10px0201 trace]$ 1 1 1 N ESTED LOOPS (cr=16 pr=2 pw -bash: syntax error near unexpected token `(' [oracle@va10px0201 trace]$ =0 time=2394 us cost=3 size=31 card=1) -bash: syntax error near unexpected token `)' [oracle@va10px0201 trace]$ 1 1 1 VIEW VW_NSO_1 (cr=12 pr=1 -bash: syntax error near unexpected token `(' [oracle@va10px0201 trace]$ pw=0 time=791 us cost=2 size=9 card=1) -bash: syntax error near unexpected token `)' [oracle@va10px0201 trace]$ 1 1 1 HASH GROUP BY (cr=12 pr=1 -bash: syntax error near unexpected token `(' [oracle@va10px0201 trace]$ pw=0 time=789 us cost=2 size=31 card=1) -bash: syntax error near unexpected token `)' [oracle@va10px0201 trace]$ 6 6 6 MERGE JOIN CARTESIAN (cr= -bash: syntax error near unexpected token `(' [oracle@va10px0201 trace]$ 12 pr=1 pw=0 time=498 us cost=2 size=31 card=1) -bash: syntax error near unexpected token `)' [oracle@va10px0201 trace]$ 1 1 1 INDEX RANGE SCAN STG_IND -bash: 1: command not found [oracle@va10px0201 trace]$ _SALI_GRUP_PK3 (cr=3 pr=1 pw=0 time=417 us cost=1 siz e=17 card=1)(object id 2079 -bash: syntax error near unexpected token `cr=3' [oracle@va10px0201 trace]$ 623) -bash: syntax error near unexpected token `)' [oracle@va10px0201 trace]$ 6 6 6 BUFFER SORT (cr=9 pr=0 p -bash: syntax error near unexpected token `(' [oracle@va10px0201 trace]$ w=0 time=75 us cost=1 size=84 card=6) -bash: syntax error near unexpected token `)' [oracle@va10px0201 trace]$ 6 6 6 TABLE ACCESS BY INDEX R -bash: 6: command not found [oracle@va10px0201 trace]$ OWID STG_IND_SALI_GRAG (cr=9 pr=0 pw=0 time=32 us cos t=1 size=84 card=6) -bash: syntax error near unexpected token `(' [oracle@va10px0201 trace]$ 6 6 6 INDEX RANGE SCAN STG_I -bash: 6: command not found [oracle@va10px0201 trace]$ ND_SALI_GRAG_PK (cr=3 pr=0 pw=0 time=17 us cost=1 siz e=0 card=6)(object id 20796 -bash: syntax error near unexpected token `cr=3' [oracle@va10px0201 trace]$ 12) -bash: syntax error near unexpected token `)' [oracle@va10px0201 trace]$ --More--(64%) -bash: syntax error near unexpected token `64%'