Home » RDBMS Server » Performance Tuning » Urgent, SQL tuning
 Urgent, SQL tuning [message #65715] |
Tue, 14 December 2004 08:32  |
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 #65720 is a reply to message #65718] |
Tue, 14 December 2004 21:35  |
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 |
Frank
Messages: 7901
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 :(
|
|
|
|
Goto Forum:
Current Time: Sun May 04 04:15:55 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
