how can I increase performance of a oracle function call.
we are having 10 million records in source. which i try to call oracle function in data services it is taking around 2 hours to complete the job. oracle function is taking around 7 Millie seconds for each record in SQL developer.
I tried enabling parallel option on function in data store and increased degree of parallelism in data flow as 16 but it is still the same.
please let me know if there is any article pointing to increase performance.
It sounds like the Oracle function isn’t getting pushed down to the database with the rest of the query. That’s where the problem is.
If the function call is not part of the main query then what’s happening is that for each row the Dataflow is connecting to Oracle, executing the function and then disconnecting. The connect/disconnect is very costly.
You may need to recode the Dataflow to use a SQL transform. That will ensure that the Oracle function call is always pushed down.
Is the Oracle function call imported into the Datastore? I think it would have to be for you to use it in the Dataflow.
(BOB member since 2007-09-12)