Hello all,
I have an insert issue, which is taking too much.
Please help me establish any tunning or maybe query redifining.
Here are my specifications
Server details
Server Description:
Windows Server 2003 R2
6GB of RAM
Intel Xeon 3.0 GHz (4CPUs)
DB Version
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
This is my query:
insert /*+ append parallel(x,4)*/into TRANS_UNITS x -- maybe nologing here
SELECT /*+ parallel(ca,4) parallel(coa,4) parallel(ch,4) parallel(rh,4) parallel(rp,4) parallel(serv,4) parallel(csc,4) parallel(dn,4) parallel(sm,4) parallel(p,4) parallel(cd,4) parallel(d,4)*/
-- I have often gotten errors
DISTINCT
CASE WHEN CSC.field_1 = 1 THEN 'P' ELSE 'S' END AS AST_LEVEL,
CASE WHEN CSC.field_2 = 1 THEN RP.DES
WHEN CSC.field_1 IN (4,6) THEN SERV.DES
END AS AST_PROD_NAME,
CASE WHEN CH.CH_STATUS = 'a' THEN 'short_1'
WHEN CH.CH_STATUS = 'd' THEN 'short_2'
WHEN CH.CH_STATUS = 's' THEN 'short_3'
WHEN CH.CH_STATUS = 'o' THEN 'short_4'
END AS AST_STATUS_CD,
NULL AS AST_SUB_STATUS,
COA.CO,
DN.DN,
SM.SM_f1,
SM.SM_f2,
TO_DATE(rh.TM_DATE) AS AST_START_DT,
NULL AS AST_END_DT,
D.CCE,
CASE WHEN COA.CO_ITEMIZED ='X' THEN 'Detail' ELSE 'Summary' END AS AST_TYPE,
CASE WHEN CA.CSLEVEL = 40 THEN CA.CUSTOMER_ID ELSE NULL END AS AST_LINE_USER,
CASE WHEN INSTR(ca.CUSTCODE,'.',1,4) = 0 THEN ca.CUSTCODE ELSE SUBSTR(ca.CUSTCODE,1,INSTR(ca.CUSTCODE,'.',1,4)-1) END AS CC,
CASE WHEN INSTR(ca.CUSTCODE,'.',1,3) = 0 THEN ca.CUSTCODE ELSE SUBSTR(ca.CUSTCODE,1,INSTR(ca.CUSTCODE,'.',1,3)-1) END AS DIV,
CASE WHEN INSTR(ca.CUSTCODE,'.',1,2) = 0 THEN ca.CUSTCODE ELSE SUBSTR(ca.CUSTCODE,1,INSTR(ca.CUSTCODE,'.',1,2)-1) END AS LAC ,
CA.CUSTCODE
FROM customer_all@first_dblink.world ca, -- big table
contract_all@first_dblink.world coa, -- big table
rateplan@first_dblink.world rp,
CONTRACT_HISTORY@first_dblink.world CH, --big table
mpusntab@first_dblink.world serv,
contr_services_cap@first_dblink.world csc,
directory_number@first_dblink.world dn, --big table
storage_medium@first_dblink.world sm,
port@first_dblink.world p,
contr_devices@first_dblink.world cd,
rateplan_hist@first_dblink.world rh, --big table
sysadm.dealer@first_dblink.world d -- view
WHERE CA.CUSTOMER_ID = COA.CUSTOMER_ID
AND SM.DEALER_ID = D.CUSTOMER_ID
AND COA.TMCODE = RP.TMCODE
AND COA.CO_ID = CH.CO_ID
AND RH.TMCODE = COA.TMCODE
AND RH.CO_ID = COA.CO_ID
AND csc.sncode = serv.sncode
AND CH.CH_SEQNO IN (SELECT MAX(CH1.CH_SEQNO ) FROM CONTRACT_HISTORY@first_dblink.world CH1 WHERE CH.CO_ID = CH1.CO_ID GROUP BY ch1.co_id)
AND SM.REC_VERSION IN ((SELECT MAX(sm1.REC_VERSION ) FROM storage_medium@first_dblink.world sm1 WHERE SM.SM_SERIALNUM = sm1.SM_SERIALNUM GROUP BY sm1.sm_serialnum ))
AND rh.SEQNO IN (SELECT MAX(rh1.SEQNO ) FROM rateplan_hist@first_dblink.world rh1 WHERE rh.CO_ID = rh1.CO_ID GROUP BY rh1.co_id)
AND CSC.CO_ID = COA.CO_ID
AND CSC.DN_ID = DN.DN_ID
AND CD.PORT_ID = P.PORT_ID
AND SM.SM_ID = P.SM_ID
AND CD.CO_ID = coa.co_id
AND ca.cstype = 'a'
AND CSC.SNCODE IN (1,4,6)
AND SM.SM_STATUS = 'a'
AND dn.dn_status='a'
AND Ca.custcode LIKE '1.%'
AND csc.seqno IN (SELECT MAX(csc1.seqno) FROM contr_services_cap@first_dblink.world csc1 WHERE csc1.co_id=csc.co_id AND csc1.sncode=csc.sncode GROUP BY csc1.co_id, csc1.sncode);
And this is the explain plan for my query
Plan hash value: 811001390
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 470 | 896 (1)| 00:00:11 | | |
| 1 | LOAD AS SELECT | BI_ASSETS_RATEPLAN | | | | | | |
| 2 | HASH UNIQUE | | 1 | 470 | 896 (1)| 00:00:11 | | |
|* 3 | FILTER | | | | | | | |
| 4 | NESTED LOOPS | | 2 | 940 | 882 (1)| 00:00:11 | | |
| 5 | NESTED LOOPS | | 1 | 442 | 879 (1)| 00:00:11 | | |
| 6 | NESTED LOOPS | | 1 | 415 | 877 (1)| 00:00:11 | | |
| 7 | NESTED LOOPS | | 1 | 385 | 876 (1)| 00:00:11 | | |
| 8 | NESTED LOOPS | | 1 | 318 | 874 (1)| 00:00:11 | | |
| 9 | NESTED LOOPS | | 4 | 960 | 866 (1)| 00:00:11 | | |
| 10 | NESTED LOOPS | | 4 | 856 | 858 (1)| 00:00:11 | | |
| 11 | NESTED LOOPS | | 4 | 752 | 845 (1)| 00:00:11 | | |
|* 12 | HASH JOIN | | 6 | 918 | 830 (1)| 00:00:10 | | |
| 13 | REMOTE | | 88 | 2816 | 19 (0)| 00:00:01 | BSCSDB | R->S |
| 14 | REMOTE | MPUSNTAB | 3 | 90 | 2 (0)| 00:00:01 | BSCSDB | R->S |
| 15 | REMOTE | RATEPLAN_HIST | 1 | 35 | 3 (0)| 00:00:01 | BSCSDB | R->S |
| 16 | REMOTE | CONTR_DEVICES | 1 | 26 | 3 (0)| 00:00:01 | BSCSDB | R->S |
| 17 | REMOTE | PORT | 1 | 26 | 2 (0)| 00:00:01 | BSCSDB | R->S |
| 18 | REMOTE | STORAGE_MEDIUM | 1 | 78 | 2 (0)| 00:00:01 | BSCSDB | R->S |
| 19 | REMOTE | DIRECTORY_NUMBER | 1 | 67 | 2 (0)| 00:00:01 | BSCSDB | R->S |
| 20 | REMOTE | RATEPLAN | 1 | 30 | 1 (0)| 00:00:01 | BSCSDB | R->S |
| 21 | REMOTE | DEALER | 1 | 27 | 2 (0)| 00:00:01 | BSCSDB | R->S |
| 22 | REMOTE | CONTRACT_HISTORY | 3 | 84 | 3 (0)| 00:00:01 | BSCSDB | R->S |
|* 23 | FILTER | | | | | | | |
| 24 | SORT GROUP BY NOSORT | | 1 | 26 | 3 (0)| 00:00:01 | | |
| 25 | REMOTE | CONTRACT_HISTORY | 3 | 78 | 3 (0)| 00:00:01 | BSCSDB | R->S |
|* 26 | FILTER | | | | | | | |
| 27 | SORT GROUP BY NOSORT | | 1 | 40 | 4 (0)| 00:00:01 | | |
| 28 | REMOTE | STORAGE_MEDIUM | 1 | 40 | 4 (0)| 00:00:01 | BSCSDB | R->S |
|* 29 | FILTER | | | | | | | |
| 30 | SORT GROUP BY NOSORT | | 1 | 26 | 3 (0)| 00:00:01 | | |
| 31 | REMOTE | RATEPLAN_HIST | 1 | 26 | 3 (0)| 00:00:01 | BSCSDB | R->S |
|* 32 | FILTER | | | | | | | |
| 33 | SORT GROUP BY NOSORT| | 1 | 39 | 3 (0)| 00:00:01 | | |
| 34 | REMOTE | CONTR_SERVICES_CAP | 1 | 39 | 3 (0)| 00:00:01 | BSCSDB | R->S |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter( EXISTS (???)
12 - access("CSC"."SNCODE"="SERV"."SNCODE")
23 - filter(MAX("CH1"."CH_SEQNO")=:B1)
26 - filter(MAX("SM1"."REC_VERSION")=:B1)
29 - filter(MAX("RH1"."SEQNO")=:B1)
32 - filter(MAX("CSC1"."SEQNO")=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
13 - SELECT "A1"."CSLEVEL","A1"."CUSTOMER_ID","A1"."CUSTCODE","A1"."CUSTCODE","A1"."CUSTCODE","A1"."CUST
CODE","A1"."CUSTCODE","A1"."CUSTCODE","A1"."CUSTCODE","A1"."CUSTCODE","A1"."CUSTCODE","A1"."CUSTCODE","A1"
."CUSTCODE","A1"."CUSTCODE","A1"."CUSTCODE","A1"."CUSTOMER_ID","A1"."CSTYPE","A1"."CUSTCODE","A2"."CO_ID",
"A2"."CO_ITEMIZED_BILL","A2"."CUSTOMER_ID","A2"."TMCODE","A2"."CO_ID","A2"."TMCODE","A2"."CO_ID","A2"."CO_
ID","A2"."CO_ID","A3"."SNCODE","A3"."SNCODE","A3"."SNCODE","A3"."SNCODE","A3"."CO_ID","A3"."DN_ID","A3"."S
NCODE","A3"."SEQNO","A3"."CO_ID","A3"."SNCODE" FROM "CUSTOMER_ALL" "A1","CONTRACT_ALL"
"A2","CONTR_SERVICES_CAP" "A3" WHERE ("A3"."SNCODE"=1 OR "A3"."SNCODE"=4 OR "A3"."SNCODE"=6) AND
"A3"."CO_ID"="A2"."CO_ID" AND "A1"."CUSTOMER_ID"="A2"."CUSTOMER_ID" AND "A1"."CSTYPE"='a' AND
"A1"."CUSTCODE" LIKE '1.%' (accessing 'BSCSDB.WORLD' )
14 - SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("SERV",4) */ "SNCODE","DES" FROM "MPUSNTAB" "SERV" WHERE
"SNCODE"=1 OR "SNCODE"=4 OR "SNCODE"=6 (accessing 'BSCSDB.WORLD' )
15 - SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("SYS_ALIAS_3",4) */ "CO_ID","SEQNO","TMCODE","TMCODE_DATE"
FROM "RATEPLAN_HIST" "SYS_ALIAS_3" WHERE "TMCODE"=:1 AND "CO_ID"=:2 (accessing 'BSCSDB.WORLD' )
16 - SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("CD",4) */ "CO_ID","PORT_ID" FROM "CONTR_DEVICES" "CD" WHERE
"CO_ID"=:1 (accessing 'BSCSDB.WORLD' )
17 - SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("P",4) */ "PORT_ID","SM_ID" FROM "PORT" "P" WHERE
:1="PORT_ID" (accessing 'BSCSDB.WORLD' )
18 - SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("SYS_ALIAS_2",4) */
"SM_ID","SM_SERIALNUM","SM_STATUS","DEALER_ID","SM_PUK","REC_VERSION" FROM "STORAGE_MEDIUM" "SYS_ALIAS_2"
WHERE "SM_STATUS"='a' AND "SM_ID"=:1 (accessing 'BSCSDB.WORLD' )
19 - SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("DN",4) */ "DN_ID","DN_NUM","DN_STATUS" FROM
"DIRECTORY_NUMBER" "DN" WHERE "DN_STATUS"='a' AND :1="DN_ID" (accessing 'BSCSDB.WORLD' )
20 - SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("RP",4) */ "TMCODE","DES" FROM "RATEPLAN" "RP" WHERE
:1="TMCODE" (accessing 'BSCSDB.WORLD' )
21 - SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("D",4) */ "CUSTOMER_ID","CUSTCODE" FROM "SYSADM"."DEALER"
"D" WHERE :1="CUSTOMER_ID" (accessing 'BSCSDB.WORLD' )
22 - SELECT /*+ OPAQUE_TRANSFORM PARALLEL ("SYS_ALIAS_1",4) */ "CO_ID","CH_SEQNO","CH_STATUS" FROM
"CONTRACT_HISTORY" "SYS_ALIAS_1" WHERE :1="CO_ID" (accessing 'BSCSDB.WORLD' )
25 - SELECT /*+ OPAQUE_TRANSFORM */ "CO_ID","CH_SEQNO" FROM "CONTRACT_HISTORY" "CH1" WHERE "CO_ID"=:1
(accessing 'BSCSDB.WORLD' )
28 - SELECT /*+ OPAQUE_TRANSFORM */ "SM_SERIALNUM","REC_VERSION" FROM "STORAGE_MEDIUM" "SM1" WHERE
"SM_SERIALNUM"=:1 (accessing 'BSCSDB.WORLD' )
31 - SELECT /*+ OPAQUE_TRANSFORM */ "CO_ID","SEQNO" FROM "RATEPLAN_HIST" "RH1" WHERE "CO_ID"=:1
(accessing 'BSCSDB.WORLD' )
34 - SELECT /*+ OPAQUE_TRANSFORM */ "CO_ID","SNCODE","SEQNO" FROM "CONTR_SERVICES_CAP" "CSC1" WHERE
"CO_ID"=:1 AND "SNCODE"=:2 (accessing 'BSCSDB.WORLD' )
Please help me.
I can think of nothing better
Thank you all and regards,
Lanti