Performance of Lookup Function

Recently one of my developer friends experienced a performance problem. They run a PL/SQL lookup function over a table with one million rows. Every time the query failed since it reached the CPU usage limit specified by CPU_PER_CALL.

My suggestion is, DO NOT use lookup function if you can do it in SQL statement. SQL statements always have better performance then PL/SQL. Here is my example.

Create two tables,

create table scott.t as select * from dba_tables;

create table scott.u as select * from dba_users;

Now we create a lookup function, 

create function scott.get_uid(l_name in varchar2) return number

as

  ret number;

begin

  select user_id into ret from scott.u where username=l_name;

  return ret;

end;

/

alter system flush shared_pool;
alter system flush buffer_cache;

Now we run two queries,

set timing on

alter session set sql_trace=true; 

select user_id from scott.t, scott.u where t.owner=u.username;

Elapsed: 00:00:01.74

select scott.get_uid(owner) from scott.t;

Elapsed: 00:00:06.86

alter session set sql_trace=false;

Two queries return the same results. The join statement takes 1.74 seconds but the lookup function consumes 6.86 seconds. Why? Look into the trace file and we will find the lookup function is executed 2475 times!!!

********************************************************************************

SQL ID : bta262awhyx23

select scott.get_uid(owner)

from

 scott.t

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      166      0.96       2.80          0        242          0        2475

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      168      0.96       2.81          0        242          0        2475

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows     Row Source Operation

-------  ---------------------------------------------------

   2475  TABLE ACCESS FULL T (cr=242 pr=0 pw=0 time=68 us cost=25 size=41854 card=2462)

********************************************************************************

SQL ID : 574gd4b0c2rxr

SELECT USER_ID

FROM

 SCOTT.U WHERE USERNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute   2474      0.14       0.61          0          0          0           0

Fetch     2474      0.28       1.96          0       7422          0        2474

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total     4949      0.42       2.57          0       7422          0        2474

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS FULL U (cr=3 pr=0 pw=0 time=0 us cost=3 size=17 card=1)

********************************************************************************