Home » RDBMS Server » Performance Tuning » Performance Issue Problem
Performance Issue Problem [message #182916] Tue, 18 July 2006 12:56 Go to next message
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 1246 times)

Report message to a moderator

Re: Performance Issue Problem [message #183004 is a reply to message #182916] Wed, 19 July 2006 02:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 1283 times)

Report message to a moderator

Re: Performance Issue Problem [message #183257 is a reply to message #183158] Thu, 20 July 2006 03:09 Go to previous message
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon May 06 13:12:44 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.