3 Replies Latest reply on Oct 18, 2012 4:17 PM by rp0428

    SQL query tuning

    969133
      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
          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.
          PL/SQL and SQL

          This forum is for SQL Developer questions only.
          • 2. Re: SQL query tuning
            969133
            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
              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.