Recently, a query caught my eye with some strange overuse of resources (LIO in that case). As usual, there were several calls of pretty complex pl/sql user-defined functions. Also an analytic function row_numbe() was used there to eliminate possible row duplicates. The query was similar to the following one:
select id, val
from (select id, val, row_number() over (partition by id order by val) as n
from (select id,
tc1$p1.test1(id) as val
from tc1$t1
)
) where n=1;
As it appeared, for the table tc1$t1, which contains N unique values of id column, the function tc1$p1.test1(id) is called almost 2*N times.
It is not clear what is going on behind the scene. For example, if I use trace event 10032 to get some sorting statistics, I can see no correlation between the number of comparisons ("Total number of comparisons performed" statistic) and the number of function calls while adding some duplicates or changing clustering of the data in tc1$t1 (loading the table with different order by clause).
For those who hits this issue, a very simple workaround exists: just wrap function call with subquery from dual like in the following example:
select id, val
from (select id, val, row_number() over (partition by id order by val) as n
from (select id,
(select tc1$p1.test1(id) from dual) as val
from tc1$t1
)
) where n=1;
select id, val
from (select id, val, row_number() over (partition by id order by val) as n
from (select id,
tc1$p1.test1(id) as val
from tc1$t1
)
) where n=1;
As it appeared, for the table tc1$t1, which contains N unique values of id column, the function tc1$p1.test1(id) is called almost 2*N times.
It is not clear what is going on behind the scene. For example, if I use trace event 10032 to get some sorting statistics, I can see no correlation between the number of comparisons ("Total number of comparisons performed" statistic) and the number of function calls while adding some duplicates or changing clustering of the data in tc1$t1 (loading the table with different order by clause).
For those who hits this issue, a very simple workaround exists: just wrap function call with subquery from dual like in the following example:
select id, val
from (select id, val, row_number() over (partition by id order by val) as n
from (select id,
(select tc1$p1.test1(id) from dual) as val
from tc1$t1
)
) where n=1;
Here is a script to reproduce the described behavior. I've checked it on 11.2.0.4 and 12.1.0.2, the output was the same:
===========================================================
How many times is user-defined PL/SQL function (UDF) called
for table tc1$t1 with 100000 rows in it
===========================================================
#1 Using analytic function
===========================================================
Counter (start): 0
1 select id, val
2 from (select id, val, row_number() over (partition by id order by val) as n
3 from (select id,
4 tc1$p1.test1(id) as val
5 from tc1$t1
6 )
7* ) where n=1
Counter (end), UDF function calls: 200040
===========================================================
#2 Without analytic function
Counter (start): 0
1 SELECT id,
2 tc1$p1.test1(id) AS val
3* FROM tc1$t1
Counter (end), UDF function calls: 100000
===========================================================
#3 WA for analytic function
Counter (start): 0
1 select id, val
2 from (select id, val, row_number() over (partition by id order by val) as n
3 from (select id,
4 (select tc1$p1.test1(id) from dual) as val
5 from tc1$t1
6 )
7* ) where n=1
Counter (end), UDF function calls: 100000
===========================================================
