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

Apologies for omitting the Oracle version -- 9.2.0.5 running on Windows
2000 Server.

well, i don't know if there is a better way to do this, but this is how
i got your code to work without calling the function 4x:

-----

SQL> create or replace type res_util_typ
2 as object (pct_util number(4),
3 morn_avail number(4),
4 aftn_avail number(4),
5 eve_avail number(4))
6 /

Type created.

SQL> create or replace type blah as table of res_util_typ;
2 /

Type created.

SQL> CREATE OR REPLACE PACKAGE ALLAN_PKG AS
2 function get_utilization return res_util_typ;
3 function get_u return blah;
4 end;
5 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY ALLAN_PKG AS
2 function get_u return blah is
3 rtu res_util_typ;
4 ret blah;
5 begin
6 rtu := get_utilization;
7 ret := blah();
8 ret.extend;
9 ret(1) := rtu;
10 return ret;
11 end;
12 function get_utilization return res_util_typ is
13 retVal res_util_typ;
14 pct_util number(4) := 0;
15 tot_used pls_integer := 0;
16 tot_avail_m pls_integer := 0;
17 tot_avail_a pls_integer := 0;
18 tot_avail_e pls_integer := 0;
19 begin
20 select least(count(*), 9999)
21 into pct_util
22 from all_objects;
23 select least(count(*), 9999)
24 into tot_avail_m
25 from all_objects;
26 select least(count(*), 9999)
27 into tot_avail_a
28 from all_objects;
29 select least(count(*), 9999)
30 into tot_avail_e
31 from all_objects;
32 retVal := res_util_typ(pct_util, tot_avail_m, tot_avail_a,
33 tot_avail_e);
34 return retVal;
35 end;
36 END ALLAN_PKG;
37 /

Package body created.

SQL> set autot on
SQL> set timing on
SQL> select pct_util, morn_avail, aftn_avail, eve_avail from
table(cast(allan_pkg.get_u as blah));

PCT_UTIL MORN_AVAIL AFTN_AVAIL EVE_AVAIL
---------- ---------- ---------- ----------
9999 9999 9999 9999

Elapsed: 00:00:08.92

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COLLECTION ITERATOR (PICKLER FETCH)




Statistics
----------------------------------------------------------
16 recursive calls
16 db block gets
229598 consistent gets
0 physical reads
0 redo size
308 bytes sent via SQL*Net to client
311 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

-----

(i did this in 8i, so i had to change your CASE statements a little
bit.)

basically all i did was to create another type ("blah") that is a table
of your type, wrote a wrapper function that created a "blah" with a
single res_util_typ in it. then, from sql i called the wrapper
function with the table(cast( ...)) bit so that it can be accessed as a
table in sql.

shrug. there's probably a better workaround to your problem, but until
then ...

This is a really interesting problem. At first I thought this was to do
with query merging, but after some tests I can only surmise that I
haven't a clue what is going on ;o)

I added a little "execution counter" to the ALLAN_PKG and tried various
iterations of the SQL statement to see the effects. The general rule is
that there are no rules about how many times a function will be
executed in a SQL statement ! As a set-based language, who knows what
might be happening under the covers ? Anyway, my examples ( using
9.2.0.5.0 on Solaris ):-

9i> select allan_pkg.get_utilization
2 from dual
3 /

GET_UTILIZATION(PCT_UTIL, MORN_AVAIL, AFTN_AVAIL, EVE_AVAIL)
------------------------------------------------------------------------------------------------------------------------
RES_UTIL_TYP(8148, 8148, 8148, 8148)

1 row selected.

9i> exec allan_pkg.report;
Function executed 1 time(s).

PL/SQL procedure successfully completed.



9i>
9i> select ru.u.pct_util
2 , ru.u.morn_avail
3 , ru.u.aftn_avail
4 , ru.u.eve_avail
5 from (
6 select allan_pkg.get_utilization u
7 from dual
8 ) ru
9 /

U.PCT_UTIL U.MORN_AVAIL U.AFTN_AVAIL U.EVE_AVAIL
---------- ------------ ------------ -----------
8148 8148 8148 8148

1 row selected.

9i> exec allan_pkg.report
Function executed 4 time(s).

PL/SQL procedure successfully completed.

query merging by the CBO, such that it would end up running something
along the following lines :-

SELECT allan_pkg.get_utilization.pct_util
, allan_pkg.get_utilization.morn_avail
, allan_pkg.get_utilization.aftn_avail
, allan_pkg.get_utilization.eve_avail
FROM dual;

