Home » SQL & PL/SQL » SQL & PL/SQL » User vs System Function Execution Time (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
User vs System Function Execution Time [message #351412] Tue, 30 September 2008 14:16 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I have been working on a system which normalizes large sets of data then indexes the normalized values, and does matching with that information. For investigative purposes we preserve the raw data values we have loaded and match those values to our normalized data set using a functional index on the raw data table which implements the normalization code.

Typically this normalization code is just a collection of seeded Oracle SQL functions so the option of simply putting these raw functions into the INDEX does exist the problem with this is code maintainability if the normalization code is built right into the index it must also be built into queries on that index and selections of data on that index, etc. So if a change is made to the normalization method the change must be reproduced in many different places. As an alternative we simply created a set of user functions that wrap up the seeded Oracle, we can then access the functions from each place we need to perform normalization so changes to normalization methods need to be made in one place and they get used everywhere.

The problem with this solution is that efficiency tanks in comparison to using just the Oracle functions. See the following code example:

CREATE TABLE func_test
AS SELECT DBMS_RANDOM.STRING('l', 20) val
FROM DUAL CONNECT BY ROWNUM <= 1000000;

CREATE OR REPLACE FUNCTION user_upper (val IN VARCHAR2)
   RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
   RETURN UPPER (val);
END;

CREATE INDEX sfunc ON func_test(UPPER(val));

--takes 2 to 3 times longer
CREATE INDEX ufunc ON func_test(user_upper(val));


In testing the system function based index is taking 3 seconds to create the user function based index is taking 10 seconds. Now I know nothing is free and so I do expect some type of performance hit the scale of the hit is what is surprising to me.

The only thing I have yet to try (because it is not enabled in our environment) is native compilation but it would seem to me (and I could obviously be wrong) that Oracle would be smart enough to not re-interpret the p_code 1,000,000 times but simply do it once up front and then use that cached code.

Is there anything else that could cause the performance difference? Am I wrong about the PL/SQL interpreter? Would the ability to do this with the native code help dramatically?

Anyone who has the native features enabled who wants to check it out the example above should work adding the following code after the function (untested on my part for obvious reasons)

ALTER FUNCTION user_upper COMPILE PLSQL_CODE_TYPE=NATIVE;


Thanks,
Andrew
Re: User vs System Function Execution Time [message #351464 is a reply to message #351412] Wed, 01 October 2008 00:04 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I am not sure about native functions.

System functions are built in C and User Function is in PL/SQL.

And simply C is faster then PL/SQL.

If you found something more then please share with us also.

Regards,
Rajat
Re: User vs System Function Execution Time [message #351514 is a reply to message #351464] Wed, 01 October 2008 05:23 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
From what I have read, compiling with the NATIVE operative will compile the PL/SQL to a binary library which should in theory eliminate the C vs PL/SQL distinction between the two...there may still be other barriers when it comes to speed though but until I can convince someone to enable NATIVE compilation on our instance or get a test instance set up myself I won't know for sure...
Re: User vs System Function Execution Time [message #351518 is a reply to message #351514] Wed, 01 October 2008 05:42 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I will try this at home and let you know.

I hope NATIVE compilation will make it faster.

Try it by yourself also

Some Info

Regards,
Rajat Ratewal

[Updated on: Wed, 01 October 2008 05:43]

Report message to a moderator

Re: User vs System Function Execution Time [message #351527 is a reply to message #351518] Wed, 01 October 2008 07:00 Go to previous message
annagel
Messages: 220
Registered: April 2006
Senior Member
I am going to try...but unfortunately I am just a developer and ALTER SYSTEM is not something I can do myself...thanks for the help.
Previous Topic: counting function
Next Topic: Spool decimal value to csv file
Goto Forum:
  


Current Time: Thu Dec 08 06:12:49 CST 2016

Total time taken to generate the page: 0.30095 seconds