OraFAQ Forum: Performance Tuning » Performance Issue Problem

Home » RDBMS Server » Performance Tuning » Performance Issue Problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance Issue Problem [message #182916]

Tue, 18 July 2006 12:56

kameshindia
Messages: 2
Registered: July 2006

Junior Member

Please help me in tuning this query

Plan execution is attached with this.

SELECT a.end_item_part_number
,a.sub_kit_code
,c.default_location
,c.kit_code
,a.part_number
,NVL(d.kit_qty,0) kit_qty
,to_number(DECODE(RPAD(c.default_location,8), b.inventory_location,to_char(b.inventory_qty),'0')) inventory_qty
,(to_number(DECODE(RPAD(c.default_location,8), b.inventory_location,to_char(b.inventory_qty),'0')) + report_pkg.incomplete_skits_fnc(a.end_item_part_number,a.site_code,a.sub_kit_code,c.default_location,a.part_number)) / (DECODE(NVL(d.kit_qty,0),0,b.inventory_qty+1,d.kit_qty)) no_of_sub_kits
,c.sub_kit_container
,report_pkg.incomplete_skits_fnc(a.end_item_part_number,a.site_code,a.sub_kit_code,c.default_location,a.part_number) incomplete
FROM pss_sub_kit_structure a,
pss_inventory b,
pss_kit_structure c,
pss_pack_design d
WHERE a.end_item_part_number = c.end_item_part_number
AND ltrim(rtrim(c.default_location)) = DECODE('NULL','NULL',ltrim(rtrim(c.default_location)),'NULL')
AND c.kit_code = DECODE('NULL','NULL',c.kit_code,'NULL')
AND a.site_code ='E'
AND a.sub_kit_code = c.sub_kit_code
AND a.site_code = c.site_code
AND d.rowid = (SELECT MAX(x.ROWID)
FROM pss_pack_design x,
pss_pack_design_release y
WHERE x.pack_design_seq_id = y.pack_design_seq_id
AND y.part_number = a.part_number
AND y.part_destination = c.kit_code
AND x.kit_qty is not null
AND x.kit_qty > 0
AND x.pick_pack_ind = 0
AND y.pick_pack_ind = 0)
AND a.part_number = b.part_number (+)
AND a.site_code = b.site_code (+)
AND (c.default_location = b.inventory_location
OR b.inventory_location is null)
AND b.inventory_location NOT LIKE 'XXX%'
AND (a.end_item_part_number || '|' || a.sub_kit_code) IN
(
SELECT a.end_item_part_number || '|' || a.sub_kit_code
FROM pss_sub_kit_structure a,
pss_inventory b,
pss_kit_structure c,
pss_pack_design d
WHERE a.end_item_part_number = c.end_item_part_number
AND ltrim(rtrim(c.default_location)) = DECODE('NULL','NULL',ltrim(rtrim(c.default_location)),'NULL')
AND c.kit_code = DECODE('NULL','NULL',c.kit_code,'NULL')
AND a.site_code = 'E'
AND a.sub_kit_code = c.sub_kit_code
AND a.site_code = c.site_code
AND d.rowid = (SELECT MAX(x.ROWID)
FROM pss_pack_design x,
pss_pack_design_release y
WHERE x.pack_design_seq_id = y.pack_design_seq_id
AND y.part_number = a.part_number
AND y.part_destination = c.kit_code
AND x.kit_qty is not null
AND x.kit_qty > 0
AND x.pick_pack_ind = 0
AND y.pick_pack_ind = 0)
AND a.part_number = b.part_number (+)
AND a.site_code = b.site_code (+)
AND (c.default_location = b.inventory_location
OR b.inventory_location is null)
AND b.inventory_location NOT LIKE 'XXX%'
GROUP BY
a.end_item_part_number,
a.sub_kit_code
HAVING min((to_number(DECODE(RPAD(c.default_location,8), b.inventory_location,
to_char(b.inventory_qty),'0')) + report_pkg.incomplete_skits_fnc(a.end_item_part_number,a.site_code,a.sub_kit_code,c.default_location,a.part_number)) /
(DECODE(NVL(d.kit_qty,0),0,b.inventory_qty+1,d.kit_qty))) >= 1
)
ORDER BY 1,2

Attachment: plan.xls
(Size: 33.00KB, Downloaded 1377 times)

Report message to a moderator

Re: Performance Issue Problem [message #183004 is a reply to message #182916]

Wed, 19 July 2006 02:52

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

What is this fascination with adding Excel attachments to posts. We're going to need to see the plan - just post it!
I don't have Excel on this box, so I'm flying a bit blind here.

You can try losing the outer join to 'b' - the condition

AND b.inventory_location NOT LIKE 'XXX%'

will reject any rows where inventory_location is null. Removing this will free up the optimiser to consider more plans.
It will also let you remove the 'OR b.inventory_location is null' which will speed things up.

Your use of DECODE is, well, unique. This piece of code

DECODE('NULL','NULL',ltrim(rtrim(c.default_location)),'NULL')

says
'Check the string "NULL". If it is equal to the string "NULL", then return the trimmed default location'
So, the condition:

AND ltrim(rtrim(c.default_location)) = DECODE('NULL','NULL',ltrim(rtrim(c.default_location)),'NULL')

is always true, unless default_Location is null, in whaich case it is false.

A similar situation applies for

AND c.kit_code = DECODE('NULL','NULL',c.kit_code,'NULL')

Your code 'SELECT MAX(rowid)....' is highly suspect. You do know that the maximum rowid is not automatically the most recently inserted record, don't you?

I suspect the real killer from a performance point of view is this bit:

AND (a.end_item_part_number || '|' || a.sub_kit_code) IN
(
SELECT a.end_item_part_number || '|' || a.sub_kit_code...

The sub query you are using is identical to the main query.
What you seem to be saying is

Quote:

Get me all the rows that meet this set of conditions, AND where these two columns are in the set of rows that meet these conditions.

I really don't think you need that entire sub query.

Report message to a moderator

Re: Performance Issue Problem [message #183158 is a reply to message #183004]

Wed, 19 July 2006 13:39

kameshindia
Messages: 2
Registered: July 2006

Junior Member

I was unable to paste the plan in the message box, so I'm uploading the plan in the form of text file.

Attachment: plan.txt
(Size: 7.11KB, Downloaded 1417 times)

Report message to a moderator

Re: Performance Issue Problem [message #183257 is a reply to message #183158]

Thu, 20 July 2006 03:09

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Did you actually look at that plan before youposted it?
It's unreadable - your columns are set too narrow, and a lot of data has been truncated.

Did you try any of my suggestions, and what effet did they have?

Report message to a moderator

Previous Topic:	DROP and CREATE INDEX and need for ANALYZE INDEX ... ESTIMATE STATISTICS
Next Topic:	Bitmap Index and Histogram

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 12:31:27 CST 2024