# Disable query plan execution of HQL in hibernate 5.2.11final at the time of application loading

**URL:** <https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213>\
**Category:** Hibernate ORM\
**Created:** [February 9, 2018, 8:46am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213 "2018-02-09T08:46:35Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![ashwani\_agarwal](https://avatars.discourse-cdn.com/v4/letter/a/da6949/32.png) [@ashwani\_agarwal](https://discourse.hibernate.org/u/ashwani_agarwal)\
**Post date:** [February 9, 2018, 8:46am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/1 "2018-02-09T08:46:35Z")

</div>

Disable query plan execution of HQL in hibernate 5.2.11final at the time of application loading.

org.hibernate.loader.plan.exec.query.internal.SelectStatementBuilder.java  
is responsible for increase in memory

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 9, 2018, 10:03am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/2 "2018-02-09T10:03:13Z")

</div>

You need to provide more context to your problem.

Is the memory issue due to the fact that you have many `NamedQuery` defined which Hibernate needs to parse and store so that you can alter use without reparsing them?

---

<div class="post-metadata">

**Author:** ![ashwani\_agarwal](https://avatars.discourse-cdn.com/v4/letter/a/da6949/32.png) [@ashwani\_agarwal](https://discourse.hibernate.org/u/ashwani_agarwal)\
**Post date:** [February 9, 2018, 8:20pm UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/3 "2018-02-09T20:20:19Z")

</div>

Yes exactly this is what I want

---

<div class="post-metadata">

**Author:** ![ashwani\_agarwal](https://avatars.discourse-cdn.com/v4/letter/a/da6949/32.png) [@ashwani\_agarwal](https://discourse.hibernate.org/u/ashwani_agarwal)\
**Post date:** [February 20, 2018, 11:42am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/4 "2018-02-20T11:42:55Z")

</div>

yes I have many **NamedQuery** in my application and the memory consumption drastically increased after migrating to Hibernate **5.2.11FINAL** from **4.1.9.Final**

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 20, 2018, 11:55am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/5 "2018-02-20T11:55:56Z")

</div>

Upgrading to a newer version can solve many problems. Hibernate 4.1 is rather old, and many things have been improved since 4.x.

---

<div class="post-metadata">

**Author:** ![ashwani\_agarwal](https://avatars.discourse-cdn.com/v4/letter/a/da6949/32.png) [@ashwani\_agarwal](https://discourse.hibernate.org/u/ashwani_agarwal)\
**Post date:** [February 20, 2018, 5:29pm UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/6 "2018-02-20T17:29:14Z")

</div>

I have upgraded to 5.2.11Final and the memory consumption issue is occurring in 5.2.11

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 20, 2018, 5:45pm UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/7 "2018-02-20T17:45:35Z")

</div>

Try to profile it and see why it takes so much memory.

And when you say a lot of memory, how much is it taking? And how many named queries do you have?

---

<div class="post-metadata">

**Author:** ![ashwani\_agarwal](https://avatars.discourse-cdn.com/v4/letter/a/da6949/32.png) [@ashwani\_agarwal](https://discourse.hibernate.org/u/ashwani_agarwal)\
**Post date:** [February 22, 2018, 4:20am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/8 "2018-02-22T04:20:33Z")

</div>

hi vlad,  
I did the profiling and observed that **org.loader.plan.build.internal** \* package is consuming more memory and i compared it with older version (4.1.9) in which the above mention package is not present.

And number of named queries i have is 660 and number of entities are 450.

I am running batches using spring batch previously it required 512MB heap but now after migrating to hibernate 5.2.11 it requies 1024MB heap memory  
**please find the profiling screenshot for hibernate 5.2.11**. ![Heap_New_Version](https://cdck-file-uploads-canada1.s3.dualstack.ca-central-1.amazonaws.com/flex035/uploads/hibernate/original/1X/ecd5cb68a03c2ea891106c739c0fdb098195899c.jpg)

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 22, 2018, 5:08am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/9 "2018-02-22T05:08:38Z")

</div>

But in that diagram, the Query plan only takes 16 MB. Anyway, if it does not leak memory, just add sufficient RAM.

Nowadays, notebooks have 16 GB of RAM and servers way more than that. A 1 GB JVM is not out of the ordinary for an enterprise application.

The plan cache is just a cache. You can tune it to cache less and save memory, but it will cost you in terms of extra CUP processing.

---

<div class="post-metadata">

**Author:** ![ashwani\_agarwal](https://avatars.discourse-cdn.com/v4/letter/a/da6949/32.png) [@ashwani\_agarwal](https://discourse.hibernate.org/u/ashwani_agarwal)\
**Post date:** [February 22, 2018, 8:02am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/10 "2018-02-22T08:02:22Z")

</div>

Hi Vlad,

Thanks for your input and view will decrease the quert plan cache as you mentioned in the below thread  
[https://forum.hibernate.org/viewtopic.php?f=1&t=1044019](https://forum.hibernate.org/viewtopic.php?f=1&t=1044019)

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 22, 2018, 8:04am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/11 "2018-02-22T08:04:23Z")

</div>

Sure thing. You can configure these two properties to adjust the query plan cache size.

1. `hibernate.query.plan_cache_max_size`
2. `hibernate.query.plan_parameter_metadata_max_size`

---

<div class="post-metadata">

**Author:** ![ashwani\_agarwal](https://avatars.discourse-cdn.com/v4/letter/a/da6949/32.png) [@ashwani\_agarwal](https://discourse.hibernate.org/u/ashwani_agarwal)\
**Post date:** [February 22, 2018, 10:27am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/12 "2018-02-22T10:27:04Z")

</div>

Is there any way to disable this query plan cache?

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 22, 2018, 11:14am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/13 "2018-02-22T11:14:10Z")

</div>

If you set the `hibernate.query.plan_cache_max_size` to `0`, it will disable the plan cache, but it’s not a good idea because the performance of JPQL and Criteria API will not be very good.

---

<div class="post-metadata">

**Author:** ![ashwani\_agarwal](https://avatars.discourse-cdn.com/v4/letter/a/da6949/32.png) [@ashwani\_agarwal](https://discourse.hibernate.org/u/ashwani_agarwal)\
**Post date:** [March 5, 2018, 3:07pm UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/15 "2018-03-05T15:07:56Z")

</div>

**hibernate.batch\_fetch\_style =DYNAMIC** fixed my issue

---

<div class="post-metadata">

**Author:** ![gmarziou](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/gmarziou/32/598_2.png) [@gmarziou](https://discourse.hibernate.org/u/gmarziou)\
**Post date:** [May 20, 2019, 5:37pm UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/16 "2019-05-20T17:37:34Z")

</div>

I also set plan\_cache\_max\_size to a low number because we had many queries using batch updates like:

```
@Query("Update MyEntity m SET m.result = 1 WHERE m.id IN :ids")

```

and each one consumed few MB in cache.

Due to different lists of ids in their where clause, I suspect that caching query plan for batch updates does not bring any benefit because they won’t be re-used.

---

<div class="post-metadata">

**Author:** ![gmarziou](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/gmarziou/32/598_2.png) [@gmarziou](https://discourse.hibernate.org/u/gmarziou)\
**Post date:** [May 21, 2019, 8:56am UTC](https://discourse.hibernate.org/t/disable-query-plan-execution-of-hql-in-hibernate-5-2-11final-at-the-time-of-application-loading/213/17 "2019-05-21T08:56:27Z")

</div>

By the way @vlad , setting plan\_cache\_max\_size to 0 is not possible because it fails while initializing a BoundedConcurrentHashMap with a concurrency level of 20, so the minimal value should be 40.

```auto
Caused by: java.lang.IllegalArgumentException: Maximum capacity has to be at least twice the concurrencyLevel
        at org.hibernate.internal.util.collections.BoundedConcurrentHashMap.<init>(BoundedConcurrentHashMap.java:1648)
        at org.hibernate.internal.util.collections.BoundedConcurrentHashMap.<init>(BoundedConcurrentHashMap.java:1722)
        at org.hibernate.engine.query.spi.QueryPlanCache.<init>(QueryPlanCache.java:119)
        at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:289)

```