This discussion is archived
3 Replies Latest reply: Oct 18, 2012 9:17 AM by rp0428 RSS

SQL query tuning

969133 Newbie
Currently Being Moderated
Hi All,

we have the following query which is taking nearly 3 hrs to comlete execution. DB version
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

The query is

SELECT distinct
decode(tcpt.NIVCLI,'ETB','1','NTT','2','SGP','3','GRP','4',' ')||
lpad(nvl(tcpt.CODNTT,'0'),9,'0')||
lpad(nvl(tcpt.CODSGP,'0'),8,'0')||
lpad(nvl(tcpt.CODGRP,'0'),8,'0')||
lpad((replace(hess.codstrsui,'N1_1','MARK')),10,' ')||
lpad(hess.typris||'.'||hess.indris,10,' ')||
lpad(hess.codpdtcem,10,' ')||
lpad(hess.codzonges,10,' ')||
lpad(hess.codzonbkg,10,' ')||
'USD '||
decode(hess.mnemettot,'DEP',decode(hess.eng001,0,'-000000000000000',to_char(hess.eng001,'S000000000000000')),to_char(hess.eng001,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng002,0,'-000000000000000',to_char(hess.eng002,'S000000000000000')),to_char(hess.eng002,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng003,0,'-000000000000000',to_char(hess.eng003,'S000000000000000')),to_char(hess.eng003,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng004,0,'-000000000000000',to_char(hess.eng004,'S000000000000000')),to_char(hess.eng004,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng005,0,'-000000000000000',to_char(hess.eng005,'S000000000000000')),to_char(hess.eng005,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng006,0,'-000000000000000',to_char(hess.eng006,'S000000000000000')),to_char(hess.eng006,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng007,0,'-000000000000000',to_char(hess.eng007,'S000000000000000')),to_char(hess.eng007,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng008,0,'-000000000000000',to_char(hess.eng008,'S000000000000000')),to_char(hess.eng008,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng009,0,'-000000000000000',to_char(hess.eng009,'S000000000000000')),to_char(hess.eng009,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng010,0,'-000000000000000',to_char(hess.eng010,'S000000000000000')),to_char(hess.eng010,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng011,0,'-000000000000000',to_char(hess.eng011,'S000000000000000')),to_char(hess.eng011,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng012,0,'-000000000000000',to_char(hess.eng012,'S000000000000000')),to_char(hess.eng012,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng013,0,'-000000000000000',to_char(hess.eng013,'S000000000000000')),to_char(hess.eng013,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng014,0,'-000000000000000',to_char(hess.eng014,'S000000000000000')),to_char(hess.eng014,'S000000000000000'))||
hess.mnemettot
FROM
TCLIDETPRC tcpt,
HESSPRC hess
WHERE tcpt.codclibdr=hess.codclibdr
AND hess.codstrsui in ('DTRE', 'DFIX','DTRE_NY')
AND hess.codzonbkg='LMGLOB'
AND hess.codpdtcem='GLOBAL'
AND hess.codclibdr<>'GLOBAL'
AND hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','DEB.PRI','REMP.CVAR')
AND hess.mnemettot in ('DEP','DSP')
and not exists
(Select 1 from Tlimprc lim
where lim.codclibdr || 'EJ'=hess.codclibdr
and lim.codzonbkg=hess.codzonbkg
and lim.codpdtcem=hess.codpdtcem
and lim.typris = hess.typris||'.'||hess.indris
and lim.codstrsui='N1_1'
AND (lim.typris in ('REMP.RCM','RDL.RDL','REMP.CVAR')
OR (lim.typris = 'DEB.PRI'
AND lim.codclibdr NOT IN (SELECT codclibdr FROM tmglperimdebtprc)
)
)
)
UNION
SELECT distinct
decode(tcpt.NIVCLI,'ETB','1','NTT','2','SGP','3','GRP','4',' ')||
lpad(nvl(tcpt.CODNTT,'0'),9,'0')||
lpad(nvl(tcpt.CODSGP,'0'),8,'0')||
lpad(nvl(tcpt.CODGRP,'0'),8,'0')||
lpad((replace(hess.codstrsui,'N1_1','MARK')),10,' ')||
lpad(hess.typris||'.'||hess.indris,10,' ')||
lpad(hess.codpdtcem,10,' ')||
lpad(hess.codzonges,10,' ')||
lpad(hess.codzonbkg,10,' ')||
'USD '||
decode(hess.mnemettot,'DEP',decode(hess.eng001,0,'-000000000000000',to_char(hess.eng001,'S000000000000000')),to_char(hess.eng001,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng002,0,'-000000000000000',to_char(hess.eng002,'S000000000000000')),to_char(hess.eng002,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng003,0,'-000000000000000',to_char(hess.eng003,'S000000000000000')),to_char(hess.eng003,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng004,0,'-000000000000000',to_char(hess.eng004,'S000000000000000')),to_char(hess.eng004,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng005,0,'-000000000000000',to_char(hess.eng005,'S000000000000000')),to_char(hess.eng005,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng006,0,'-000000000000000',to_char(hess.eng006,'S000000000000000')),to_char(hess.eng006,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng007,0,'-000000000000000',to_char(hess.eng007,'S000000000000000')),to_char(hess.eng007,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng008,0,'-000000000000000',to_char(hess.eng008,'S000000000000000')),to_char(hess.eng008,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng009,0,'-000000000000000',to_char(hess.eng009,'S000000000000000')),to_char(hess.eng009,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng010,0,'-000000000000000',to_char(hess.eng010,'S000000000000000')),to_char(hess.eng010,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng011,0,'-000000000000000',to_char(hess.eng011,'S000000000000000')),to_char(hess.eng011,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng012,0,'-000000000000000',to_char(hess.eng012,'S000000000000000')),to_char(hess.eng012,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng013,0,'-000000000000000',to_char(hess.eng013,'S000000000000000')),to_char(hess.eng013,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng014,0,'-000000000000000',to_char(hess.eng014,'S000000000000000')),to_char(hess.eng014,'S000000000000000'))||
hess.mnemettot
FROM
TCLIDETPRC tcpt,
HESSPRC hess
WHERE tcpt.codclibdr=hess.codclibdr
AND tcpt.NIVCLI IN('SGP','GRP')
AND hess.codstrsui in (select codstrsui from tstrdetprc where codstrsui002 in ('N1_1'))
AND hess.codzonbkg='LMGLOB'
AND hess.codpdtcem='GLOBAL'
AND hess.codclibdr<>'GLOBAL'
AND hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','DEB.PRI','REMP.CVAR')
AND hess.mnemettot in ('DEP','DSP')
UNION
SELECT distinct
decode(tcpt.NIVCLI,'ETB','1','NTT','2','SGP','3','GRP','4',' ')||
lpad(nvl(tcpt.CODNTT,'0'),9,'0')||
lpad(nvl(tcpt.CODSGP,'0'),8,'0')||
lpad(nvl(tcpt.CODGRP,'0'),8,'0')||
lpad((replace(hess.codstrsui,'N1_1','MARK')),10,' ')||
lpad(hess.typris||'.'||hess.indris,10,' ')||
lpad(hess.codpdtcem,10,' ')||
lpad(hess.codzonges,10,' ')||
lpad(hess.codzonbkg,10,' ')||
'USD '||
decode(hess.mnemettot,'DEP',decode(hess.eng001,0,'-000000000000000',to_char(hess.eng001,'S000000000000000')),to_char(hess.eng001,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng002,0,'-000000000000000',to_char(hess.eng002,'S000000000000000')),to_char(hess.eng002,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng003,0,'-000000000000000',to_char(hess.eng003,'S000000000000000')),to_char(hess.eng003,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng004,0,'-000000000000000',to_char(hess.eng004,'S000000000000000')),to_char(hess.eng004,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng005,0,'-000000000000000',to_char(hess.eng005,'S000000000000000')),to_char(hess.eng005,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng006,0,'-000000000000000',to_char(hess.eng006,'S000000000000000')),to_char(hess.eng006,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng007,0,'-000000000000000',to_char(hess.eng007,'S000000000000000')),to_char(hess.eng007,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng008,0,'-000000000000000',to_char(hess.eng008,'S000000000000000')),to_char(hess.eng008,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng009,0,'-000000000000000',to_char(hess.eng009,'S000000000000000')),to_char(hess.eng009,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng010,0,'-000000000000000',to_char(hess.eng010,'S000000000000000')),to_char(hess.eng010,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng011,0,'-000000000000000',to_char(hess.eng011,'S000000000000000')),to_char(hess.eng011,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng012,0,'-000000000000000',to_char(hess.eng012,'S000000000000000')),to_char(hess.eng012,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng013,0,'-000000000000000',to_char(hess.eng013,'S000000000000000')),to_char(hess.eng013,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng014,0,'-000000000000000',to_char(hess.eng014,'S000000000000000')),to_char(hess.eng014,'S000000000000000'))||
hess.mnemettot
FROM
TCLIDETPRC tcpt,
HESSPRC hess
WHERE tcpt.codclibdr=hess.codclibdr
AND tcpt.NIVCLI ='NTT'
AND hess.codstrsui ='DECC'
AND hess.codzonbkg='LMGLOB'
AND hess.codpdtcem='GLOBAL'
AND hess.codclibdr<>'GLOBAL'
AND hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','DEB.PRI','REMP.CVAR')
AND hess.mnemettot in ('DEP','DSP')
and exists
(Select 1 from Tlimprc lim
where lim.codclibdr || 'EJ'=hess.codclibdr
and lim.codzonbkg=hess.codzonbkg
and lim.codpdtcem=hess.codpdtcem
and lim.typris=hess.typris||'.'||hess.indris
and lim.codstrsui='N1_1')
and not exists
(SELECT 1 FROM TMGLPERIMDEBTPRC debt
WHERE debt.codclibdr || 'EJ' = hess.codclibdr
AND hess.typris||'.'||hess.indris ='DEB.PRI' )
UNION
SELECT distinct
decode(tcpt.NIVCLI,'ETB','1','NTT','2','SGP','3','GRP','4',' ')||
lpad(nvl(tcpt.CODNTT,'0'),9,'0')||
lpad(nvl(tcpt.CODSGP,'0'),8,'0')||
lpad(nvl(tcpt.CODGRP,'0'),8,'0')||
lpad((replace(hess.codstrsui,'N1_1','MARK')),10,' ')||
lpad(hess.typris||'.'||hess.indris,10,' ')||
lpad(hess.codpdtcem,10,' ')||
lpad(hess.codzonges,10,' ')||
lpad(hess.codzonbkg,10,' ')||
'USD '||
decode(hess.mnemettot,'DEP',decode(hess.eng001,0,'-000000000000000',to_char(hess.eng001,'S000000000000000')),to_char(hess.eng001,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng002,0,'-000000000000000',to_char(hess.eng002,'S000000000000000')),to_char(hess.eng002,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng003,0,'-000000000000000',to_char(hess.eng003,'S000000000000000')),to_char(hess.eng003,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng004,0,'-000000000000000',to_char(hess.eng004,'S000000000000000')),to_char(hess.eng004,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng005,0,'-000000000000000',to_char(hess.eng005,'S000000000000000')),to_char(hess.eng005,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng006,0,'-000000000000000',to_char(hess.eng006,'S000000000000000')),to_char(hess.eng006,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng007,0,'-000000000000000',to_char(hess.eng007,'S000000000000000')),to_char(hess.eng007,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng008,0,'-000000000000000',to_char(hess.eng008,'S000000000000000')),to_char(hess.eng008,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng009,0,'-000000000000000',to_char(hess.eng009,'S000000000000000')),to_char(hess.eng009,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng010,0,'-000000000000000',to_char(hess.eng010,'S000000000000000')),to_char(hess.eng010,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng011,0,'-000000000000000',to_char(hess.eng011,'S000000000000000')),to_char(hess.eng011,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng012,0,'-000000000000000',to_char(hess.eng012,'S000000000000000')),to_char(hess.eng012,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng013,0,'-000000000000000',to_char(hess.eng013,'S000000000000000')),to_char(hess.eng013,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng014,0,'-000000000000000',to_char(hess.eng014,'S000000000000000')),to_char(hess.eng014,'S000000000000000'))||
hess.mnemettot
FROM
TCLIDETPRC tcpt,
HESSPRC hess
WHERE tcpt.codclibdr=hess.codclibdr
AND hess.codstrsui ='BHFM'
AND hess.codzonbkg='SKB'
AND hess.codpdtcem='GLOBAL'
AND hess.codclibdr<>'GLOBAL'
AND hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','DEB.PRI','REMP.CVAR')
AND hess.mnemettot in ('DEP','DSP')
UNION
SELECT distinct
decode(tcpt.NIVCLI,'ETB','1','NTT','2','SGP','3','GRP','4',' ')||
lpad(nvl(tcpt.CODNTT,'0'),9,'0')||
lpad(nvl(tcpt.CODSGP,'0'),8,'0')||
lpad(nvl(tcpt.CODGRP,'0'),8,'0')||
lpad((replace(hess.codstrsui,'N1_1','MARK')),10,' ')||
lpad(hess.typris||'.'||hess.indris,10,' ')||
lpad(hess.codpdtcem,10,' ')||
lpad(hess.codzonges,10,' ')||
lpad(hess.codzonbkg,10,' ')||
'USD '||
decode(hess.mnemettot,'DEP',decode(hess.eng001,0,'-000000000000000',to_char(hess.eng001,'S000000000000000')),to_char(hess.eng001,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng002,0,'-000000000000000',to_char(hess.eng002,'S000000000000000')),to_char(hess.eng002,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng003,0,'-000000000000000',to_char(hess.eng003,'S000000000000000')),to_char(hess.eng003,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng004,0,'-000000000000000',to_char(hess.eng004,'S000000000000000')),to_char(hess.eng004,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng005,0,'-000000000000000',to_char(hess.eng005,'S000000000000000')),to_char(hess.eng005,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng006,0,'-000000000000000',to_char(hess.eng006,'S000000000000000')),to_char(hess.eng006,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng007,0,'-000000000000000',to_char(hess.eng007,'S000000000000000')),to_char(hess.eng007,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng008,0,'-000000000000000',to_char(hess.eng008,'S000000000000000')),to_char(hess.eng008,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng009,0,'-000000000000000',to_char(hess.eng009,'S000000000000000')),to_char(hess.eng009,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng010,0,'-000000000000000',to_char(hess.eng010,'S000000000000000')),to_char(hess.eng010,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng011,0,'-000000000000000',to_char(hess.eng011,'S000000000000000')),to_char(hess.eng011,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng012,0,'-000000000000000',to_char(hess.eng012,'S000000000000000')),to_char(hess.eng012,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng013,0,'-000000000000000',to_char(hess.eng013,'S000000000000000')),to_char(hess.eng013,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng014,0,'-000000000000000',to_char(hess.eng014,'S000000000000000')),to_char(hess.eng014,'S000000000000000'))||
hess.mnemettot
FROM
TCLIDETPRC tcpt,
HESSPRC hess
WHERE tcpt.codclibdr=hess.codclibdr
AND hess.codstrsui in('DCOT')
AND hess.codzonbkg='LMGLOB'
AND hess.codpdtcem='GLOBAL'
AND hess.codclibdr<>'GLOBAL'
AND hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','REMP.CVAR')
AND hess.mnemettot in ('DEP','DSP')
and not exists
(Select 1 from Tlimprc lim
where lim.codclibdr || 'EJ'= hess.codclibdr
and lim.codzonbkg=hess.codzonbkg
and lim.codpdtcem=hess.codpdtcem
and lim.typris=hess.typris||'.'||hess.indris
and lim.codstrsui= 'N1_1')
UNION
SELECT distinct
decode(tcpt.NIVCLI,'ETB','1','NTT','2','SGP','3','GRP','4',' ')||
lpad(nvl(tcpt.CODNTT,'0'),9,'0')||
lpad(nvl(tcpt.CODSGP,'0'),8,'0')||
lpad(nvl(tcpt.CODGRP,'0'),8,'0')||
lpad((replace(hess.codstrsui,'N1_1','MARK')),10,' ')||
lpad(hess.typris||'.'||hess.indris,10,' ')||
lpad(hess.codpdtcem,10,' ')||
lpad(hess.codzonges,10,' ')||
lpad(hess.codzonbkg,10,' ')||
'USD '||
decode(hess.mnemettot,'DEP',decode(hess.eng001,0,'-000000000000000',to_char(hess.eng001,'S000000000000000')),to_char(hess.eng001,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng002,0,'-000000000000000',to_char(hess.eng002,'S000000000000000')),to_char(hess.eng002,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng003,0,'-000000000000000',to_char(hess.eng003,'S000000000000000')),to_char(hess.eng003,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng004,0,'-000000000000000',to_char(hess.eng004,'S000000000000000')),to_char(hess.eng004,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng005,0,'-000000000000000',to_char(hess.eng005,'S000000000000000')),to_char(hess.eng005,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng006,0,'-000000000000000',to_char(hess.eng006,'S000000000000000')),to_char(hess.eng006,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng007,0,'-000000000000000',to_char(hess.eng007,'S000000000000000')),to_char(hess.eng007,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng008,0,'-000000000000000',to_char(hess.eng008,'S000000000000000')),to_char(hess.eng008,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng009,0,'-000000000000000',to_char(hess.eng009,'S000000000000000')),to_char(hess.eng009,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng010,0,'-000000000000000',to_char(hess.eng010,'S000000000000000')),to_char(hess.eng010,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng011,0,'-000000000000000',to_char(hess.eng011,'S000000000000000')),to_char(hess.eng011,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng012,0,'-000000000000000',to_char(hess.eng012,'S000000000000000')),to_char(hess.eng012,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng013,0,'-000000000000000',to_char(hess.eng013,'S000000000000000')),to_char(hess.eng013,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng014,0,'-000000000000000',to_char(hess.eng014,'S000000000000000')),to_char(hess.eng014,'S000000000000000'))||
hess.mnemettot
FROM
TCLIDETPRC tcpt,
HESSPRC hess
WHERE tcpt.codclibdr=hess.codclibdr
AND tcpt.NIVCLI='NTT'
AND hess.codstrsui ='CTY'
AND hess.codzonbkg='LMGLOB'
AND hess.codpdtcem='GLOBAL'
AND hess.codclibdr<>'GLOBAL'
AND hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','REMP.CVAR')
AND hess.mnemettot in ('DEP','DSP')
and exists
(Select 1 from Tlimprc lim
where lim.codclibdr || 'EJ'=hess.codclibdr
and lim.codzonbkg=hess.codzonbkg
and lim.codpdtcem=hess.codpdtcem
and lim.typris=hess.typris||'.'||hess.indris
and lim.codstrsui='N1_1')
UNION
SELECT distinct
decode(tcpt.NIVCLI,'ETB','1','NTT','2','SGP','3','GRP','4',' ')||
lpad(nvl(tcpt.CODNTT,'0'),9,'0')||
lpad(nvl(tcpt.CODSGP,'0'),8,'0')||
lpad(nvl(tcpt.CODGRP,'0'),8,'0')||
lpad((replace(hess.codstrsui,'N1_1','MARK')),10,' ')||
lpad(hess.typris||'.'||hess.indris,10,' ')||
lpad(hess.codpdtcem,10,' ')||
lpad(hess.codzonges,10,' ')||
lpad(hess.codzonbkg,10,' ')||
'USD '||
decode(hess.mnemettot,'DEP',decode(hess.eng001,0,'-000000000000000',to_char(hess.eng001,'S000000000000000')),to_char(hess.eng001,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng002,0,'-000000000000000',to_char(hess.eng002,'S000000000000000')),to_char(hess.eng002,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng003,0,'-000000000000000',to_char(hess.eng003,'S000000000000000')),to_char(hess.eng003,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng004,0,'-000000000000000',to_char(hess.eng004,'S000000000000000')),to_char(hess.eng004,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng005,0,'-000000000000000',to_char(hess.eng005,'S000000000000000')),to_char(hess.eng005,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng006,0,'-000000000000000',to_char(hess.eng006,'S000000000000000')),to_char(hess.eng006,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng007,0,'-000000000000000',to_char(hess.eng007,'S000000000000000')),to_char(hess.eng007,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng008,0,'-000000000000000',to_char(hess.eng008,'S000000000000000')),to_char(hess.eng008,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng009,0,'-000000000000000',to_char(hess.eng009,'S000000000000000')),to_char(hess.eng009,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng010,0,'-000000000000000',to_char(hess.eng010,'S000000000000000')),to_char(hess.eng010,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng011,0,'-000000000000000',to_char(hess.eng011,'S000000000000000')),to_char(hess.eng011,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng012,0,'-000000000000000',to_char(hess.eng012,'S000000000000000')),to_char(hess.eng012,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng013,0,'-000000000000000',to_char(hess.eng013,'S000000000000000')),to_char(hess.eng013,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng014,0,'-000000000000000',to_char(hess.eng014,'S000000000000000')),to_char(hess.eng014,'S000000000000000'))||
hess.mnemettot
FROM
TCLIDETPRC tcpt,
HESSPRC hess
WHERE tcpt.codclibdr=hess.codclibdr
AND hess.codstrsui in ('IRDSGO','CREDER')
AND hess.codzonbkg='LMGLOB'
AND hess.codpdtcem='GLOBAL'
AND hess.codclibdr<>'GLOBAL'
AND hess.typris||'.'||hess.indris IN('REMP.CVAR','REMP.RCM')
AND hess.mnemettot in ('DEP','DSP')
and not exists
(Select 1 from Tlimprc lim
where lim.codclibdr || 'EJ'=hess.codclibdr
and lim.codzonbkg=hess.codzonbkg
and lim.codpdtcem=hess.codpdtcem
and lim.typris=hess.typris||'.'||hess.indris
and lim.codstrsui='N1_1')
UNION
SELECT distinct
decode(tcpt.NIVCLI,'ETB','1','NTT','2','SGP','3','GRP','4',' ')||
lpad(nvl(tcpt.CODNTT,'0'),9,'0')||
lpad(nvl(tcpt.CODSGP,'0'),8,'0')||
lpad(nvl(tcpt.CODGRP,'0'),8,'0')||
lpad((replace(hess.codstrsui,'N1_1','MARK')),10,' ')||
lpad(hess.typris||'.'||hess.indris,10,' ')||
lpad(hess.codpdtcem,10,' ')||
lpad(hess.codzonges,10,' ')||
lpad(hess.codzonbkg,10,' ')||
'USD '||
decode(hess.mnemettot,'DEP',decode(hess.eng001,0,'-000000000000000',to_char(hess.eng001,'S000000000000000')),to_char(hess.eng001,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng002,0,'-000000000000000',to_char(hess.eng002,'S000000000000000')),to_char(hess.eng002,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng003,0,'-000000000000000',to_char(hess.eng003,'S000000000000000')),to_char(hess.eng003,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng004,0,'-000000000000000',to_char(hess.eng004,'S000000000000000')),to_char(hess.eng004,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng005,0,'-000000000000000',to_char(hess.eng005,'S000000000000000')),to_char(hess.eng005,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng006,0,'-000000000000000',to_char(hess.eng006,'S000000000000000')),to_char(hess.eng006,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng007,0,'-000000000000000',to_char(hess.eng007,'S000000000000000')),to_char(hess.eng007,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng008,0,'-000000000000000',to_char(hess.eng008,'S000000000000000')),to_char(hess.eng008,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng009,0,'-000000000000000',to_char(hess.eng009,'S000000000000000')),to_char(hess.eng009,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng010,0,'-000000000000000',to_char(hess.eng010,'S000000000000000')),to_char(hess.eng010,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng011,0,'-000000000000000',to_char(hess.eng011,'S000000000000000')),to_char(hess.eng011,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng012,0,'-000000000000000',to_char(hess.eng012,'S000000000000000')),to_char(hess.eng012,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng013,0,'-000000000000000',to_char(hess.eng013,'S000000000000000')),to_char(hess.eng013,'S000000000000000'))||
decode(hess.mnemettot,'DEP',decode(hess.eng014,0,'-000000000000000',to_char(hess.eng014,'S000000000000000')),to_char(hess.eng014,'S000000000000000'))||
hess.mnemettot
FROM
TCLIDETPRC tcpt,
HESSPRC hess
WHERE tcpt.codclibdr=hess.codclibdr
AND hess.codstrsui = 'GLOBAL'
AND hess.codzonbkg='GLOBAL'
AND hess.codpdtcem='GLOBAL'
AND hess.codclibdr<>'GLOBAL'
AND hess.typris||'.'||hess.indris = 'EXEC.CVAR'
AND hess.mnemettot in ('DEP','DSP')
order by 1;

