Oracle Optimization help?

Post #216,531 by SpiceWare 7/26/05 2:57:41 PM Reply	Oracle Optimization help? In the following query, if the first & last lines(in blue) are dropped the query takes < 1 second. With them the query sometimes takes < 1 second, but mostly takes > 20 seconds. I've done nested queries like this many times before w/out a performance hit. Any ideas? select * from (\nselect w.wo_num,\n rt_sub_wo.total_through_function(w.ccn, w.mas_loc, w.wo_num, w.wo_line, 'INSPECT') inspect\n from wo w,\n c_expedite_job c\n where w.ccn = c.ccn\n and w.mas_loc = c.mas_loc\n and w.wo_num = c.wo_num\n and w.wo_line = c.wo_line \n and w.mfg_close_date is null\n and w.complete_qty < w.ord_qty\n ) where inspect > 0 The PL/SQL functions are as follows: from PACKAGE\n FUNCTION total_through_function(\n i_ccn IN lab_tim.ccn%TYPE,\n i_ml IN lab_tim.mas_loc%TYPE,\n i_wo IN lab_tim.wo_num%TYPE,\n i_line IN lab_tim.wo_line%TYPE,\n i_function IN wo_rtg.function_%TYPE\n )\n RETURN NUMBER;\n\n PRAGMA RESTRICT_REFERENCES (total_through_function, WNDS, WNPS, RNPS);\n\n\nfrom PACKAGE BODY\n FUNCTION total_through_function(\n i_ccn IN lab_tim.ccn%TYPE,\n i_ml IN lab_tim.mas_loc%TYPE,\n i_wo IN lab_tim.wo_num%TYPE,\n i_line IN lab_tim.wo_line%TYPE,\n i_function IN wo_rtg.function_%TYPE\n )\n RETURN NUMBER\n IS\n return_value INTEGER;\n BEGIN\n SELECT sum(r.comp_qty) INTO return_value\n FROM wo_rtg r\n WHERE r.CCN = i_ccn\n AND r.MAS_LOC = i_ml\n AND r.WO_NUM = i_wo\n AND r.WO_LINE = i_line\n AND r.PAY_POINT = 'Y'\n AND r.function_ = i_function;\n\n RETURN return_value;\n END total_through_function; Darrell Spice, Jr. [link\|http://spiceware.org/gallery/ArtisticOverpass\|Artistic Overpass]\n[link\|http://www.spiceware.org/\|SpiceWare] - We don't do Windows, it's too much of a chore
Post #216,678 by SpiceWare 7/27/05 3:46:31 PM Reply	I blew out the query It performs consistantly under 1 second. Not as easy to read though - the full query returns a number of sums to provide visibility of where a WO is currently at on the production floor(wo_rtg is the routing file). Oh well. select * from (\nSelect w.WO_NUM,\n (select NVL(sum(comp_qty),0) from wo_rtg where ccn = w.ccn and mas_loc = w.mas_loc and wo_num = w.wo_num\n and wo_line = w.wo_line and pay_point = 'Y' and function_ = 'INSPECT') inspect,\n'PACK') PACK\n FROM WO W,\n C_EXPEDITE_JOB C\n WHERE W.CCN = C.CCN\n AND W.MAS_LOC = C.MAS_LOC\n AND W.WO_NUM = C.WO_NUM\n and w.wo_line = c.wo_line \n AND MFG_CLOSE_DATE IS NULL \n AND W.COMPLETE_QTY < W.ORD_QTY \n ) where inspect > 0 Darrell Spice, Jr. [link\|http://spiceware.org/gallery/ArtisticOverpass\|Artistic Overpass]\n[link\|http://www.spiceware.org/\|SpiceWare] - We don't do Windows, it's too much of a chore
Post #216,695 by ben_tilly 7/27/05 5:04:39 PM Reply	One trick that might work with the original... ALTER SESSION SET SORT_AREA_SIZE=10000000 Sometimes Oracle will switch query plans if it thinks that the amount of temporary space needed for the best one might pass a certain threshold. The above command lets you change that threshold. Of course if every query does that and uses the space, then your server will fall over due into heavy swap. So discuss with a DBA before using this. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #216,698 by SpiceWare 7/27/05 5:24:41 PM Reply	Would that help for a small result set? the query normally returns < 10 rows. It's a way for production to track the "hot jobs", of which there should hopefully not be many. Darrell Spice, Jr. [link\|http://spiceware.org/gallery/ArtisticOverpass\|Artistic Overpass]\n[link\|http://www.spiceware.org/\|SpiceWare] - We don't do Windows, it's too much of a chore
Post #216,708 by ben_tilly 7/27/05 6:04:34 PM Reply	It might The optimizer relies on statistics to figure out how much work it will need to do. When it can't project very well it has to be pessimistic. If you're doing something that it can't project very well (which I'd think calling PL/SQL would be), it may be using an internal estimate of rows returned and space used which bears no relation to what you actually wind up with. In that case setting the sort size shouldn't hurt because (but check this with a DBA!) Oracle doesn't actually reserve the space until it needs it. You're just telling it how much space it should be prepared to reserve. So you're just fooling it into using the query plan that you want. Also if you have competent DBAs, then you should be talking to them for another reason. There are techniques that they can use to get a particular query plan to be used then lock it in. (The key phrase is "stored outlines".) That is a better solution, because even if you get it to use the right plan now, you have no way of knowing when changing statistics will cause Oracle to switch its evaluation of the right query plan to a much worse one. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #216,725 by SpiceWare 7/27/05 8:30:09 PM 7/27/05 8:30:41 PM Reply	I'll see how it works then we don't have a DBA so I'll read up on it and check with my boss. He knows a bit more about Oracle than I do. He's leaving in a couple months so I suspect I'll be the "acting DBA". Thanx! Darrell Spice, Jr. [link\|http://spiceware.org/gallery/ArtisticOverpass\|Artistic Overpass]\n[link\|http://www.spiceware.org/\|SpiceWare] - We don't do Windows, it's too much of a chore Edited by SpiceWare July 27, 2005, 08:30:41 PM EDT Expand All History

Welcome to IWETHEY!