Sometimes, our deployments might require deployment of changes to an existing function, proc, package or table where there are a few dependencies with other packages that might be accessed very frequently by calls from APEX.
Eg. we deploy a change to a function, let's say its called “common_function” (with no changes to its specification/parameters), that is used by “package_a” which, in turn is executed by users via APEX about once every few seconds. There are breaks (breathing space) between executions of “package_a”. “package_a” has no global variables in either the spec or body - nice and simple.
When we deploy the changes to “common_function”, then the body of “package_a” becomes invalid. If “package_a” was only being executed by discrete db sessions that end after execution, then the first db session would perform an auto-recompilation of “package_a” body to make it valid prior to execution.
Or I could simply recompile “package_a” body, and if a db session was currently executing, it would wait for it to end, then perform the recompilation.
However, APEX works with shared db session pools. The db sessions don't really end, just go inactive, then reactivate the old db session for use with a different APEX session. It runs DBMS_SESSION.reset_package to ensure prior package states don't carry over from a different APEX session.
So, between the deployment of “common_function” and any change to recompile “package_a”, one of the APEX sessions might reactivate and try to execute “package_a”. The first one will start package auto-recompilation. A second APEX session might activate before package auto-recompilation completes.
We can end up with a cascade of library cache pin waits that can only be resolved by ending the apex sessions immediately. Thereby turning a “hot deployment” into a “cold deployment” with downtime/disruption to users.
How can I deploy “common_function” and allow clean recompilation (either explicitly or by db auto-recompilation on execution) of dependent packages without disrupting the APEX users (or, indeed ORDS for API calls dependent on all this). I don't mind if end users or API clients might have to wait a few seconds to obtain a lock