High memory usage when using Hibernate

You have 250k entries in your IN query list. Even a native query will put the database to its knees. Oracle limits the IN query listing to 1000 for performance reasons so you should do the same.

Giving it more RAM is not going to solve the problem, you need to limit your select/updates to batches of at most 1000 entries, by using pagination.

Streaming is an option as well, but, for such a large result set, keyset pagination is usually the best option.

If you can do all the processing in the database, then you will not have to move 250k records from the DB to the app. There's a very good reason why many RDBMS offer advanced procedural languages (e.g. PL/SQL, T-SQL).

Notice that even although the number of object within the queryPlanCache can be configured and limited, it is probably not normal having that much.

In our case we were writing queries in hql similar to this:

hql = String.format("from Entity where msisdn='%s'", msisdn);

This resulted in N different queries going to the queryPlanCache. When we changed this query to:

hql = "from Blacklist where msisnd = :msisdn";
...
query.setParameter("msisdn", msisdn);

the size of queryPlanCache was dramatically reduced from 100Mb to almost 0. This second query is translated into a one single preparedStament resulting just one object inside the cache.