Hello everybody,
Hopefully I did not miss any topic which would deal with my issue, even if I did not find any.
In production, we quite often have to pin execution plans on the Oracle side to avoid unefficient plans to be executed. This Oracle mechanism is based on the hash code of the text of the SQL statement. When Oracle receives an SQL statement for the first time, it parses the statement to make sure it is correct and to understand what should be done. Then Oracle chooses the most cost efficient way to compute the result, this is the execution plan. All these analysis elements are then cached with the hash code computed with the text of the sql statement as a key for the cache. Sometimes, we also have to force a specific execution plan for a given SQL statement. This mechanism is based on the hash code of the text of the SQL statement : pining modifies this cache and deactivate automatic and dynamic optimisation of the execution plans so that always the configured plan is used.
This means if the sql text varies even slightly Oracle will have to repeat all these analysis steps and will miss the execution plan pining because hash codes are no more matching.
Unfortunately, we observe in production each time Wildfly (and then Hibernate) is restarted that the sql text which corresponds to a JPQL statement is never translated into the exact same SQL statement string. This bypasses the SQL pining and the application is running into performance issues. Typicaly, aliases for columns and tables have an integer index in their name and the value changes after the restart which changes the SQL text even though the semantic of the statement is exactly the same.
So here is my question : is there a way to force Hibernate to always generate the exact same SQL statement text for a given JPQL (or HQL) query so that all the Oracle optimizations are preserved after restarts ?
Thank you very much.