Home » RDBMS Server » Performance Tuning » Urgent, SQL tuning
Urgent, SQL tuning [message #65715] Tue, 14 December 2004 08:32 Go to next message
Sam
Messages: 255
Registered: April 2000
Senior Member
Hello every one, I have a performance problem. Users are complaining abt slow response of an SQL, for which the explain plan is given below. As u can see, it is using necessary indexes, is there any way to improve its performance?

 

  0      SELECT STATEMENT Optimizer=RULE
  1    0   FILTER
  2    1     NESTED LOOPS (OUTER)
  3    2       NESTED LOOPS (OUTER)
  4    3         NESTED LOOPS
  5    4           NESTED LOOPS
  6    5             NESTED LOOPS
  7    6               NESTED LOOPS
  8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'MTL_SYSTEM
         _ITEMS'

  9    8                   INDEX (RANGE SCAN) OF 'MTL_SYSTEM_ITEMS_N1
         ' (NON-UNIQUE)

 10    7                 TABLE ACCESS (BY INDEX ROWID) OF 'SO_LINE_DE
         TAILS'

 11   10                   INDEX (RANGE SCAN) OF 'SO_LINE_DETAILS_N2'
          (NON-UNIQUE)

 12    6               TABLE ACCESS (BY INDEX ROWID) OF 'SO_LINES_ALL
         '

 13   12                 INDEX (UNIQUE SCAN) OF 'SO_LINES_U1' (UNIQUE
         )

 14    5             TABLE ACCESS (BY INDEX ROWID) OF 'SO_HEADERS_ALL
         '

 15   14               INDEX (UNIQUE SCAN) OF 'SO_HEADERS_U1' (UNIQUE
         )

 16    4           TABLE ACCESS (BY INDEX ROWID) OF 'RA_CUSTOMERS'
 17   16             INDEX (UNIQUE SCAN) OF 'RA_CUSTOMERS_U1' (UNIQUE
         )

 18    3         TABLE ACCESS (BY INDEX ROWID) OF 'SO_LINES_ALL'
 19   18           INDEX (UNIQUE SCAN) OF 'SO_LINES_U1' (UNIQUE)
 20    2       TABLE ACCESS (BY INDEX ROWID) OF 'SO_LINES_ALL'
 21   20         INDEX (UNIQUE SCAN) OF 'SO_LINES_U1' (UNIQUE)
Re: Urgent, SQL tuning [message #65718 is a reply to message #65715] Tue, 14 December 2004 21:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
add 'AND 1 = 0' to your where clause. That will speed things up !

How do you think we would be able to see what is going on if you don't even provide the query ?

hth
Re: Urgent, SQL tuning [message #65720 is a reply to message #65718] Tue, 14 December 2004 21:35 Go to previous messageGo to next message
Sam
Messages: 255
Registered: April 2000
Senior Member
Sorry abt that Frank, Here is the query, it is basically creation of a view script and accesssing the script is taking lot of time.
Here is the query. Pls any more suggestions are welcome

SELECT
CUST.CUSTOMER_NAME A$Customer,
CUST.CUSTOMER_NUMBER A$Customer_Number,
ITEM.SEGMENT1 A$ITEM$ITEM_NUMBER,
ITEM.DESCRIPTION A$Item_Description,
LINES.OPEN_FLAG A$Line_Open_Flag,
HEAD.ORDER_NUMBER A$Order_Number,
LINES.ORIGINAL_SYSTEM_LINE_REFERENCE A$Original_System_Line_Referen,
HEAD.ORIGINAL_SYSTEM_REFERENCE A$Original_System_Reference,
'A$ZZ__________________________Copyright Noetix Corp ARIS Software Inc 1994-1998
'
A$ZZ__________________________,
NVL(LINES.ATO_FLAG,'N') Ato_Flag,
CUST.ATTRIBUTE1 CUST$Customer_Short_Name,
NVL(LINED.CONFIGURATION_ITEM_FLAG,'N') Configuration_Item_Flag,
CUST.CUSTOMER_NAME Customer,
CUST.CUSTOMER_NUMBER Customer_Number,
LINES.DATE_REQUESTED_CURRENT Customer_Requested_Date,
NVL(LINED.DEMAND_CLASS_CODE,
NVL(LINES.DEMAND_CLASS_CODE, HEAD.DEMAND_CLASS_CODE) ) Demand_Class,
HEAD.ATTRIBUTE2 HEAD$ACKNOWLEDGE_DATE,
HEAD.ATTRIBUTE1 HEAD$PROJECT,
ITEM.ATTRIBUTE5 ITEM$ER_Number,
ITEM.SEGMENT1 ITEM$ITEM_NUMBER,
ITEM.ATTRIBUTE6 ITEM$Item_Assembly_Line,
ITEM.ATTRIBUTE2 ITEM$PPAP_Pending,
ITEM.ATTRIBUTE3 ITEM$Tab_Drawing,
NVL(LINED.INCLUDED_ITEM_FLAG,'N') Included_Item_Flag,
ITEM.DESCRIPTION Item_Description,
LINED.REVISION Item_Revision,
LINES.ITEM_TYPE_CODE Item_Type_Code,
LINES.ATTRIBUTE2 LINES$SEQUENCE_NUMBER,
DECODE( LINES.CANCELLED_QUANTITY, LINES.ORDERED_QUANTITY,
'Y', NVL( HEAD.CANCELLED_FLAG, 'N') ) Line_Cancelled_Flag,
NVL(
LINES.CANCELLED_QUANTITY,0) * LINED.COMPONENT_RATIO Line_Cancelled_Quantity,
NVL(LINE2.LINE_NUMBER, NVL(LINE3.LINE_NUMBER, LINES.LINE_NUMBER)) Line_Number,
LINES.OPEN_FLAG Line_Open_Flag,
( NVL(LINES.ORDERED_QUANTITY,0) - NVL(LINES.SHIPPED_QUANTITY,0) -
NVL(LINES.CANCELLED_QUANTITY,0) ) * LINED.COMPONENT_RATIO Line_Open_Quantity,
DECODE(LINES.PARENT_LINE_ID, NULL, 0, LINES.LINE_NUMBER) Line_Option_Number,
NVL(LINES.ORDERED_QUANTITY,0) * LINED.COMPONENT_RATIO Line_Ordered_Quantity,
DECODE(LINES.SHIPMENT_SCHEDULE_LINE_ID, NULL, 1, DECODE(
LINES.PARENT_LINE_ID,
NULL, LINES.LINE_NUMBER, LINE3.LINE_NUMBER) ) Line_Shipment_Schedule_Number,
NVL(LINES.SHIPPED_QUANTITY,0) * LINED.COMPONENT_RATIO Line_Shipped_Quantity,
LINED.LOT_NUMBER Lot_Number,
HEAD.ORDER_NUMBER Order_Number,
decode(HEAD.ORDER_TYPE_ID,'1000','STANDARD ORDER','1001','RETURN ORDER',
'1002','ADD ONS','1003','PPAP','1004','TURBO ORDER','1005','PURCH/MACH PARTS',
'1006','RETURN ORDER W/O INVOICE','1007','UK ORDERS','1008','DAP','1009',
'PROTO CP (25%)','1010','PROTO CP (20%)','1011','PROTO CP (15%)','1012',
'PROTO MA (25%)','1013','PROTO MA (20%)','1014','PROTO MA (15%)','1029',
'PROTO CP(25%)','1030','PROTO CP(20%)','1031','PROTO CP(15%)','1032',
'PROTO MA(25%)','1033','PROTO MA(20%)','1034','PROTO MA(15%)','1049',
'Internal Order Type','1050','ENGINE DOWN ORDER','1070','PROTO SELL AT COST',
'1090','HD STANDARD ORDER','1091','HD RETURN ORDER','1092','HD ADD ONS',
'1093','HD ENGINE DOWN','1110','PROTO MA(EXT)',HEAD.ORDER_TYPE_ID) Order_Type,
LINES.ORIGINAL_SYSTEM_LINE_REFERENCE Original_System_Line_Reference,
HEAD.ORIGINAL_SYSTEM_REFERENCE Original_System_Reference,
HEAD.ORIGINAL_SYSTEM_SOURCE_CODE Original_System_Source_Code,
NVL(LINED.RELEASED_FLAG,'N') Pick_Released_Flag,
LINED.QUANTITY Quantity,
DECODE( LINED.RELEASED_FLAG, 'Y', LINED.QUANTITY, 0) Released_Quantity,
NVL( LINED.REQUIRED_FOR_REVENUE_FLAG, 'N') Required_For_Revenue_Flag,
DECODE(
LINED.SCHEDULE_STATUS_CODE, 'RESERVED', LINED.QUANTITY, 0) Reserved_Quantity,
HEAD.SALES_CHANNEL_CODE Sales_Channel,
decode(LINED.SCHEDULE_STATUS_CODE,'DEMANDED','Demanded','RESERVED',
'Reserved','SUPPLY RESERVED'
,'Supply Reserved',LINED.SCHEDULE_STATUS_CODE) Schedule_Status_Code,
NVL(LINED.SCHEDULE_DATE, LINES.SCHEDULE_DATE) Scheduled_Date,
LINED.SUBINVENTORY Subinventory,
decode(NVL(LINED.WAREHOUSE_ID, NVL(LINES.WAREHOUSE_ID, HEAD.WAREHOUSE_ID)),
'101','001','102','417','206','PRO','227','094',NVL(
LINED.WAREHOUSE_ID, NVL(LINES.WAREHOUSE_ID, HEAD.WAREHOUSE_ID))) Warehouse,
'Z$$_________________________' Z$$_________________________,
ITEM.rowid Z$INV1_Items,
LINED.rowid Z$OE_Line_Details,
LINES.rowid Z$OE_Lines,
HEAD.rowid Z$OE_Orders,
CUST.rowid Z$RAAR_Customers
FROM
OE.SO_LINES_ALL LINE3,
OE.SO_LINES_ALL LINE2,
INV.MTL_SYSTEM_ITEMS ITEM,
OE.SO_LINE_DETAILS LINED,
OE.SO_LINES_ALL LINES,
AR.RA_CUSTOMERS CUST,
OE.SO_HEADERS_ALL HEAD
WHERE 'Copyright Noetix Corp ARIS Software Inc 1994-1998' is not null
AND ITEM.ORGANIZATION_ID=101
AND HEAD.HEADER_ID=LINES.HEADER_ID
AND LINES.LINE_ID=LINED.LINE_ID
AND LINED.INVENTORY_ITEM_ID=ITEM.INVENTORY_ITEM_ID
AND LINE2.LINE_ID(+)=LINES.SHIPMENT_SCHEDULE_LINE_ID
AND LINE3.LINE_ID(+)=LINES.PARENT_LINE_ID
AND LINES.LINE_TYPE_CODE IN ('REGULAR','DETAIL')
AND CUST.CUSTOMER_ID=HEAD.CUSTOMER_ID
AND HEAD.ORDER_CATEGORY!='RMA'
AND NVL(HEAD.ORG_ID, 205) = 205
AND NVL(LINE2.ORG_ID (+),205) = 205
AND NVL(LINE3.ORG_ID (+),205) = 205
AND NVL(LINES.ORG_ID, 205) = 205
Re: Urgent, SQL tuning [message #65728 is a reply to message #65720] Wed, 15 December 2004 23:47 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
<quote>WHERE 'Copyright Noetix Corp ARIS Software Inc 1994-1998' is not null</quote>
??

Further, the query looks fine to me. Explain plan looks fine too in relation to the query.
Did this query perform better in the past? If yes, take a look at what changed since.
Also, check if your tables/indexes are analyzed.

Sorry, can't help you here :(
Previous Topic: Urgent:-SQL taking long time to execute
Next Topic: URGENT:-Tips for Tuning Stored Procedures
Goto Forum:
  


Current Time: Wed Sep 30 10:38:29 CDT 2020