Optimizing UDF – Is Using DUAL Expensive

functionsoracletuning

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.

Best Answer

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.

Related Solutions

Using the special Dual table to read sequences

Note that 11g has support for sequence expressions directly in PL/SQL

Otherwise, from the Oracle Docs:

Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan.

So any performance gain will be slight. In the following tests I dropped and recreated the table and sequence between each execution:

testbed:

create sequence s;
create table t(id integer);

tests:

declare
  n integer;
begin
  for i in 1..100000 loop
    select s.nextval into n from dual;
    insert into t(id) values(n);
  end loop;
end;
/
--average about 5s

declare
  n integer;
begin
  for i in 1..100000 loop
    insert into t(id) values(s.nextval) returning id into n;
  end loop;
end;
/
--average about 4s

begin
  for i in 1..100000 loop
    insert into t(id) values(s.nextval);
  end loop;
end;
/
--average about 3s

You should probably rerun these tests or ones like them in you own environment.

Thesql need help optimizing query

I agree with @ypercubeᵀᴹ, the comma join in the exists clause can be removed:

select hostname, criticality, source, message, record_date
from eventlog.logs l1
where not exists ( 
    SELECT 1
    FROM eventlog.rules r
    where l1.message like r.content
      and r.type = 'DROP'
) 
and criticality in ('High', 'Medium')
and record_date > sysdate() - Interval 2 Day
order by l1.message;

I also replaced the selected columns in the subquery with 1 since it really does not matter what you select, and replaced the OR predicate with IN.

Best Answer

Related Solutions

Using the special Dual table to read sequences

Thesql need help optimizing query

Related Question