Re: External function and performance

In article <a7iv8s$j1t$2_at_news.tpi.pl>, Krzysztof says...
>
>I have Oracle 9.0.1 working on RedHat Linux 7.2.
>I have noticed that the following statement:
>
>SELECT external_function(column) FROM table
>
>is 10 times slower than:
>
>SELECT column FROM table
>
>where:
>- external_function is empty C function (it only returns its parameter)
>- shared library containig function on the same machine; communication via
>IPC
>
>Is it normal? If not, what can I do to improve performance?
>
>Thanks in advance.
>External function and performance
>

[Quoted] [Quoted] I do not observe 10x, I see a hit but not 10x

select object_id
from
 all_objects

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1343      3.54       4.33        192     106914          0       20123
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1345      3.57       4.36        192     106914          0       20123

********************************************************************************

select f
from
 all_objects

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.04       0.02          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1343      9.23      12.02          3     106914          0       20123
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1345      9.27      12.05          3     106916          0       20123

[Quoted] The code path is longer -- much so. You are going from SQL, outside to another process -- and back again.

Lets see your tkprof to see the measurable difference..

My function was simply:

int some_function(OCIExtProcContext *with_context ) {

    return 1;
}

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp