Home » RDBMS Server » Performance Tuning » Consuming a more time (low performance) (Oracle8i Enterprise Edition Release 8.1.7.0.0, for 32-bit Windows)
Consuming a more time (low performance) [message #666636] Fri, 17 November 2017 23:43 Go to next message
shawaj
Messages: 89
Registered: January 2016
Member
Hello ,
I am facing performance issue when runs query.Here i have attached explain plan and view details that are evolving.
Please guide me, what i can do for better performance.

                               No of rows
select count(*) from OFF;            431183
select count(*) from strim;          397265
select count(*) from COMBO;           88810
select count(*) from COLOR;           84757
select count(*) from CONTRACT2;       83772
select count(*) from CONTRACT1;       78434
select count(*) from FABRIC;          58108
select count(*) from DELIVERY;        40605
select count(*) from STYLE;           35425
select count(*) from FAB1;             1059
select count(*) from FAB4;              881
select count(*) from LABEL;             762
select count(*) from FAB3;              760
select count(*) from FAB2;              683
select count(*) from FAB5;               75
select count(*) from FAB6;               74
SELECT COUNT(*) FROM trimconsumption; 404719
select count(*) from strim;           397265
SELECT COUNT(*) FROM tsize;           310248
SELECT COUNT(*) FROM ptcreq1;         174466
SELECT COUNT(*) FROM tcolor;          139811
and i m doing simple select ...
select * from ptcreq where po=:po_no;

create or replace view ptcreq as
(select "SCODE","PO","STYLENO","CONTNO","DELNO","OFFNO","LNAME","COMBONO","TCOLOR","SZCODE","TSZNAME","TRIMNO","TRCODE","CSDEP","LDEP","QTY","UNIT","CLUB","SOURCE","SUPNO","DESC1" from ptcreqb1)
union
(select "SCODE","PO","STYLENO","CONTNO","DELNO","OFFNO","LNAME","COMBONO","TCOLOR","SZCODE","TSZNAME","TRIMNO","TRCODE","CSDEP","LDEP","QTY","UNIT","CLUB","SOURCE","SUPNO","DESC1" from ptcreqc3)
union
(select "SCODE","PO","STYLENO","CONTNO","DELNO","OFFNO","LNAME","COMBONO","TCOLOR","SZCODE","TSZNAME","TRIMNO","TRCODE","CSDEP","LDEP","QTY","UNIT","CLUB","SOURCE","SUPNO","DESC1"  from ptcreqn3)
union
(select "SCODE","PO","STYLENO","CONTNO","DELNO","OFFNO","LNAME","COMBONO","TCOLOR","SZCODE","TSZNAME","TRIMNO","TRCODE","CSDEP","LDEP","QTY","UNIT","CLUB","SOURCE","SUPNO","DESC1" from ptcreqs3);

-------for ptcreqs3 ----------
1.
create or replace view ptcreqs3 as
select t.scode,
       t.po,
       t.styleno,
       t.contno,
       t.delno,
       t.offno,
       t.lname,
       t.combono,
       t.tcolor,
       t.szcode,
       t.tszname,
       t.trimno,
       t.trcode,
       t.desc1,
       t.csdep,
       t.ldep,
       decode(umd,
              'M',
              t.qty * factor,
              'D',
              t.qty / decode(factor, 0, 1, factor)) qty,
       t2utord unit,
       t.club,
       t.source,
       t.supno
  from ptcreqs2 t, trim, t2mst, unitref
 where t.trcode = trim.trcode
   and trim.t2code = t2mst.t2code
   and unitref.ucode = t.unit
   and unitref.cucode = t2mst.t2utord;
   
2.
create or replace view ptcreqs2 as
select scode,
       po,
       t.styleno,
       contno,
       delno,
       offno,
       lname,
       0 combono,
       null tcolor,
       t.szcode,
       tszname,
       s.trimno,
       s.trcode,
       s.desc1,
       csdep,
       ldep,
       ((nvl(cons.consumption, 0) / decode(cons.gmtpcs, 0, 1, cons.gmtpcs)) +
       ((nvl(cons.consumption, 0) / decode(cons.gmtpcs, 0, 1, cons.gmtpcs)) *
       (nvl(cons.wastage, 0) / 100))) * qty qty,
       cons.unit,
       club,
       source,
       supno
  from ptcreqS1 t, strim s, trimconsumption cons, tsize sz
 where t.styleno = s.styleno
   and csdep = 'S'
   and s.styleno = cons.styleno
   and s.trimno = cons.trimno
   and s.trcode = cons.trcode
   and t.szcode = decode(cons.szcode, null, t.szcode, cons.szcode)
   and s.styleno = sz.styleno
   and s.trimno = sz.trimno
   and t.szcode = sz.szcode;
   
3.
create or replace view ptcreqs1 as
select s.scode,
       s.po,
       c1.styleno,
       c1.contno,
       c1.delno,
       c2.offno,
       lname,
       off.szcode,
       sum(no_of_off * off.qty) qty
  from style s, contract1 c1, contract2 c2, off, label l, delivery d
 where s.styleno > 50500
   and s.styleno = c1.styleno
   and c1.styleno = c2.styleno
   and c1.contno = c2.contno
   and c2.styleno = off.styleno
   and c2.offno = off.offno
   and c2.lcode = l.lcode
   and c1.styleno = d.styleno
   and c1.delno = d.delno
   and NVL(d.flag, 'U') != 'C'
--and exists (select 'Y' from pomst pm where pm.pocode=s.po and pm.podate>=trunc(sysdate)-1440)
 group by s.scode,
          s.po,
          c1.styleno,
          c1.contno,
          c1.delno,
          c2.offno,
          lname,
          off.szcode;


--------------for ptcreqn3

1.
create or replace view ptcreqn3 as
select t.scode,
       t.po,
       t.styleno,
       t.contno,
       t.delno,
       t.offno,
       t.lname,
       t.combono,
       t.tcolor,
       t.szcode,
       t.tszname,
       t.trimno,
       t.trcode,
       t.desc1,
       t.csdep,
       t.ldep,
       decode(umd,
              'M',
              t.qty * factor,
              'D',
              t.qty / decode(factor, 0, 1, factor)) qty,
       t2utord unit,
       t.club,
       t.source,
       t.supno
  from ptcreqn2 t, trim, t2mst, unitref
 where t.trcode = trim.trcode
   and trim.t2code = t2mst.t2code
   and unitref.ucode = t.unit
   and unitref.cucode = t2mst.t2utord;
2.
create or replace view ptcreqn2 as
select scode,
       po,
       t.styleno,
       contno,
       delno,
       offno,
       lname,
       0 combono,
       null tcolor,
       0 szcode,
       null tszname,
       s.trimno,
       s.trcode,
       s.desc1,
       csdep,
       ldep,
       ((nvl(cons.consumption, 0) / decode(cons.gmtpcs, 0, 1, cons.gmtpcs)) +
       ((nvl(cons.consumption, 0) / decode(cons.gmtpcs, 0, 1, cons.gmtpcs)) *
       (nvl(cons.wastage, 0) / 100))) * qty qty,
       cons.unit,
       club,
       source,
       supno
  from ptcreqN1 t, strim s, trimconsumption cons
 where t.styleno = s.styleno
   and csdep = 'N'
   and s.styleno = cons.styleno
   and s.trimno = cons.trimno
   and s.trcode = cons.trcode;
3.
create or replace view ptcreqn1 as
select s.scode,
       s.po,
       c1.styleno,
       c1.contno,
       c1.delno,
       c2.offno,
       lname,
       sum(no_of_off * off.qty) qty
  from style s, contract1 c1, contract2 c2, off, label l, delivery d
 where s.styleno > 50500
   and s.styleno = c1.styleno
   and c1.styleno = c2.styleno
   and c1.contno = c2.contno
   and c2.lcode = l.lcode
   and c2.styleno = off.styleno
   and c2.offno = off.offno
   and c1.styleno = d.styleno
   and c1.delno = d.delno
   and NVL(d.flag, 'U') != 'C'
--and exists (select 'Y' from pomst pm where pm.pocode=s.po and pm.podate>=trunc(sysdate)-1440)
 group by s.scode, s.po, c1.styleno, c1.contno, c1.delno, c2.offno, lname;

---------for ptcreqc3

1.
create or replace view ptcreqc3 as
select t.scode,
       t.po,
       t.styleno,
       t.contno,
       t.delno,
       t.offno,
       t.lname,
       t.combono,
       t.tcolor,
       t.szcode,
       t.tszname,
       t.trimno,
       t.trcode,
       t.desc1,
       t.csdep,
       t.ldep,
       decode(umd,
              'M',
              t.qty * factor,
              'D',
              t.qty / decode(factor, 0, 1, factor)) qty,
       t2utord unit,
       t.club,
       t.source,
       t.supno
  from unitref, t2mst, trim, ptcreqc2 t
 where t.trcode = trim.trcode
   and trim.t2code = t2mst.t2code
   and unitref.ucode = t.unit
   and unitref.cucode = t2mst.t2utord;
2.
create or replace view ptcreqc2 as
select scode,
       po,
       t.styleno,
       contno,
       delno,
       offno,
       lname,
       t.combono,
       tcolor,
       0 szcode,
       null tszname,
       s.trimno,
       s.trcode,
       s.desc1,
       csdep,
       ldep,
       ((nvl(cons.consumption, 0) / decode(cons.gmtpcs, 0, 1, cons.gmtpcs)) +
       ((nvl(cons.consumption, 0) / decode(cons.gmtpcs, 0, 1, cons.gmtpcs)) *
       (nvl(cons.wastage, 0) / 100))) * qty qty,
       cons.unit,
       club,
       source,
       supno
  from ptcreqC1 t, strim s, trimconsumption cons, tcolor c
 where t.styleno = s.styleno
   and csdep = 'C'
   and s.styleno = cons.styleno
   and s.trimno = cons.trimno
   and s.trcode = cons.trcode
   and s.styleno = c.styleno
   and s.trimno = c.trimno
   and t.combono = c.combono
---and exists (select 'Y' from pomst pm,style stl where pm.pocode=stl.po and stl.styleno=s.styleno and pm.podate>=trunc(sysdate)-1440)
;
3.
create or replace view ptcreqc1 as
select s.scode,
       s.po,
       c1.styleno,
       c1.contno,
       c1.delno,
       c2.offno,
       lname,
       off.combono,
       sum(no_of_off * off.qty) qty
  from style s, contract1 c1, contract2 c2, off, label l, delivery d
 where S.STYLENO = D.STYLENO
   AND D.STYLENO = C1.STYLENO
   AND D.DELNO = C1.DELNO
   AND C1.STYLENO = C2.STYLENO
   AND C1.CONTNO = C2.CONTNO
   AND C2.STYLENO = OFF.STYLENO
   AND C2.OFFNO = OFF.OFFNO
   AND C2.LCODE = L.LCODE
   AND NVL(D.FLAG, ' ') != 'C'
   AND S.STYLENO > 50500
--and exists (select 'Y' from pomst pm where pm.pocode=s.po and pm.podate>=trunc(sysdate)-1440)
 group by s.scode,
          s.po,
          c1.styleno,
          c1.contno,
          c1.delno,
          c2.offno,
          lname,
          off.combono;

--------for ptcreqb1
1.
create or replace view ptcreqb1 as
select t.scode,
       t.po,
       t.styleno,
       t.contno,
       t.delno,
       t.offno,
       t.lname,
       t.combono,
       t.tcolor,
       t.szcode,
       t.tszname,
       t.trimno,
       t.trcode,
       t.desc1,
       t.csdep,
       t.ldep,
       decode(umd,
              'M',
              t.qty * factor,
              'D',
              t.qty / decode(factor, 0, 1, factor)) qty,
       t2utord unit,
       t.club,
       t.source,
       t.supno
  from ptcreqb t, trim, t2mst, unitref
 where t.trcode = trim.trcode
   and trim.t2code = t2mst.t2code
   and unitref.ucode = t.unit
   and unitref.cucode = t2mst.t2utord;
2.
create or replace view ptcreqb as
select scode,
       po,
       t.styleno,
       contno,
       delno,
       offno,
       lname,
       t.combono,
       tcolor,
       t.szcode,
       tszname,
       s.trimno,
       s.trcode,
       csdep,
       ldep,
       ((nvl(cons.consumption, 0) / decode(cons.gmtpcs, 0, 1, cons.gmtpcs)) +
       ((nvl(cons.consumption, 0) / decode(cons.gmtpcs, 0, 1, cons.gmtpcs)) *
       (nvl(cons.wastage, 0) / 100))) * qty qty,
       cons.unit,
       club,
       source,
       supno,
       desc1
  from ptcreq1 t, strim s, trimconsumption cons, tcolor c, tsize sz
 where t.styleno = s.styleno
   and csdep = 'B'
   and s.styleno = cons.styleno
   and s.trimno = cons.trimno
   and s.trcode = cons.trcode
   and t.szcode = decode(cons.szcode, null, t.szcode, cons.szcode)
   and s.styleno = c.styleno
   and s.trimno = c.trimno
   and t.combono = c.combono
   and s.styleno = sz.styleno
   and s.trimno = sz.trimno
   and t.szcode = sz.szcode
--and exists(select 'Y' from pomst pm,style stl where pm.pocode=stl.po and stl.styleno=t.styleno and stl.styleno=s.styleno and pm.pocode = stl.po and pm.podate >=TRUNC(SYSDATE)-1440)
;

3.
create or replace view ptcreq1 as
select s.scode,
       s.po,
       c1.styleno,
       c1.contno,
       c1.delno,
       c2.offno,
       lname,
       off.combono,
       off.szcode,
       sum(off.qty) qty
  from --style s, contract1 c1, contract2 c2, off, label l, delivery d
       label l,style s, delivery d, contract1 c1, contract2 c2,off
 where S.STYLENO = D.STYLENO
   AND D.STYLENO = C1.STYLENO
   AND D.DELNO = C1.DELNO
   AND C1.STYLENO = C2.STYLENO
   AND C1.CONTNO = C2.CONTNO
   AND C2.STYLENO = OFF.STYLENO
   AND C2.OFFNO = OFF.OFFNO
   AND C2.LCODE = L.LCODE
   AND NVL(D.FLAG, ' ') != 'C'
   AND S.STYLENO > 50500
   --and exists(select 'Y' from pomst pm where pm.pocode = s.po and pm.podate >=TRUNC(SYSDATE)-1440)
 GROUP BY s.scode,
          s.po,
          c1.styleno,
          c1.contno,
          c1.delno,
          c2.offno,
          lname,
          off.combono,
          off.szcode;

Thanks in advance.
  • Attachment: xplan.png
    (Size: 221.36KB, Downloaded 2084 times)

Report message to a moderator

Re: Consuming a more time (low performance) [message #666637 is a reply to message #666636] Sat, 18 November 2017 01:55 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
Your views are not mergeable, because they have UNION and GROUP BY. Are there going to be any duplicates? If not, use UNION ALL. If yes, perhaps remove them later on. Same with the GROUP BY: can you not do the aggregations later in the query, not in the views?

Report message to a moderator

Re: Consuming a more time (low performance) [message #667218 is a reply to message #666637] Fri, 15 December 2017 03:28 Go to previous messageGo to next message
JohnMax
Messages: 3
Registered: December 2017
Junior Member
Many views have group by but neither of them use the condition po=:po_no to filter data.

Consider to rewrite the SQL instead of view?

Report message to a moderator

Re: Consuming a more time (low performance) [message #667226 is a reply to message #667218] Fri, 15 December 2017 07:06 Go to previous message
JohnMax
Messages: 3
Registered: December 2017
Junior Member
If these views can not be rewritten to one query, please show us the scripts to create tables and indexes.

I might give you a better solution.

Report message to a moderator

Previous Topic: Query taking 20 minutes in Production
Next Topic: Non-value Added Indexes
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 23 14:47:25 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.