performance question with function returning object type

From: <streib_at_cs.indiana.edu>
Date: 16 Dec 2004 08:03:30 -0800
Message-ID: <1103213010.118780.219620@z14g2000cwz.googlegroups.com>

I have a function which returns an object type which is just a wrapper for four native sql types. When I call the function it takes x time, but if I query the individual values it takes x*4 time like it is actually calling the function four times. Can anyone explain? In case that does not make sense, here is an example. This is not my actual function, but it is a simplified illustration of what is happening:

create or replace type res_util_typ
as object (pct_util number(4),

morn_avail number(4),
aftn_avail number(4),
eve_avail number(4))

/

CREATE OR REPLACE PACKAGE ALLAN_PKG AS
function get_utilization return res_util_typ; END ALLAN_PKG;
/

CREATE OR REPLACE PACKAGE BODY ALLAN_PKG AS function get_utilization return res_util_typ is

retVal          res_util_typ;
pct_util        number(4) := 0;
tot_used        pls_integer := 0;
tot_avail_m     pls_integer := 0;
tot_avail_a     pls_integer := 0;
tot_avail_e     pls_integer := 0;

begin
select case when count(*) > 9999 then 9999 else count(*)
end into pct_util
from all_objects;

select case when count(*) > 9999 then 9999 else count(*)
end into tot_avail_m
from all_objects;

select case when count(*) > 9999 then 9999 else count(*)
end into tot_avail_a
from all_objects;

select case when count(*) > 9999 then 9999 else count(*)
end into tot_avail_e
from all_objects;

retVal := res_util_typ(pct_util, tot_avail_m, tot_avail_a, tot_avail_e);

return retVal;
end;
END ALLAN_PKG;
/

Now, if I just call the function we see the statistics:

set autotrace on
set timing on

select allan_pkg.get_utilization from dual
/

GET_UTILIZATION(PCT_UTIL, MORN_AVAIL, AFTN_AVAIL, EVE_AVAIL)

---

RES_UTIL_TYP(9999, 9999, 9999, 9999) Elapsed: 00:00:13.02

Execution Plan

---

0	  SELECT STATEMENT Optimizer=CHOOSE
1	0   TABLE ACCESS (FULL) OF 'DUAL'

Statistics

---

4 recursive calls
0 db block gets
287516 consistent gets
0 physical reads
0 redo size
936 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

But if I call it so I can get the individual values from the result, the elapsed time and number of recursive calls and consistent gets are approximately multiplied by 4:

select ru.u.pct_util, ru.u.morn_avail, ru.u.aftn_avail, ru.u.eve_avail from (select allan_pkg.get_utilization u from dual) ru
/

U.PCT_UTIL U.MORN_AVAIL U.AFTN_AVAIL U.EVE_AVAIL ---------- ------------ ------------ ----------- 9999 9999 9999 9999

Elapsed: 00:00:51.01

Execution Plan

---

0	  SELECT STATEMENT Optimizer=CHOOSE
1	0   TABLE ACCESS (FULL) OF 'DUAL'

Statistics

---

16 recursive calls
0 db block gets
1150019 consistent gets
0 physical reads
0 redo size
573 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

I don't understand this -- in the first version of the call, we can see all the values are there; why does it take four times longer to query them as individual columns?
Any way to avoid this would be appreciated!

Allan Received on Thu Dec 16 2004 - 10:03:30 CST