| quick tuning [message #237658] |
Tue, 15 May 2007 07:23  |
eb222
Messages: 7
Registered: May 2007
|
Junior Member |
|
|
below is a view
its doing a full scan on OE_ORDER_LINES_ALL. may need indexes? please help.
SELECT 'X'
FROM
OE_ORDER_LINES_ALL OOL ,
OE_ORDER_HEADERS_ALL OOH ,
MTL_SALES_ORDERS MSO ,
MTL_DEMAND_OM_VIEW MDV
WHERE OOL.HEADER_ID = OOH.HEADER_ID
AND OOH.ORDER_NUMBER = MSO.SEGMENT1
AND MSO.SALES_ORDER_ID = MDV.DEMAND_SOURCE_HEADER_ID
AND OOL.LINE_ID = MDV.DEMAND_SOURCE_LINE
AND OOH.ORDER_NUMBER >1
AND OOL.LINE_NUMBER >1
AND OOH.ORG_ID =95 AND
OOL.ORG_ID = 95 AND NVL(MDV.OPEN_FLAG,'Y') != 'N' AND
((NVL(OOL.VISIBLE_DEMAND_FLAG,'N') = 'Y'
AND OOL.FLOW_STATUS_CODE NOT IN
('FULFILLED','SHIPPED','INVOICED','CANCELLED'))
OR
(NVL(OOL.VISIBLE_DEMAND_FLAG,'N') = 'N'
AND OOL.FLOW_STATUS_CODE IN
('ENTERED','BOOKED'))) AND ((MDV.RESERVATION_QUANTITY > 0 AND
MDV.COMPLETED_QUANTITY >= 0) OR (MDV.RESERVATION_QUANTITY = 0 AND
MDV.COMPLETED_QUANTITY = 0))
|
|
|
|
| Re: quick tuning [message #237666 is a reply to message #237658] |
Tue, 15 May 2007 07:42  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. You don't have any selective conditions (except joins) in your where clause, so at least 1 full table scan will be used.
2. It may be better to use HASH join instead of NL, so FTS on other tables may actually to improve performance.
3. Without knowing explain, stats and indexes of involved tables - difficult to say something else.
HTH.
Michael
|
|
|
|
|
|
| Re: quick tuning [message #237749 is a reply to message #237686] |
Tue, 15 May 2007 10:14 |
eb222
Messages: 7
Registered: May 2007
|
Junior Member |
|
|
hi
no replys so far -can someone help me
how do i craete the hash joins also what index do i need on the full table scan -have sent the explain plan output and stats
|
|
|
|
|
|
|
|
|
|
| Re: quick tuning [message #238701 is a reply to message #237970] |
Sat, 19 May 2007 07:25 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Maybe you should have a look at the definition of the view mtl_demand_om_view, because in the plan an outer join not in the statement itself shows up:
"MD1"."DEMAND_SOURCE_LINE"(+)=TO_CHAR("OOL"."LINE_ID")
So, it looks as if OOL is selected from again within the view.
Furthermore, to help you with this, we need the number of rows of each table involved and for each where clause an indication if the are any useful indexes and/or how determinative the filters are.
PS there is no such thing as quick tuning in apps - the datamodel is too complex for that. And furthermore, you are not allowed to add custom indexes, except if you use function based indexes, these will not be used by other (standard) select statements but will affect the performance of updates/insert/deletes so be very careful with that.
[Updated on: Sat, 19 May 2007 07:27] Report message to a moderator |
|
|
|
| Re: quick tuning [message #244231 is a reply to message #238701] |
Tue, 12 June 2007 01:50 |
amol_umbarkar
Messages: 3
Registered: June 2007
|
Junior Member |
|
|
Hi,
This is probably a late reply.
You could merge the mtl_demand_view into this query. This is eliminate redundant reads on OE_ORDER_LINES_ALL.
Also check the number of records/blocks in MTL_DEMAND. I am assuming that these will always significantly less than oe_order_lines_all. So its ok if you do a full scan of MTL_DEMAND and then fetch order lines based on that.
Also look for indexes on MTL_DEMAND and if you can use any.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
