"select col1, col2, fnc1(col3, col4) colnew"
...
"there is no way for the optimizer to determine the impact the function will have on the query. "
For a function in the SELECT list, the optimizer wouldn't be interested. Since it must be executed the same number of times regardless of the plan, it doesn't affect the choice of plan.

If it is used as a predicate, there is
ASSOCIATE STATISTICS WITH FUNCTIONS myFunction DEFAULT COST ...
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_4006.htm#sthref4268
and (more complicated) ODCIStatsFunctionCost
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dciextopt.htm#sthref413

Hi Tom,
Would you please elaborate on the Associate Statistics statement? Where/how would you use it? And the benefits?
Thanks

I agree with trying to as much as possible in SQL. But plsql is heavily used to encapsulate business logic and creating APIs. Then other programs need to interact via that API, so rewriting as a join is not allowed. Also, the idea of encapsulating what a function is doing would be lost if the join had to be duplicated in several places.
Some of the main Pros of implementing as a function are to make that business process consistent across every program that uses it and allowing the function to be refactored without changes to the calling programs/SQL.

I was not familiar with scalar subqueries. It sounds promising, but should they not be required to be deterministic? Within a single SQL statement I suppose we would have read consistency with regard to other sessions DML, but it might be possible to get incorrect results due to result caching if that function does processing that is not only based on table data (plsql package variables, etc). A reviewer on this link describes that situation and you responded.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40299712621507

Andrew

Regarding the read consistency, I made the same mistake as the reviewers in the link you provided. I wrongly assumed that the main SQL would have made them all read consistent. Now that I think about it, that was a silly assumption since a trace would show independent (while recursive) SQL. It is a bit disturbing that the read inconsistency can happen with function calls within SQL. But it is good to know about in the event the "wrong results" appear for a query. That would be difficult to track down.

"bollocks"
:)
As I said before, I would try to get as much as possible in SQL, but when there are tuning issues with SQL that calls PLSQL functions, my answer can't always be "rewrite that function into a view". For years the mantra (not necessarily from you) has been "put everything in packages". Well, we have. And now developers want to build on that and use those functions within SQL. But then that brings me back to the original point of the question, that the CBO can not integrate what the PLSQL function calls are doing into the execution plan. The CBO can only look at the tables explicitly identified in the original SQL and then treat the function calls through what is effectively nested loop processing.

Perhaps I am over-analyzing the situation, but it is something I have been noticing more and more, and I think I will continue to see additional cases of it as we build upon our existing systems. Especially with the database-centric (thick database) approach that is becoming more common with the migration to web enabled applications (which formerly were just Forms apps).

I will look more into the scalar subquery caching. From what I read it is even better in 10gR2.

Thanks.
Andrew

You said above:
"1) if you can do it in all SQL - do it, never write code unless you are forced to.
2) if you absolutely cannot do it in sql, write as little plsql as you can (and understand how it works - scalar subquery caching for example can be of great benefit in your case sometimes)
3) if you cannot do it in plsql (because it requires doing something plsql is not capable of doing - which is very very little today actually) - write as little java as you can"

And you have said elsewhere: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:363719300346898052 )
"Use a transactional API in the form of stored procedures - do not allow applications to directly modify the tables - have them call these well formed, correct procedures"
and
"You *need* to centralize your transactional processing - using stored procedures - to *get the right stuff*. "

Is the difference between "avoid PL/SQL" in this thread and "use PL/SQL" in the other because this thread is focused on a READ and the other on a WRITE?

begin 
   for x in ( select * from T1 ) loop
       insert into t2 values X;
   end loop;
end;

you would:

begin
   insert into t2 select * from t1;
end;

if using DML error logging
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:47458244605081#tom53104016181239