Any suggestion to tune the query is highly appreciated
Its very urgent as we have the issue in PRODUCTION.

If required I can provide the execution plan ( in this post I am not able since the maxmun length is 30000 chars)

Thanks in advance.

Regards
Rashmi.
  • 1. Re: SQL query tuning
    rp0428 Guru
    Currently Being Moderated
    WRONG FORUM!

    You need to post this question in the SQL and PL/SQL forum. Please mark this question ANSWERED and post in the correct forum.
    SQL and PL/SQL

    This forum is for SQL Developer questions only.
  • 2. Re: SQL query tuning
    969133 Newbie
    Currently Being Moderated
    I have re-written the query like below which is taking 15mins to complete execution

    WITH tlim AS
    (SELECT /*+ INDEX (lim ILIMPRC_CLIZBPDTRISSTRLIM_PRC) */ lim.codclibdr||'EJ' cli, lim.codzonbkg bkg, lim.codpdtcem cem, lim.typris ris
    FROM tlimprc lim
    WHERE lim.codstrsui='N1_1'
    )
    SELECT /*+ ALL_ROWS parallel ( hess 8) index ( hess IHESS_J1_PRC) INDEX ( TCPT ITCLIDET_CLIBDR_PRC) INLINE */
    decode(tcpt.NIVCLI,'ETB','1','NTT','2','SGP','3','GRP','4',' ')||
    lpad(nvl(tcpt.CODNTT,'0'),9,'0')||
    lpad(nvl(tcpt.CODSGP,'0'),8,'0')||
    lpad(nvl(tcpt.CODGRP,'0'),8,'0')||
    lpad((replace(hess.codstrsui,'N1_1','MARK')),10,' ')||
    lpad(hess.typris||'.'||hess.indris,10,' ')||
    lpad(hess.codpdtcem,10,' ')||
    lpad(hess.codzonges,10,' ')||
    lpad(hess.codzonbkg,10,' ')||
         'USD '||
    decode(hess.mnemettot,'DEP',decode(hess.eng001,0,'-000000000000000',to_char(hess.eng001,'S000000000000000')),to_char(hess.eng001,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng002,0,'-000000000000000',to_char(hess.eng002,'S000000000000000')),to_char(hess.eng002,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng003,0,'-000000000000000',to_char(hess.eng003,'S000000000000000')),to_char(hess.eng003,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng004,0,'-000000000000000',to_char(hess.eng004,'S000000000000000')),to_char(hess.eng004,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng005,0,'-000000000000000',to_char(hess.eng005,'S000000000000000')),to_char(hess.eng005,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng006,0,'-000000000000000',to_char(hess.eng006,'S000000000000000')),to_char(hess.eng006,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng007,0,'-000000000000000',to_char(hess.eng007,'S000000000000000')),to_char(hess.eng007,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng008,0,'-000000000000000',to_char(hess.eng008,'S000000000000000')),to_char(hess.eng008,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng009,0,'-000000000000000',to_char(hess.eng009,'S000000000000000')),to_char(hess.eng009,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng010,0,'-000000000000000',to_char(hess.eng010,'S000000000000000')),to_char(hess.eng010,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng011,0,'-000000000000000',to_char(hess.eng011,'S000000000000000')),to_char(hess.eng011,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng012,0,'-000000000000000',to_char(hess.eng012,'S000000000000000')),to_char(hess.eng012,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng013,0,'-000000000000000',to_char(hess.eng013,'S000000000000000')),to_char(hess.eng013,'S000000000000000'))||
    decode(hess.mnemettot,'DEP',decode(hess.eng014,0,'-000000000000000',to_char(hess.eng014,'S000000000000000')),to_char(hess.eng014,'S000000000000000'))||
    hess.mnemettot dat
    FROM HESSPRC hess, TCLIDETPRC tcpt
    WHERE tcpt.codclibdr=hess.codclibdr
    AND hess.codpdtcem='GLOBAL'
    AND hess.mnemettot in ('DEP','DSP')
    AND hess.codclibdr<>'GLOBAL'
    AND
    ( (hess.typris||'.'||hess.indris = 'EXEC.CVAR'
    AND hess.codzonbkg='GLOBAL'
    AND hess.codstrsui = 'GLOBAL'
    )
    OR (hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','DEB.PRI','REMP.CVAR')
    AND hess.codzonbkg='LMGLOB'
    AND hess.codstrsui IN (SELECT codstrsui from tstrdetprc where codstrsui002 in ('N1_1'))
    AND tcpt.NIVCLI IN('SGP','GRP')
    )
    OR ( hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','DEB.PRI','REMP.CVAR')
    AND hess.codzonbkg='SKB'
    AND hess.codstrsui ='BHFM'
    )
    OR (hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','DEB.PRI','REMP.CVAR')
    AND hess.codzonbkg='LMGLOB'
    AND hess.codstrsui in ('DTRE', 'DFIX','DTRE_NY')
    AND (hess.codclibdr, hess.codzonbkg, hess.codpdtcem, hess.typris||'.'||hess.indris)
    NOT IN (SELECT cli, bkg, cem, ris
    FROM tlim lim
    WHERE lim.ris IN ('REMP.RCM','RDL.RDL','REMP.CVAR')
    OR (lim.ris = 'DEB.PRI'
    AND lim.cli NOT IN (SELECT codclibdr||'EJ' FROM tmglperimdebtprc)
    )
    )
    )
    OR ( hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','DEB.PRI','REMP.CVAR')
    AND hess.codzonbkg='LMGLOB'
    AND hess.codstrsui ='DECC'
    AND tcpt.NIVCLI ='NTT'
    AND (hess.codclibdr, hess.codzonbkg, hess.codpdtcem, hess.typris||'.'||hess.indris)
    IN (SELECT cli, bkg, cem, ris FROM tlim)
    AND (hess.codclibdr, hess.typris||'.'||hess.indris)
    NOT IN (SELECT debt.codclibdr||'EJ' , 'DEB.PRI' FROM tmglperimdebtprc debt )
    )
    OR ( hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','REMP.CVAR')
    AND hess.codzonbkg='LMGLOB'
    AND hess.codstrsui in('DCOT')
    AND (hess.codclibdr, hess.codzonbkg, hess.codpdtcem, hess.typris||'.'||hess.indris)
    NOT IN (SELECT cli, bkg, cem, ris FROM tlim)
    )
    OR ( hess.typris||'.'||hess.indris in ('REMP.RCM','RDL.RDL','REMP.CVAR')
    AND hess.codzonbkg='LMGLOB'
    AND hess.codstrsui ='CTY'
    AND tcpt.NIVCLI='NTT'
    AND (hess.codclibdr, hess.codzonbkg, hess.codpdtcem, hess.typris||'.'||hess.indris)
    IN (SELECT cli, bkg, cem, ris FROM tlim)
    )
    OR ( hess.typris||'.'||hess.indris IN('REMP.CVAR','REMP.RCM')
    AND hess.codzonbkg='LMGLOB'
    AND hess.codstrsui in ('IRDSGO','CREDER')
    AND (hess.codclibdr, hess.codzonbkg, hess.codpdtcem, hess.typris||'.'||hess.indris)
    NOT IN (SELECT cli, bkg, cem, ris FROM tlim)
    )
    )
    order by dat;

    Any other suggestions higly appreciated.


    Regards
    Rashmi.
  • 3. Re: SQL query tuning
    rp0428 Guru
    Currently Being Moderated
    Please mark this question ANSWERED and post it in the proper forum as asked above.

    As forum contributors are required to accept and follow the forum guidelines; see the FAQ for details.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points