It has been a long time since my last post and I’m sorry for that. I’ve been very busy lately and a lot of things happened in my life. Most importantly; I’ve finally decided to move to the next level and I’ve founded my own company – Terama Consulting. You can check our web site here http://www.terama.consulting. We focus mainly on IT consulting, employee training and small cloud services. Feel free to contact us :).
Now, last time we asked ourselves an interesting question. How does Oracle order predicates in where clause and how to tell him how expensive our function really is. Reason for this is that Oracle has none to very limited way to say how CPU or I/O intensive your function is.
We’ll break our post into two parts. In first part, we will learn how to give Oracle information he needs for best possible decision. And second part, where we actually learn how to measure resource consumption, so we know what to tell to Oracle.
So, how to give Oracle information about cost and other attributes of our function?
Answer is quite simple: ASSOCIATE STATISTICS
Purpose of this statement is to associate statistic of given type to our function. By using it, we can tell Oracle what is the CPU cost or our function, what is its selectivity, I/O cost, etc.
Let’s have a look on our options here. We can associate statistics with following object types, while only one set of statistics may be attached to that object:
• Columns• Functions
• Packages
• Types
• Indexes
• Indextypes
There are certain limitations to what you can set with what object type but I don’t think we need to go into so much detail here. You can study Oracle documentation for that (https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4006.htm).
Now, let’s see what statistics you can associate with your objects:
• CPU cost (number of units greater than 0)
• I/O cost (number of units greater than 0)
• Network cost (not implemented as of 12c)
• Selectivity (number between 0 and 100 where 100 means no filtering at all)
Here are some usage examples:
Selectivity 5%:
ASSOCIATE STATISTICS WITH FUNCTIONS my_where_func DEFAULT SELECTIVITY 5;
CPU cost 950, I/O cost 11 and network cost of 0 (which you can happily ignore):
ASSOCIATE STATISTICS WITH FUNCTIONS my_where_func DEFAULT COST (950,11,0);
All together on one function:
ASSOCIATE STATISTICS WITH FUNCTIONS my_where_func DEFAULT SELECTIVITY 5 DEFAULT COST (950,11,0);
Now it would be a nice time for some execution plans where we play with statistics, but I think you’ve already got the trick. So no execution plans for today and see you in next part :)
