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

.
.
.

SQLs in Functions : Each Execution is Independent

Functions (Stored PLSQL code that return a single value at each call) are good for calculations, validation against business rules etc. But when you use them for lookups, you must watch out for underlying data being changed !

Thus, if you have a statement like "SELECT col_1, my_function(col_2) FROM ...", the function "my_function" is executed from every row that is returned from the query. Oracle guarantees Read Consistency at the Statement Level. However, insofar as the Function is executed, each execution of the Function is a separate SQL statement executed against the database. If underlying data has been changed in transaction from a different database session, the Function may start returning different values mid-way in a result set.


Here's a demonstration of how a Function being used for a lookup returns different values for the same lookup, within a single SQL statement.

SQL> drop table my_obj_types_lookup purge;

Table dropped.

SQL> drop table obj_names_and_descr purge;

Table dropped.

SQL>
SQL> -- create the Lookups table
SQL> create table my_obj_types_lookup
2  (obj_type varchar2(30) not null, obj_descr varchar2(50) not null)
3  /

Table created.

SQL> insert into my_obj_types_lookup select distinct object_type, 'Is an ' || object_type  from dba_objects;

42 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> -- REM Create a function that does Lookups
SQL> create or replace function lookup_obj_descr (obj_type_in  varchar2)
2  return varchar2
3  as
4  obj_type_ret  varchar2(50);
5  begin
6    select obj_descr into obj_type_ret from my_obj_types_lookup  where obj_type = obj_type_in;
7    return obj_type_ret;
8  exception when others then return 'Unknown object type';
9  end;
10  /

Function created.

SQL>
SQL> -- REM create the target table
SQL> create table obj_names_and_descr
2  (obj_owner varchar2(30) not null, obj_name varchar2(128) not null, obj_type varchar2(30) not null, obj_descr varchar2(60) not null);

Table created.

SQL>
SQL> pause PRESS ENTER TO PROCEED
PRESS ENTER TO PROCEED

SQL> -- Now we populate OBJ_NAMES_AND_DESCR using our Lookup Function !
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.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- REM     Let's verify the count of different types of objects
SQL> select obj_type, obj_descr, count(*)
2  from obj_names_and_descr
3  where obj_type like 'TAB%'
4  group by obj_type, obj_descr
5  order by 1,2
6  /

OBJ_TYPE                       OBJ_DESCR                                                      COUNT(*)
------------------------------ ------------------------------------------------------------ ----------
TABLE                          Is an TABLE                                                         897
TABLE                          OOPS !                                                              759
TABLE PARTITION                Is an TABLE PARTITION                                               128

SQL>
SQL>
SQL> -- REM REM REM #!#!
SQL> -- REM Hey ! Where did the "OOPS !" rows come from ?  My INSERT INTO ... SELECT should have given my Read Consistency
SQL> -- REM     Therefore, every TABLE should have the same OBJ_DESCR !
SQL> 

Surely, the "insert into obj_names_and_descr" should have "seen" a Read Consistent image of data ? Yes. The image from the dba_objects view would have been consistent. However, the lookup function "lookup_obj_descr" was executed once against "my_obj_types_lookup" for *each* row from the query against dba_objects. Each execution of the function was independent of the other executions. It so happens that the underlying table "my_obj_types_lookup" was updated by someone else running :

SQL> update my_obj_types_lookup set obj_descr = 'OOPS !' where obj_type  = 'TABLE';

1 row updated.

SQL> commit;

Commit complete.

SQL> 

mid-way through the INSERT ... SELECT operation.

Therefore, although some rows that were returned from dba_objects saw the obj_descr as "Is an TABLE" for obj_type = 'TABLE' , rows that were still being fetched from dba_objects after the other session committed his update to 'OOPS !', now saw the new value 'OOPS !' as being the description.

Therefore, the SELECT portion of the INSERT actually returned different values for obj_descr when using the Function !

.
.
.

RMAN can identify and catalog / use ArchiveLogs automagically

When doing RESTORE DATABASE and RECOVER DATABASE using RMAN, you may find that RMAN automagically

a) CATALOGs files (BackupPieces and even ArchiveLogs) in the FRA, if you are using an FRA
b) Reads ArchiveLogs from the destination identified by log_archive_dest_1 if you use this instead of an FRA

Knowledge of this can also be used when you are restoring to another server where you haven't created an FRA. Copy/Restore your ArchiveLogs (using non-RMAN methods) to any alternate log_archive_dest directory, designate it as log_archive_dest_1 in the parameter file of the database you are creating (i.e. pretending to restore and recover on the new server) and let RMAN identify the files.

I had just posted an example of this in this forums thread.

.
.
.