A Hibernate application has enabled second-level caching on a parent entity's @OneToMany association, yet loading each parent still emits one SELECT per child row. How would you diagnose and fix that?

## The symptom and its usual cause One SELECT per child, on a collection that is supposed to be cached, is the classic outcome of caching *only* the collection. A collection region entry contains the **element identifiers**, not the elements' state. On a collection-cache hit Hibernate has the id list and must materialize each element: persistence context → the element entity's cache region → database `select ... from child where id = ?`. When the element entity is not in the second-level cache, that last branch fires once per id. Note what this replaced: an *uncached* collection would have loaded with a single statement, `select ... from child where fk = ?`. So the intervention made things worse — the diagnosis has to be honest about that, and "remove the collection cache" is a legitimate fix. ## Step 1: measure, do not guess Enable `hibernate.generate_statistics` and read `SessionFactory#getStatistics`, specifically the **per-region** views: second-level cache hit count, miss count, put count, and element count for each region name. Interpret: - Collection region: high hits, element region: high misses or zero puts → the elements are not cached. This is the case in the question. - Collection region: high misses too → the collection is not being cached either (wrong annotation, wrong region config, or the read path is a query rather than navigation). - Element region: high puts, high misses, low hits → thrashing; entries are evicted or invalidated before they are read. Pair that with SQL logging (`show_sql`, or better, a datasource proxy that counts statements per request) so you can tie the statement pattern to the region counters. A slow-query dashboard alone will not show this — the individual key lookups are each fast; it is their *count* that hurts. ## Step 2: work down the checklist **Is the element entity cacheable at all?** Under `jakarta.persistence.sharedCache.mode=ENABLE_SELECTIVE` — the effective default in most setups — nothing is cached unless annotated. Adding `@Cache(usage = ...)` (or `@Cacheable`) to the child class is the direct fix. **Do the region names match the provider config?** Hibernate's defaults are the fully-qualified entity name for an entity region and entity-name-plus-field for a collection region. Provider configuration (max entries, TTL, on-heap vs off-heap) is keyed by those exact names; a mismatch leaves the region on some tiny default. Print the region names from `Statistics#getSecondLevelCacheRegionNames()` rather than trusting the config file. **Is the region big enough and long-lived enough?** A region sized for 1,000 entries in front of 200,000 hot children evicts constantly. The statistics' put-versus-hit ratio exposes this immediately. **Is the association write-heavy?** Every structural change to a collection invalidates and rewrites that owner's entry, and every child update invalidates the child's entity-region entry. Under heavy writes the entries never live long enough to serve a read. **Is the read path actually navigation?** The collection cache is only consulted when the association is initialized through the owner. A JPQL query that selects children directly never touches it, so if the code was "optimized" into a repository-style query, the cache is bypassed by construction. **Is the transaction/session setup sane?** Second-level reads happen through a session; code that opens a session per child, or that reads outside a transaction with a provider requiring one, can defeat the strategy. ## Step 3: choose a fix deliberately 1. **Cache the element entity as well.** The minimal change that makes the existing design coherent — the id list resolves into the entity region, and the collection navigation becomes SQL-free. Requires the children to be read-mostly and bounded in count. 2. **Add `@BatchSize` to the element entity or the collection.** Residual misses then load as `where id in (?, ?, …)` instead of one statement each — an order-of-magnitude reduction in round trips even with a partially cold cache. Cheap insurance to keep even when entity caching is on. 3. **Drop the collection cache and fetch properly on the read path.** A `join fetch` in the query that loads the parents, or `@BatchSize` on the collection so N parents' collections load in a few `where fk in (…)` statements, is often faster and far simpler than two coordinated cache regions — especially for large or churning associations. 4. **Re-scope what is cached.** If only a handful of parents are hot, the fix may be to cache their children and leave the long tail alone, which the provider's size limit does naturally once the region is sized correctly. ## Verifying the fix Re-run the same load with statistics on and compare: statement count per request, collection-region hits, element-region hits versus puts. A correct fix shows the element region's hit ratio climbing and the per-child statement count collapsing. Then check the write path did not regress — measure invalidation churn under a realistic write mix, not just a read benchmark, because the cost of collection caching is paid by writers.