Performance: call packages from APEX is slow
81772Aug 4 2009 — edited Sep 4 2010Hi
we have a complexe authorization, authentication and policy concept. We have encapsulate a lot of functions into a seperate package. Functions and procedures of this package gets called at different places in three different applications. I know if a package function ie. get's called in a report query it could be very slow because it get's called not one time for one row, it sometimes get's called 5 or 10 times for one row. It's always better to join tables and information into reports.
Problem is, we have two instances and in one instance, the application is "running". In the other instance, the application is f*****g slow...
So I read about pinning of packages and problems with session pooling and session cached cursors.
First question is, what excatly happens if I pin a package into memory and what are the prerequisites to do that? Could this realy improve the performance dramatically?
Second question is, could there be some problems of session pooling or can we increase the amount of session that could be pooled so that packages get be chached?
Do you have some more information, links or ideas to increase performance? or caching of packages? or some other tricks like overwriting V(xxx) syntax?
Thanks in advance!