3 Replies Latest reply on Feb 16, 2013 9:27 AM by user503635

    Slow Performing Query

    650838
      Hi

      I am looking for suggestions to improve the performance of a particular query. The query is the code behind for a portlet (a kind of report) which displays data in the application. So, whenever it is being run in the front-end, the portlet keeps hanging and finally fails. The tables are all properly indexed and analyzed regularly.

      QUERY *******************

      SELECT
      RESULTS.UID UID,
      RESULTS.PID PID,
      RESULTS.PC PC,
      RESULTS.PE PE,
      RESULTS.PMID PMID,
      RESULTS.MNAME MNAME,
      RESULTS.PRS PRS,
      RESULTS.PJE PJE,
      RESULTS.PSID PSID,
      RESULTS.PSS PSS,
      RESULTS.PCC PCC,
      RESULTS.PCN PCN,
      RESULTS.PIA PIA,
      RESULTS.PNDID PNDID,
      RESULTS.PNDME PNDME,
      RESULTS.RID RID,
      RESULTS.RODE RODE,
      RESULTS.RN RN,
      RESULTS.TD TD,
      RESULTS.TN TN,
      RESULTS.EPEID EPEID,
      RESULTS.EENAME EENAME,
      RESULTS.TID TID,
      RESULTS.TERIT TERIT,
      RESULTS.TFIN TFIN,
      RESULTS.TMON TMON,
      RESULTS.BHOUR BHOUR,
      RESULTS.BDOLLAR BDOLLAR,
      RESULTS.AHRS AHRS,
      RESULTS.AARS AARS,
      RESULTS.EHRS EHRS,
      RESULTS.EDOLLAR EDOLLAR,
      RESULTS.PHRS PHRS,
      RESULTS.PDS PDS,
      RESULTS.PD PD,
      RESULTS.PE PE,
      RESULTS.R R,
      RESULTS.RE RE,
      RESULTS.RDE RDE,
      RESULTS.SID SID,
      RESULTS.ME ME,
      RESULTS.E E,
      RESULTS.RID RID,
      RESULTS.REE REE,
      RESULTS.PE PE,
      RESULTS.LTE LTE,
      RESULTS.PEID PEID,
      RESULTS.TE TE,
      RESULTS.P P,
      RESULTS.TODE TODE,
      RESULTS.YAR YAR,
      RESULTS.TATE TATE,
      RESULTS.TDATE TDATE,
      RESULTS.AME AME,
      RESULTS.C_ID C_ID,
      RESULTS.C_NAME C_NAME,
      RESULTS.C C,
      RESULTS.udget udget

      FROM
      (SELECT
      LPA.PID||LPA.TID||LPA.TID||LPA.RD||LPA.PRYPE||LPA.rede UID,
      LPA.PD PID,
      LPA.PC PC,
      LPA.PE PE,
      LPA.PMID PMID,
      LPA.MNAME MNAME,
      LPA.PRS PRS,
      LPA.PJE PJE,
      LPA.PSID PSID,
      LPA.PSS PSS,
      LPA.PCC PCC,
      LPA.PCN PCN,
      LPA.PIA PIA,
      LPA.PNDID PNDID,
      LPA.PNDME PNDME,
      LPA.RID RID,
      LPA.RODE RODE,
      LPA.RN RN,
      LPA.RM RM,
      LPA.RNA RNA,
      LPA.RANA RANA,
      LPA.TD TD,
      LPA.TN TN,
      LPA.EPEID EPEID,
      LPA.ENE ENE,
      LPA.TD TD,
      LPA.TARE TARE,
      LPA.TATE TATE,
      LPA.TNTH TNTH,
      LPA.BHOUR BHOUR,
      LPA.BDOLLAR BDOLLAR,
      LPA.ARS ARS,
      LPA.AARS AARS,
      LPA.EHRS EHRS,
      LPA.EOLLAR EOLLAR,
      LPA.PHRS PHRS,
      LPA.POLLARS POLLARS,
      LPA.PD PD,
      LPA.P P,
      LPA.PE PE,
      LPA.RID RID,
      LPA.RODE RODE,
      LPA.RAME RAME,
      LPA.PD PD,
      LPA.PME PME,
      LPA.CTE CTE,
      LPA.LTE LTE,
      LPA.PD PD,
      LPA.PE PE,
      LPA.T T,
      LPA.YAR YAR,
      LPA.TATE TATE,
      LPA.TDATE TDATE,
      COMP.C_NAME C_NAME,
      CONT.C_ID C_ID,
      CONTR.NAME COUNTRY_NAME,
      CONT.C C,
      (select udget from o_c_p where id = lpa.pid) udget
      FROM Z_L_P_A_A LPA
      ,S_C CONT,S_CO COMP
      ,C_C_V CONTR
      WHERE LPA.PYPE = 'MONTH'
      AND CONT.PID(+) = LPA.R_ID
      AND CONT.PL_TYPE(+) = 'RESOURCE'
      AND CONT.CNY_ID = COMP.ID(+)
      AND CONTR.I(+) = CONT.I
      AND CONTR.L(+) = 'en') RESULTS



      *************************

      The strange part is,the query runs in less than a minute in SQL Developer and returns 976604 rows.

      The tables data is as follows -

      select count(1) from Z_L_P_A_A -- 3539195 rows

      select count(1) from S_C -- 150754 rows

      select count(1) from S_C -- 1318 rows

      select count(1) from C_C_V -- 3840 rows

      The explain plan shows a cost = 38995 and a full table scan for tables S_C, S_CO and Z_L_P_A_A.

      We are Oracle 11g.

      Any ways to improve the performance on this query?

      Thanks