Re: CBO costing pl/sql functions

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_at_ardentperf.com> 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
>

--
http://www.freelists.org/webpage/oracle-l