Home » RDBMS Server » Performance Tuning » Performance Issue (Oracle8i Enterprise Edition Release 8.1.7.0.0,TNS for 32-bit Windows)
Performance Issue [message #666492] Thu, 09 November 2017 00:11 Go to next message
shawaj
Messages: 50
Registered: January 2016
Member
Hi to all
I have view TRPS_MCHNT_FRNT but when i query from this it takes long time
Please guide me how it go fast.
 CREATE OR REPLACE VIEW TRPS_MCHNT_FRNT 
  AS
   SELECT PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME M3FULL,POCODE 
   PO,PM.SCODE,PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,STYLEBUY,DESCRIPTION,
   FAB1NAME FABRIC,VNAME,SUM(QTY) QTY
 FROM 
   PO_MCHNT_BYR PM,FABRIC F,DELIVERY D,VENDOR V,FAB1 F1
 WHERE PM.STYLENO=F.STYLENO(+) 
       AND PM.STYLENO=D.STYLENO(+) 
       AND D.VCODE=V.VCODE(+) 
       AND F.FAB1NO=F1.FAB1NO(+) 
       AND F.MAINALLIED(+)='M' 
       AND NVL(D.FLAG(+),'S')<>'C' 
       AND PM.POSTRING<>'Z'
 GROUP BY 
      PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME,POCODE,PM.SCODE,PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,STYLEBUY,
      DESCRIPTION,FAB1NAME,VNAME;

CREATE OR REPLACE VIEW PO_MCHNT_BYR 
	AS
	SELECT P.SHCODE,P.LOCNO,POCODE,P.ACTIVE,SHIP_FLAG,P.POSTRING,P.B1CODE,S.SCODE,STYLENO,STYLEBUY,S.DESCRIPTION,
	U1.UCODE MANAGER,U2.UCODE,U0.LNAME||'/'||U1.LNAME||'/'||U2.LNAME MNAME,B3.B3FULL
FROM 
	USERMST U0,USERMST U1,USERMST U2,POMST P,STYLE S,BUYER1 B1,BUYER2 B2,BUYER3 B3
WHERE 
	U0.UCODE=U1.MANAGER 
	AND U1.UCODE=P.MANAGER 
	AND U1.UCODE=U2.MANAGER 
	AND U2.UCODE=P.UCODE 
	AND P.POCODE=S.PO 
	AND S.B1CODE=B1.B1CODE 
	AND S.B2CODE=B2.B2CODE 
	AND S.B3CODE=B3.B3CODE 
	AND B3.B2CODE=B2.B2CODE 
	AND B3.B1CODE=B1.B1CODE 
	AND B2.B1CODE=B1.B1CODE 
	AND P.ACTIVE<>'C' 
ORDER 
	BY 1;


All tables structur and test data is attached.
Re: Performance Issue [message #666495 is a reply to message #666492] Thu, 09 November 2017 02:11 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
You say you want to tune the view TRPS_MCHNT_FRNT and then you give the definition of another view and the tables it uses. Can you be a bit more organized in the way you describe the problem?
Re: Performance Issue [message #666497 is a reply to message #666495] Thu, 09 November 2017 02:30 Go to previous messageGo to next message
Roachcoach
Messages: 1498
Registered: May 2010
Location: UK
Senior Member
>>Oracle8i Enterprise Edition Release 8.1.7.0.0

I'm out.
Re: Performance Issue [message #666500 is a reply to message #666497] Thu, 09 November 2017 03:43 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
First thing you need to do: get rid of that order by in the 2nd view. Views should never contain order by, unless they're doing top-n style queries. What if you want the data the view provides in a different order?
Second thing - add table aliases to all the columns in the views that don't have them so we can see what columns come from what tables.
Third - provide the query you are using against the view along with an explain plan.
Fourth - explain the data model - it looks deeply odd, why have you got 3 buyer tables?
Re: Performance Issue [message #666515 is a reply to message #666500] Thu, 09 November 2017 23:13 Go to previous messageGo to next message
shawaj
Messages: 50
Registered: January 2016
Member
I am using simple select as below.
 SQL> SELECT * FROM TRPS_MCHNT_FRNT;
Re: Performance Issue [message #666517 is a reply to message #666500] Fri, 10 November 2017 01:59 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
Quote:
First thing you need to do: get rid of that order by in the 2nd view. Views should never contain order by, unless they're doing top-n style queries. What if you want the data the view provides in a different order?
Furthermore, the ORDER BY is forcing Oracle to materialize the view, so it can't be merged into the outer query. This is shown in the execution plan.
Re: Performance Issue [message #666526 is a reply to message #666517] Sat, 11 November 2017 00:59 Go to previous message
shawaj
Messages: 50
Registered: January 2016
Member
Thanks
Previous Topic: Undo tablespace(monitoring and avoiding ORA-01555
Next Topic: Query taking 20 minutes in Production
Goto Forum:
  


Current Time: Mon Nov 20 09:22:09 CST 2017

Total time taken to generate the page: 0.12376 seconds