| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: performance question with function returning object type
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)
9i> exec allan_pkg.report;
Function executed 1 time(s).
PL/SQL procedure successfully completed.
>> OK. So we see above our starting position. 1 execution returning the
object. Consistent with your OP.
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
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.
>> Just to prove to myself that there are 4 executions on my database
as you demonstrated in your OP. So I thought this must be to do with
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_availFROM 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
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.
>> NO! Now down to 3 executions. So that means that the first query DID
merge. I conclude this because by explicitly not merging, we got fewer
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
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.
>> Nothing unexpected there. What about if we force a temp table
transformation by forcing a no merge ( using the MATERIALIZE hint gave
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
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).
>> Back to 3 executions, which I think is as low as you'll get this
without using a method such as the nested table method in the previous
post.
So that's as far as I managed to get. Where's Jonathan Lewis when you need him ? !!!
Regards
Adrian
Received on Fri Dec 17 2004 - 08:21:47 CST
 |
 |