27 September, 2009

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 !").

.
.
.

5 comments:

Mufalani - OCM said...

Hello Hemant,

Great article and welcome to the Oracle ACE Member team.

Best Regards,

Rodrigo Mufalani
mufalani at gmail dot com

rahul said...

Hi,

Nice and interesting article. Thanks a lot.

Thanks and regards,
Rahul Jain
http://www.dbametrix.com

Anonymous said...

Hi,

Nice and interesting article. Thanks a lot.

Thanks and regards,
Rahul Jain
http://www.dbametrix.com

Ankush Juneja said...

Hi

Is there any performance overhead for
definining big datatypes
For instance:
varchar2(4000)
rather than varchar2(40);

Hemant K Chitale said...

Ankush,
How about "more memory being allocated by PLSQL" ?

Hemant