Home » RDBMS Server » Performance Tuning » quick tuning
quick tuning [message #237658] Tue, 15 May 2007 07:23 Go to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 #237686 is a reply to message #237666] Tue, 15 May 2007 08:18 Go to previous messageGo to next message
eb222
Messages: 7
Registered: May 2007
Junior Member
hi

have attached the explainplan i obtained from oracle -please look and any help will be appreciated -im new to this tuning thing.

[Updated on: Tue, 15 May 2007 08:20]

Report message to a moderator

Re: quick tuning [message #237749 is a reply to message #237686] Tue, 15 May 2007 10:14 Go to previous messageGo to next message
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 #237802 is a reply to message #237658] Tue, 15 May 2007 13:59 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Can you post TKPROF report for the query as well?
Re: quick tuning [message #237964 is a reply to message #237658] Wed, 16 May 2007 03:53 Go to previous messageGo to next message
eb222
Messages: 7
Registered: May 2007
Junior Member
how do i do a tkprof of the sql?
icon2.gif  Re: quick tuning [message #237970 is a reply to message #237658] Wed, 16 May 2007 03:58 Go to previous messageGo to next message
krishan_334@yahoo.co.in
Messages: 1
Registered: May 2007
Junior Member
i donot now
Re: quick tuning [message #238701 is a reply to message #237970] Sat, 19 May 2007 07:25 Go to previous messageGo to next message
skooman
Messages: 912
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 Go to previous message
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.

Previous Topic: DELETE with Parallel - Parallel is not working
Next Topic: SQL Tuning
Goto Forum:
  


Current Time: Sat Dec 03 06:03:29 CST 2016

Total time taken to generate the page: 0.13595 seconds