Hibernate’s Query Plan Cache – How It Works and How to Tune It
Take your skills to the next level!
The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.
Hibernate’s Query Plan Cache speeds up the preparation of your queries. That reduces their overall execution time, and improves the performance of your application. In the test scenario of this article, it improved the performance of the query preparation by up to 500%. To make it even better, Hibernate does all of that automatically. The only thing you should do is making sure that the cache has the right size. I will show you how to do that in this article.
But before we dive into the configuration and monitoring of your cache, let’s quickly discuss why Hibernate uses such a cache.
Hibernate has to prepare a query before it can execute it. If it’s a JPQL or CriteriaQuery, Hibernate generates an Abstract Syntax Tree (AST), identifies bind parameters and return types, and generates an SQL statement. This is often called “compiling a query”. For native queries, Hibernate only extracts some metadata, like used bind parameters and the return type of the query. All of this takes time and resources.
Hibernate’s Query Plan Cache optimizes this process by caching the plans of previously executed queries. Every time you create and execute a new query, Hibernate first checks if the cache already contains a plan for it. If not, it compiles the query and adds the plan to the cache.
Configure Hibernate’s Query Plan Cache
By default, the cache is active and stores up to 2048 query plans. This is sufficient to cache the plans of all queries executed by a lot of applications. But it might be too small for huge enterprise applications. In that case, Hibernate has to compile the plan of a query, remove another plan from the cache, and add the new one. That slows down the execution of your query. You can adjust the size of the query plan cache by setting the property hibernate.query.plan_cache_max_size in your persistence.xml file.
<persistence>
<persistence-unit name="my-persistence-unit">
...
<properties>
<!-- Max number of cached plans - default: 2048 -->
<property name="hibernate.query.plan_cache_max_size" value="4096"/>
<!-- Max number of cached metadata - default: 128 -->
<property name="hibernate.query.plan_parameter_metadata_max_size" value="256"/>
</properties>
</persistence-unit>
</persistence>
As mentioned earlier, Hibernate only stores metadata about native queries in its cache. You can customize the size of that part of query plan cache by configuring the property hibernate.query.plan_parameter_metadata_max_size. By default, it’s set to 128.
Get Insights on the Cache Usage
Before you start changing the size of your caches, you should first check if it needs to be adjusted. Since Hibernate 5.4, you can easily do that using Hibernate’s Statistics component. In addition to the log messages that provide you an overview of the performed operations, it also exposes more fine-grained information via its API. The number of hits and misses on the Query Plan Cache are 2 of the provided metrics.
Before you can access these metrics, you need to activate Hibernate’s statistics. You can do that by setting the property hibernate.generate_statistics in your persistence.xml to true or by providing this configuration as a system property.
<persistence>
<persistence-unit name="my-persistence-unit">
...
<properties>
<property name="hibernate.generate_statistics" value="true"/>
</properties>
</persistence-unit>
</persistence>
After you did that, you can call the getStatistics() method on Hibernate’s SessionFactory to get a Statistics interface. It provides 2 getter methods to get the number of hits and misses.
Statistics statistics = sessionFactory.getStatistics(); long hitCount = statistics.getQueryPlanCacheHitCount(); long missCount = statistics.getQueryPlanCacheMissCount();
In the following code snippet, you can see a test case that executes 2 queries 1000 times. After that, it prints out the tracked queries and their number of hits and misses on the Query Plan Cache.
@Test
public void testQueryPlanCache() {
EntityManager em = emf.createEntityManager();
SessionFactory sessionFactory = emf.unwrap(SessionFactory.class);
Statistics statistics = sessionFactory.getStatistics();
em.getTransaction().begin();
for (int i = 0; i < 1000; i++) {
em.createQuery("select p from ChessPlayer p", ChessPlayer.class);
em.createQuery("select g from ChessGame g", ChessGame.class);
}
for (String query : statistics.getQueries()) {
log.info("Executed query: " + query);
}
log.info("Hit Count: " + statistics.getQueryPlanCacheHitCount());
log.info("Miss Count: " + statistics.getQueryPlanCacheMissCount());
em.getTransaction().commit();
em.close();
}
When you execute this test case, you can see in the log output that the preparation of 2000 queries caused 2 misses and 3998 hits on the Query Plan Cache. The 2 misses happened when Hibernate prepared each query for the 1st time. After that, each request to the cache returned the plan and was tracked as a hit.
11:55:32,896 INFO TestSample:99 - Executed query: select g from ChessGame g 11:55:32,897 INFO TestSample:99 - Executed query: select p from ChessPlayer p 11:55:32,897 INFO TestSample:107 - Hit Count: 3998 11:55:32,897 INFO TestSample:108 - Miss Count: 2
One thing to point out here is that the number of hits and misses adds up to 4000. That’s twice as much as the number of executed queries. If you look into Hibernate’s code, you can see that it accesses the cache twice for each query. One request to get the parameter metadata, and another request to get the result type.
Performance Improvements Provided by the Query Plan Cache
The performance improvements provided by Hibernate’s Query Plan Cache depend on the type of query you’re executing.
For JPQL and Criteria Queries, Hibernate parses the statement, determines metadata on parameters and return types, creates an Abstract Syntax Tree, and generates the statement. That is a complex and resource-consuming process that highly benefits from caching.
As mentioned earlier, Hibernate doesn’t compile native queries and only caches their metadata. Due to that, the performance benefit for native queries is lower than for JPQL or Criteria Queries.
Performance Benefits for JPQL and Criteria Queries
Let’s use the default configuration, rerun the same test as before and measure how long it takes to prepare 2000 JPQL queries.
long start = System.nanoTime();
for (int i = 0; i < 1000; i++) {
em.createQuery("select p from ChessPlayer p", ChessPlayer.class);
em.createQuery("select g from ChessGame g", ChessGame.class);
}
long end = System.nanoTime();
log.info("Query compilation time: "+(end-start)+"ns");
You can see in the log output that Hibernate spend 96800500ns ≈ 96ms to prepare the 2000 queries.
12:29:02,459 INFO TestSample:97 - Query compilation time: 96800500ns 12:29:02,460 INFO TestSample:108 - Hit Count: 3998 12:29:02,460 INFO TestSample:109 - Miss Count: 2
That changes when we change the properties hibernate.query.plan_cache_max_size and hibernate.query.plan_parameter_metadata_max_size to 1, and rerun the test case.
<persistence>
<persistence-unit name="my-persistence-unit">
...
<properties>
<property name="hibernate.query.plan_cache_max_size" value="1"/>
<property name="hibernate.query.plan_parameter_metadata_max_size" value="1"/>
</properties>
</persistence-unit>
</persistence>
This time, Hibernate spend 505305700ns ≈ 505ms preparing the queries. It also tracked 2000 hits and 2000 misses on the Query Plan Cache. As mentioned earlier, during each preparation, Hibernate accesses the cache twice. Because I set the plan_cache_max_size to 1, the first of these 2 requests always fails. This forces Hibernate to prepare the query plan and put it into the cache. The 2nd request then finds it there and gets tracked as a hit.
12:35:37,043 INFO TestSample:97 - Query compilation time: 505305700ns 12:35:37,043 INFO TestSample:108 - Hit Count: 2000 12:35:37,043 INFO TestSample:109 - Miss Count: 2000
If we compare the time measured during both test runs, it becomes evident that the preparation of a query only takes up a small fraction of its overall execution time. But it’s something we can easily improve. In my test case, a Query Plan Cache that was big enough to contain the plans of all queries was 5 times faster than cache that forced Hibernate to compile a new plan for each query. Considering the small effort it takes to configure the cache, this is a performance improvement you shouldn’t miss out on.
Performance Benefits for Native SQL Queries
Let’s perform a similar test with a native SQL query instead of a JPQL query and execute it using Hibernate’s default configuration.
long start = System.nanoTime();
for (int i = 0; i < 1000; i++) {
em.createNativeQuery("select * from ChessPlayer p", ChessPlayer.class);
em.createNativeQuery("select * from ChessGame g", ChessGame.class);
}
long end = System.nanoTime();
log.info("Query preparation time: "+(end-start)+"ns");
The performance benefits for native SQL queries are smaller than for JPQL or Criteria Queries. That’s because the Query Plan Cache stores less information about them. The preparation of the 2000 native queries using the default configuration took 27746100ns ≈ 27ms.
12:51:08,790 INFO TestSample:132 - Query preparation time: 27746100ns 12:51:08,790 INFO TestSample:143 - Hit Count: 0 12:51:08,790 INFO TestSample:144 - Miss Count: 0
Let’s now change the properties hibernate.query.plan_cache_max_size and hibernate.query.plan_parameter_metadata_max_size to 1, and rerun the test case.
<persistence>
<persistence-unit name="my-persistence-unit">
...
<properties>
<property name="hibernate.query.plan_cache_max_size" value="1"/>
<property name="hibernate.query.plan_parameter_metadata_max_size" value="1"/>
</properties>
</persistence-unit>
</persistence>
Because the cache stores less information about native queries, the performance benefits for native SQL queries are smaller than for JPQL or Criteria Queries. This time, the preparation of the 2000 native queries took 42203200ns ≈ 42ms. That is 1.5 times as long as with a correctly sized Query Plan Cache.
12:51:08,790 INFO TestSample:132 - Query preparation time: 42203200ns 12:51:08,790 INFO TestSample:143 - Hit Count: 0 12:51:08,790 INFO TestSample:144 - Miss Count: 0
Conclusion
Hibernate has to prepare a query before it can execute it. Even though this is a fast operation compared to the execution of the query, the preparation consumes resources and time. To avoid this overhead, Hibernate stores the plan of a prepared query in the Query Plan Cache.
You can monitor the hits and misses on the cache using Hibernate’s Statistics component. After activating that component in your configuration, it collects metrics on all performed operations. You can access these metrics via the Statistics interface.
It’s activated by default and can store up to 2048 plans and the metadata of up to 128 parameters. These defaults are a good fit for most applications but might be too small for huge enterprise applications. You can adjust them by setting the properties hibernate.query.plan_cache_max_size and hibernate.query.plan_parameter_metadata_max_size in your persistence.xml file.
