SQLs in Functions : Performance Impact

Taking the same case as I had in my previous demo "SQLs in Functions : Each Execution is Independent", I now demonstrate how calling a Function (which executes other SQL) for each row, from an SQL itself, can prove to be very detrimental to performance.

Recall from the previous demo that the INSERT statement had inserted 50,639 rows into the target table. This means that the Function, itself, was called 50,639 times. Could I have avoided having to execute the Function 50,639 times ? Yes, the INSERT statement could have used a Join instead of the Function.

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

These are the results of using the Function :

SQL> set timing on
SQL> insert into obj_names_and_descr
2  select owner, object_name, object_type, lookup_obj_descr (object_type)
3  from dba_objects
4  order by owner
5  /

50639 rows created.

Elapsed: 00:00:16.23
SQL>

insert into obj_names_and_descr
select owner, object_name, object_type, lookup_obj_descr (object_type)
from dba_objects
order by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.05          0         11          0           0
Execute      1     11.24      11.73       1900       6164       4868       50639
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     11.27      11.79       1900       6175       4868       50639

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64

Rows     Row Source Operation
-------  ---------------------------------------------------
50639  SORT ORDER BY (cr=359889 pr=0 pw=0 time=15694827 us)
50639   VIEW  DBA_OBJECTS (cr=5385 pr=0 pw=0 time=1327397 us)
50639    UNION-ALL  (cr=5385 pr=0 pw=0 time=1124834 us)
50638     FILTER  (cr=5378 pr=0 pw=0 time=669040 us)
51827      HASH JOIN  (cr=631 pr=0 pw=0 time=839952 us)
70       TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=858 us)
51827       TABLE ACCESS FULL OBJ$ (cr=625 pr=0 pw=0 time=312171 us)
1801      TABLE ACCESS BY INDEX ROWID IND$ (cr=4747 pr=0 pw=0 time=116996 us)
2368       INDEX UNIQUE SCAN I_IND1 (cr=2370 pr=0 pw=0 time=54337 us)(object id 39)
1     NESTED LOOPS  (cr=3 pr=0 pw=0 time=101 us)
1      INDEX FULL SCAN I_LINK1 (cr=1 pr=0 pw=0 time=39 us)(object id 107)
1      TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=45 us)
1       INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=17 us)(object id 11)

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

SELECT OBJ_DESCR
FROM
MY_OBJ_TYPES_LOOKUP WHERE OBJ_TYPE = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  50639      1.54       1.42          0          1          0           0
Fetch    50639      2.77       2.60          0     354473          0       50639
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   101279      4.32       4.02          0     354474          0       50639

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64     (recursive depth: 1)
******************************************************************************** 

Notice how the query on MY_OBJ_TYPES_LOOKUP was executed 50,639 times !
Also, the CPU time for the "parent" INSERT statement itself is very high ! Each call to the Function required PLSQL. Within the Function, there was an SQL call. Oracle kept switching between SQL and PLSQL.

The INSERT took 16seconds to run.

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

These are the results of using a Join to perform the lookup :

SQL> set timing on
SQL> insert into obj_names_and_descr
2  select owner, object_name, object_type, obj_descr
3  from dba_objects, my_obj_types_lookup
4  where object_type = obj_type
5  order by owner
6  /

50639 rows created.

Elapsed: 00:00:00.64
SQL>

insert into obj_names_and_descr
select owner, object_name, object_type, obj_descr
from dba_objects, my_obj_types_lookup
where object_type = obj_type
order by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.58       0.59       1900       6192       4873       50639
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.59       0.60       1900       6193       4873       50639

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64

Rows     Row Source Operation
-------  ---------------------------------------------------
50639  SORT ORDER BY (cr=5392 pr=0 pw=0 time=521559 us)
50639   HASH JOIN  (cr=5392 pr=0 pw=0 time=1381766 us)
42    TABLE ACCESS FULL MY_OBJ_TYPES_LOOKUP (cr=7 pr=0 pw=0 time=175 us)
50639    VIEW  DBA_OBJECTS (cr=5385 pr=0 pw=0 time=1021851 us)
50639     UNION-ALL  (cr=5385 pr=0 pw=0 time=819289 us)
50638      FILTER  (cr=5378 pr=0 pw=0 time=363509 us)
51827       HASH JOIN  (cr=631 pr=0 pw=0 time=579119 us)
70        TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=534 us)
51827        TABLE ACCESS FULL OBJ$ (cr=625 pr=0 pw=0 time=156502 us)
1801       TABLE ACCESS BY INDEX ROWID IND$ (cr=4747 pr=0 pw=0 time=63373 us)
2368        INDEX UNIQUE SCAN I_IND1 (cr=2370 pr=0 pw=0 time=26582 us)(object id 39)
 1      NESTED LOOPS  (cr=3 pr=0 pw=0 time=86 us)
 1       INDEX FULL SCAN I_LINK1 (cr=1 pr=0 pw=0 time=31 us)(object id 107)
 1       TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=39 us)
 1        INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=12 us)(object id 11)

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

The Join based INSERT took less than a second to run.
It was executed as a single SQL call. Much faster.

The function makes sense for single-row lookups (e.g. when called from a user-screen). It should NOT be used for a batch update which attempts the lookup more than a few times -- in this case, 50,639 times ! If possible, replace PLSQL calls with straightforward SQL.
(That is Tom Kyte's mantra as well : If it is possible to do it in SQL, do it in SQL !").

.
.
.