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 #65718 is a reply to message #65715] |
Tue, 14 December 2004 21:29  |
Frank
Messages: 7901
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 |
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: Fri Nov 29 20:25:35 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
