Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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