I am new to Oracle technology (primarily versed in SQL Server) and I am having trouble with the performance of a UDF.

The purpose of the UDF is to take some inputs for an "event", and look up an "expected time for event b to occur". The function returns a single DATE value.

The first thing that occurs is a SELECT ... INTO [variable] FROM DUAL; a variable. Finally a lookup occurs in a reference table (only ~30k rows), and that is stored in the return variable.

It took 8 hours to process on 800k rows. I can select all of the columns without using the function in less than 40 seconds.

My main question is: Is the use of DUAL costing me?

We are on an Oracle 11g RAC.


As Ivan said, your problem is not with dual specifically. However, the context switch required to switch between PL/SQL and SQL is slowing things down. Often, SELECT statements like this can be converted into simple PL/SQL assignments. If that isn't possible, perhaps the logic can be moved into the later look up.

Something else you could consider is doing a join with your reference table outside of the UDF.

Before doing anything though, you should consider profiling the function with DBMS_PROFILER. This will show you what part of the function to focus on for your tuning.

| improve this answer | |

No. Oracle knows that DUAL is "in-memory" table. Your problem is somewhere else.

BTW: you can also use call fname(...); statement.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.