Java Springboot Hibernate setting an Oracle Database session parameter

Question

Our Database Administrator has recommended for a specific microservice that I alter its sessions with.

alter session set optimizer_dynamic_sampling=2;

The microservice serves a very particular use case which is searching large quantities of orders (the data is divided among 3 tables) so it can return paginated results back to the user, in SQL developer session after making this session change the query times were cut in half.

Our microservice is Java Springboot based, and we are using JPA (Hibernate implementation) and this is an Oracle Database being connected to. The query being run is a native query. I only need the sessions altered once.. I do not want to run an alter statement every time I go to run the query for obvious performance reasons. That being said, how do I ensure every connection JPA creates in my pool has its corresponding session updated with this property? It seems the entity manager factory or entity manager can provide this.. just have not been able to find detailed documentation on it.

One further note.. per current implementation (the service has been around for 2 years now), an entity manager is created and closed every time the native query is run. Have not been able to determine yet if this complicates or even causes drain to performance as well.

All tips/thoughts welcome. Thanks!

Answer 1

After some playing around I found that this alter session statement could actually be added in-line as a SQL hint.

SELECT /*+ dynamic_sampling(2) */ <your columns and rest of query>

So I'll be adding this hint into the native query currently being run. There is not a lot of documentation on the syntax for this hint, so was a bit of trial and error.