Oracle ADF BC by default is trying to reuse prepared SQL query from statement cache. It works this way when ADF BC runs with DB pooling off (jbo.doconnectionpooling=false). Normally we tune ADF application to run with DB pooling on (jbo.doconnectionpooling=true), this allows to release unused DB connection back to the pool when a request is completed (and in this case, statement cache will not be used anyway). If View Object is re-executed multiple times during the same request - in this situation, it will use statement cache too.
However, there are cases when for specific View Object you would want to turn off statement cache usage. There could be multiple reasons for this - for example, you are getting Closed Statement error after it tries to execute SQL for statement obtained from statement cache. Normally you would be fine using statement cache, but as I said - there are those special cases.
We are lucky because there is a way to override statement cache usage behavior. This can be done in View Object implementation class either for particular View Object or in the generic class.
After View Object was executed, check the log. If this is not the first execution, you will see log message - "reusing defined prepared statement". This means SQL will be reused from statement cache:
To control this behavior, override getPreparedStatement method:
We create new prepared statement in this method, instead of reusing one from the cache.
As a result - each time View Object is executed, there is no statement cache usage:
Download sample application from GitHub repo.
However, there are cases when for specific View Object you would want to turn off statement cache usage. There could be multiple reasons for this - for example, you are getting Closed Statement error after it tries to execute SQL for statement obtained from statement cache. Normally you would be fine using statement cache, but as I said - there are those special cases.
We are lucky because there is a way to override statement cache usage behavior. This can be done in View Object implementation class either for particular View Object or in the generic class.
After View Object was executed, check the log. If this is not the first execution, you will see log message - "reusing defined prepared statement". This means SQL will be reused from statement cache:
To control this behavior, override getPreparedStatement method:
We create new prepared statement in this method, instead of reusing one from the cache.
As a result - each time View Object is executed, there is no statement cache usage:
Download sample application from GitHub repo.
No comments:
Post a Comment