CBO costing pl/sql functions

  • From: "Jeremy Paul Schneider" <jeremy.schneider@xxxxxxxxxxxxxx>
  • To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 12 Jun 2007 10:12:55 -0700

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

Other related posts: