CBO costing pl/sql functions

shoot, left my lewis cbo book at home this week... definitely should have brought it with me. well maybe someone here can help me out.

just had two quick questions about costing of pl/sql functions. i'm working on a system right now where the root of some performance problems seems to be that the CBO is rather dramatically under-estimating the cost of a few pl/sql functions. (Most notably some that execute dynamic sql against a table whose name is passed in to the function as a parameter...)

1. first off, does anyone remember, does the CBO assign a default cost to pl/sql functions that don't have associated statistics or does it ignore them altogether? i suspect the latter, particularly from the 10053 which simply says "No [statistics type|default cost] defined for function" - but haven't done the math yet. :)
2. second, any tips for costing a pl/sql function? i was just going to start with a ceiling for the sql it generates (picking the biggest table it accesses) and fudge upwards a bit for more cpu... basically taking a wild guess here. maybe someone has a better idea.

-Jeremy

--

Jeremy Schneider
Chicago, IL
http://www.ardentperf.com/category/technical

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 12 2007 - 12:12:55 CDT