Re: CBO costing pl/sql functions

  • From: "Vlad Sadilovskiy" <vlovsky@xxxxxxxxx>
  • To: jeremy.schneider@xxxxxxxxxxxxxx
  • Date: Tue, 12 Jun 2007 19:32:55 -0400

Hello, Jeremy

CBO assigns default cost and data distribution statistics for functions that
do not have associated cost. In at least 9i I saw the actual "assumed"
numbers in the 10053 trace.

You can of course use educated guess as to what cost of the function is, but
sometimes it really doesn't work. So, you'd have to know what the function
does and how much each step costs. Also you would need to estimate the data
distribution statistics.

If you need to implement more advanced costing that depends on your inputs I
suggest to use the data cartridge implementation. Probably you should look
at this then
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dciwhatis.htm#i1004600

Vlad Sadilovskiy
Oracle Database Tools
http://www.fourthelephant.com

On 6/12/07, Jeremy Paul Schneider <jeremy.schneider@xxxxxxxxxxxxxx> wrote:

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: