Guide: Ebean query plan capture

Purpose

This guide explains how to enable and configure query plan capture in Ebean — the mechanism that captures the database's actual execution plan (via EXPLAIN) for slow queries, so you can diagnose missing indexes and poor plans in production.

Use this guide when you want Ebean to record real query plans, when tuning the capture thresholds and load limits, or when wiring a listener to ship captured plans somewhere.

---

Overview

Query plan capture is a two-phase mechanism:

1. Bind capture — when enabled, Ebean watches query executions and, for queries slower than a threshold, captures the actual bind values that were used. This is cheap: it just remembers the parameters of a slow execution.
2. Plan capture — using those captured bind values, Ebean runs EXPLAIN <sql> against the database to obtain the execution plan, producing MetaQueryPlan results that are handed to a QueryPlanListener.

Plan capture is split this way so the expensive EXPLAIN work (actual database load) happens periodically or on demand, against representative bind values, rather than on every slow query.

Two ways to trigger phase 2:

• Automatic periodic capture — a background timer collects plans on a schedule.
• On demand — call the MetaInfoManager API to arm and collect plans yourself (this is what remote tooling such as ebean-insight uses).

Plan capable queries are:

• ORM entity SELECT queries (orm.* metrics) — captured via the per-entity BeanDescriptor.
• Native-SQL DtoQuery (dto.* metrics) — a DtoQuery created from a SQL string (DB.findDto(MyDto.class, "select ...")) has its own bind capture and is EXPLAIN'd directly.
• ORM-backed DtoQuery (Query.asDto(...)) — captured via the underlying ORM query plan (orm.*), not the dto.* plan. The dto.* plan itself is not armed in this case, so it does not double-count in queryPlanInit.
• Native-SQL SqlQuery (sql.query.* metrics) — a labelled SqlQuery (DB.sqlQuery("select ...").setLabel("myLabel")) has its own bind capture and is EXPLAIN'd directly. A label is required: without setLabel(...) the query produces no metric and no plan.

Specifically excluded are:

• Update / DML — orm.update.*, iud.*, sql.update.*, sql.call.*.

Bind capture is wired into the ORM query path (per-entity BeanDescriptor), the native-SQL DTO path (per-DTO DtoBeanDescriptor), and the native-SQL SqlQuery path (the relational query engine); the init/collect API iterates all three. DML — even though it produces timing metrics — never captures bind values and cannot be EXPLAIN'd.

Cost when disabled: SqlQuery plan capture is fully gated on the queryPlan.enable master switch. When capture is disabled no SqlQuery plans are created or cached, so labelled queries incur no extra cost beyond their existing timing metric.

---

Step 1 - Enable bind capture

Bind capture is the master switch; nothing is captured until it is on.

Security — bind values may contain PII. Bind capture records the actual parameter values used by slow query executions, and those values are stored and shown verbatim in the captured plan output (alongside the SQL and EXPLAIN plan). They can therefore contain personal or otherwise sensitive data. Capture is opt-in and off by default (queryPlan.enable=false): only enable it where that data exposure is acceptable, restrict who can read captured plans, and prefer arming specific query hashes (Step 3) over a low global threshold so you capture the minimum needed.

Database database = Database.builder()
  .queryPlanEnable(true)               // turn on bind capture
  .queryPlanThresholdMicros(100_000)   // capture binds for queries slower than 100ms
  .build();

• queryPlanEnable(boolean) — enable bind capture. Default false.
• queryPlanThresholdMicros(long) — global execution-time threshold (microseconds) a query must exceed before its bind values are captured. Default Long.MAX_VALUE (effectively off), so you must either lower it or arm specific plans by hash (Step 3).

Equivalent application.properties (avaje-config / properties):

queryPlan.enable=true
queryPlan.thresholdMicros=100000

---

Step 2 - Enable automatic periodic capture (optional)

To have Ebean periodically run EXPLAIN for armed queries and report the plans:

Database database = Database.builder()
  .queryPlanEnable(true)
  .queryPlanThresholdMicros(100_000)
  .queryPlanCapture(true)                  // turn on the periodic capture timer
  .queryPlanCapturePeriodSecs(600)         // every 10 minutes (default)
  .queryPlanCaptureMaxTimeMillis(10_000)   // stop after 10s of capturing per cycle
  .queryPlanCaptureMaxCount(10)            // at most 10 plans per cycle
  .queryPlanListener(capture -> {
    for (var plan : capture.plans()) {
      System.out.println(plan.label() + "\n" + plan.plan());
    }
  })
  .build();

• queryPlanCapture(boolean) — enable the background periodic capture. Default false.
• queryPlanCapturePeriodSecs(long) — capture frequency in seconds. Default 600 (10 min).
• queryPlanCaptureMaxTimeMillis(long) — per-cycle time budget; capture stops once exceeded, bounding the database load. Default 10000 (10s).
• queryPlanCaptureMaxCount(int) — max plans captured per cycle. Default 10.
• queryPlanListener(QueryPlanListener) — receives each QueryPlanCapture. If not set, the default listener logs plans to the io.ebean.QUERYPLAN logger at INFO.

Properties form:

queryPlan.enable=true
queryPlan.thresholdMicros=100000
queryPlan.capture=true
queryPlan.capturePeriodSecs=600
queryPlan.captureMaxTimeMillis=10000
queryPlan.captureMaxCount=10

---

Step 3 - Capture on demand (foreground)

Instead of (or in addition to) the periodic timer, drive capture through database.metaInfo(). This is useful for targeted capture and is how remote tooling arms specific slow queries by their plan hash.

import io.ebean.meta.MetaInfoManager;
import io.ebean.meta.MetaQueryPlan;
import io.ebean.meta.QueryPlanInit;
import io.ebean.meta.QueryPlanRequest;

MetaInfoManager meta = database.metaInfo();

// Phase 1: arm bind capture - either all plans or specific hashes
QueryPlanInit init = new QueryPlanInit();
init.setAll(true);                 // or init.add("<planHash>", 50_000);
init.thresholdMicros(100_000);
List<MetaQueryPlan> armed = meta.queryPlanInit(init);

// ... let the application run so slow executions capture their bind values ...

// Phase 2: collect plans now (runs EXPLAIN)
QueryPlanRequest request = new QueryPlanRequest();
request.maxCount(10);
request.maxTimeMillis(10_000);
request.since(System.currentTimeMillis() - 300_000); // binds at least ~5 min old
List<MetaQueryPlan> plans = meta.queryPlanCollectNow(request);

• QueryPlanInit arms bind capture. setAll(true) arms every plan; add(hash, micros) arms a specific plan (a hash of "all" is treated as all).
• QueryPlanRequest.since(epochMillis) ensures the captured bind values have existed for a while, so they better represent the slowest executions. maxCount / maxTimeMillis bound the work, mirroring the periodic settings.

MetaQueryPlan exposes beanType(), label(), profileLocation(), sql(), hash(), bind(), plan() (the raw EXPLAIN output), queryTimeMicros(), captureCount(), captureMicros(), and whenCaptured().

---

Step 4 - EXPLAIN dialect

Ebean chooses the EXPLAIN statement per database platform:

Platform	EXPLAIN used
PostgreSQL	explain (analyze, costs, verbose, buffers) <sql>
YugabyteDB	explain (analyze, buffers, dist) <sql>
Oracle	EXPLAIN PLAN FOR <sql>
SQL Server	platform-specific logger
H2 / MySQL / other	explain <sql>

Override the prefix with queryPlanExplain(..) (or queryPlan.explain):

Database.builder()
  .queryPlanExplain("explain (costs, verbose)") // omit ANALYZE on Postgres
  .build();

Caution (PostgreSQL / Yugabyte): the default includes ANALYZE, which actually executes the query to produce real timings. For non-idempotent or expensive queries, override with a non-ANALYZE explain to avoid side effects and extra load.

---

Related setting: internal plan TTL

queryPlanTTLSeconds(int) (default 300) is a different concept — it is the time to live for Ebean's internal query plan (the object that knows how to execute a query, read the result set and collect metrics). It is not part of EXPLAIN capture, but is set through the same builder.

---

Troubleshooting

No plans are captured

1. queryPlanEnable(true) must be set — it is the master switch.
2. queryPlanThresholdMicros defaults to Long.MAX_VALUE. Lower it, or arm specific plans via QueryPlanInit, otherwise no execution is ever "slow enough".
3. For periodic capture, also set queryPlanCapture(true).
4. Queries must actually run slower than the threshold to have their binds captured.

Plans appear but nothing is reported anywhere

No queryPlanListener is configured, so plans go to the default io.ebean.QUERYPLAN logger. Set a listener, or enable INFO logging for io.ebean.QUERYPLAN.

Capture adds noticeable database load

EXPLAIN ANALYZE executes the query. Reduce queryPlanCaptureMaxCount, increase queryPlanCapturePeriodSecs, tighten queryPlanCaptureMaxTimeMillis, or override queryPlanExplain to a non-ANALYZE form.

An unlabelled SqlQuery or update metric never offers plan capture

ORM entity SELECT queries (orm.*), native-SQL DtoQuery (dto.*) and native-SQL labelled SqlQuery (sql.query.*) are plan capable. ORM-backed DTO queries (Query.asDto(...)) are captured via their underlying ORM plan (orm.*), not the dto.* plan. An unlabelled SqlQuery produces no metric and no plan — add setLabel(...) to make it capturable. Write metrics (orm.update.*, iud.*, sql.update.*, sql.call.*) have no bind capture and are intentionally excluded.