Thanks for the question, Jon.
Asked: July 03, 2024 - 12:28 pm UTC
Last updated: July 30, 2024 - 3:06 am UTC
Version: 23
Viewed 1000+ times
You Asked
Thank you for taking my question.
We're getting ready for 23ai and see that there is a new RESET_STATE option we can use if we're using PL/SQL behind a web server connected to Oracle using a connection pool and serving up stateless services. (
https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/high-availability.html#GUID-F7E968E4-EE8F-4563-91F3-CD44B5D2E747 ) Up until now on 12.1.0.2 we've been running the following each time we return the connection to the pool to make sure we're not leaking data between calls:
begin dbms_session.modify_package_state ( dbms_session.reinitialize ); end;
It's always our top CPU consumer on the database. With that in mind...
1. Is RESET_STATE less demanding CPU wise than dbms_session.modify_package_state ( dbms_session.reinitialize )?
2. Was dbms_session.modify_package_state ( dbms_session.reinitialize ) the right way to go to prevent session state leaks on 12.1 or was there a better way?
and Connor said...
I asked around internally - they are slightly different.
"dbms_session.modify_package_state ( dbms_session.reinitialize );" is re-initialises the package, ie, like a "first" instantation, and thus any package memory that was in use *remains* in use (its just the state has been cleared).
RESET_STATE is "throwing away" the session state altogether (so package memory etc is included)
*Conceptually* you could think of "dbms_session.modify_package_state ( dbms_session.reinitialize );" as doing a reset_state followed by some initialisation, so it may be worth testing to see if you get an efficiency gains out of that.
However, if this is a high CPU contributor, then I'd be taking a look at how your app is using connection pooling because that's an unusual occurrence. Tens of thousands of APEX apps around the world use this call, and we rarely see anything in terms of high CPU usage.
Is this answer out of date? If it is, please let us know via a Comment