So to test this, I made sure the query could not be MERGED by adding
the /*+ NO_MERGE */ hint into the in-line view. If my above hypothesis
were correct, then we'd (hopefully) be back down to 1 function call.

9i>
9i> select ru.u.pct_util
2 , ru.u.morn_avail
3 , ru.u.aftn_avail
4 , ru.u.eve_avail
5 from (
6 select /*+ no_merge */ allan_pkg.get_utilization u
7 from dual
8 ) ru
9 /

U.PCT_UTIL U.MORN_AVAIL U.AFTN_AVAIL U.EVE_AVAIL
---------- ------------ ------------ -----------
8148 8148 8148 8148

1 row selected.

9i> exec allan_pkg.report
Function executed 3 time(s).

PL/SQL procedure successfully completed.

function calls. To test whether the 4 function calls for 4 type
attributes was circumstantial or not, I added 4 more elements to the
object type ( making 8 ) and ran the merged and unmerged queries again.
The merged query gave 8 executions and the merged query gave 3.

So, I continued with the original setup by trying some variations on a
theme...

9i>
9i> with subq as (
2 select allan_pkg.get_utilization u
3 from dual
4 )
5 select ru.u.pct_util
6 , ru.u.morn_avail
7 , ru.u.aftn_avail
8 , ru.u.eve_avail
9 from subq ru
10 /

U.PCT_UTIL U.MORN_AVAIL U.AFTN_AVAIL U.EVE_AVAIL
---------- ------------ ------------ -----------
8148 8148 8148 8148

1 row selected.

9i> exec allan_pkg.report
Function executed 4 time(s).

PL/SQL procedure successfully completed.

me a nasty ORA-0600 so I used an alternative ) ?

9i>
9i> with subq as (
2 select /*+ no_merge */
3 allan_pkg.get_utilization u
4 from dual
5 )
6 select ru.u.pct_util
7 , ru.u.morn_avail
8 , ru.u.aftn_avail
9 , ru.u.eve_avail
10 from subq ru
11 /

U.PCT_UTIL U.MORN_AVAIL U.AFTN_AVAIL U.EVE_AVAIL
---------- ------------ ------------ -----------
8148 8148 8148 8148

1 row selected.

9i> exec allan_pkg.report
Function executed 3 time(s).

post.

So that's as far as I managed to get. Where's Jonathan Lewis when you
need him ? !!!

Regards
Adrian

Sigh. Why does the new Google Groups insist on formatting my replies
for me? Trust me that the above was nicely formatted in fixed-width
font, and now, just like Metalink, it strips it all away. Anyone know
how I can stop this ? Or do I have to start putting tags into my
reponses to make the code snippets legible ? Luckily, the code snippets
above are short and simple.

Thanks -- I was going to try adding some counters etc. to prove that
the function was being invoked multiple times, but I have a deadline so
I had move on to an alternate approach. What I am doing now is
returning a table type that contains slightly less aggregated data than
my original function. Although I then have to subsequently do some
simple summarizing of that information, it actually makes the function
more general-purpose and the performance is much better. I have gone
from over 3 minutes to get a set of data to under 2 seconds.

What prompted my first technique was Tom Kyte's "Effective Oracle"
book, specifically the technique to avoid an outer join by using a
scalar subquery that returns an object type (p 508 - 509). Ultimately
I still need this in a scalar subquery, but I now concatenate the
values into a single string value and substr() them out in the parent
query (this technique is also described on p. 508) instead of using the
object type technique.

I appreciate that you took the time to investigate this further...
Allan

In article <1103293935.414620.290650 (AT) z14g2000cwz (DOT) googlegroups.com>, A Billington
says...
google for the first time "broke" something.

groups wasn't broken.
they broke it bad.

so bad, I'm paying someone else to provide me access to the newsgroups. I found
it impossible to use their new interface. I never knew what would get posted,
what it would look like and finding stuff became impossible. No threaded views.
ugh.

newsguy.com -- simple interface.


--
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

You can still get the older views/behaviors (some of them, at least) by
using alternate google sites.
For example, following the Canadian path (from www.google.ca), you can
use the following base for comp.databases.oracle.*
"http://groups.google.ca/groups?q=comp.databases.oracle&hl=en&btnG=Google+S earch"
I don't know for how long, but in the mean time ...

Forget my previous post - it looks like you can only view the posts,
and not reply to them.

Sorry.