Trace Slow AEM JCR Queries with Explain Plans

Repository latency often appears as a slow content page, delayed navigation, or an application request that eventually times out. AEM’s Query Debugger helps separate those symptoms from the underlying Java Content Repository operation by recording query statements, execution times, and repository plans. An explain plan adds the missing perspective: it shows how Oak expects to traverse content, which indexes it can use, and where the estimated cost is concentrated.

A useful investigation joins three views: the application trace, the Query Debugger record, and the repository’s explain plan. This combination is especially important for Australian organisations whose AEM estates serve customers, staff, and partners across different cities and network environments. Start with one representative query, preserve the evidence, and change one variable at a time rather than tuning several components simultaneously.

Capture the right evidence

Use AEM’s Query Debugger and server-side query logging during a tightly scoped reproduction. A useful window might cover one content request, a background job, or a batch of search results. Filter by the relevant package, path, query type, or duration so the resulting trace contains the slow operation without drowning it in unrelated repository traffic.

For each candidate statement, capture the SQL2 query, bind values when available, elapsed time, result size, selected indexes, and any explain plan. Keep the original parameters and timestamps: an apparently expensive query may only become slow for particular values, while a fast query on a cold node can behave differently during normal traffic.

It also helps to identify which part of the application initiated the repository call. If the content page depends on full-text search rather than a direct JCR lookup, compare that boundary with the approach described in AEM and Elasticsearch. A broad JCR fallback following an unsuccessful search can look like a database problem when the real fault sits in the search architecture.

Build a representative query

Reduce the failing statement to the smallest SQL2 query that still returns the same kind of result. A typical starting point might be SELECT * FROM [nt:base] WHERE [jcr:path] = '/content/dam/campaigns' AND [status] = 'approved'. Preserve ordering, pagination, inherited properties, and permission context where they affect execution, but remove unrelated selections and conditions during the first investigation.

Ask the Query Debugger for an explain plan using representative parameter values, then save that plan beside the measured query. Plans can differ as predicates or estimates change, so test empty, common, and large result sets instead of assuming one plan explains every occurrence. Parameter values should be sanitised before a plan or log enters a ticket or chat channel.

Record both cold and warm timings, along with rows returned, bytes delivered, and whether Dispatcher or another cache served the page. This baseline prevents a misleading improvement caused by caching. Repeat the request enough times to distinguish a one-off pause from a consistent pattern, and note concurrent jobs that may be competing for the same repository resources.

Read the plan tree

An Oak explain plan is a tree of operators and estimated work. An index lookup generally indicates that Oak can jump directly to matching nodes, while a traversal scans a wider portion of the repository. Estimated cardinality describes how many nodes the optimiser expects an operator to encounter, and estimated cost provides a relative indication of where the optimiser believes the work will be concentrated.

Compare those estimates with the rows and timing observed in the debugger. A large gap may point to stale statistics, an unusual value distribution, or predicates that the index cannot evaluate precisely. The cheapest-looking operator is not always the fastest in production because network transfer, result serialisation, cache state, and repository contention sit outside the plan’s cost model.

Pay particular attention to unrestricted path conditions, leading wildcards, broad OR clauses, unnecessary joins, and sorting performed after a large result set. These patterns can force extra node visits or expensive comparisons. Rewriting one condition may help more than adding another index, especially when the query returns only a small number of fields but the engine must first discover a much larger candidate set.

Separate application and repository delays

The total response time is not automatically the JCR execution time. Browser and mobile-client delays, Dispatcher cache misses, reverse proxies, AEM rendering, repository work, and a remote MongoDB or relational datastore can all contribute. Record timestamps around each boundary so a slow repository call is not confused with slow application processing or an overloaded content delivery path.

Australian geography makes this distinction particularly valuable. Teams and users may operate across Sydney, Melbourne, Brisbane, and Perth, often moving between office networks, NBN connections, fixed wireless, 4G or 5G, and corporate links. Variability in round-trip time can affect remote repository access, but a network delay cannot be removed by changing a JCR index. Once the repository is local to the AEM environment, investigate its own execution plan.

Time zones add another practical source of confusion. Logs recorded in AEST or AEDT can shift during daylight-saving changes, while teams supporting an Australian operation may compare them with UTC events from an overseas cloud region or a Chicago development environment. Normalise correlation data to UTC and retain the original local timestamp. For distributed support teams, that simple convention prevents a deployment or batch job from appearing to happen at the wrong time.

Tune the query and its indexes

Query shape should be addressed before index count. Narrow the search path, use exact property predicates where the business rules allow it, request only required columns, and paginate results where the interface supports it. An output limit can reduce response size, but it does not necessarily remove expensive work performed before the limit is reached. Confirm that ordering and sorting requirements still produce the intended rows.

Oak indexes are defined as repository configuration and may cover property, node-type, aggregate, or full-text access patterns. Add or adjust an index when the plan shows a traversal that repeatedly serves the same query. A property index is suitable for defined equality or range predicates; Lucene-style indexes address full-text matching and related analysis rather than every JCR lookup. The best index often reflects a stable access pattern shared across several templates or services.

Treat index changes as production changes because they consume storage, increase write work, and can alter optimiser decisions. Validate the new plan in a representative author or publish environment, then compare timings with the saved baseline. Hybrid AEM estates remain common in the Australian market, so test both the target author instance and the delivery topology where possible rather than assuming laboratory results transfer unchanged to a customer-facing environment.

Protect logs and customer information

Query logs can contain paths, identifiers, customer attributes, unpublished content references, and bind values. Under the Privacy Act 1988 and the Australian Privacy Principles, treat that material as personal information when it relates to an identifiable person. Restrict access to diagnostic tooling, minimise captured values, and avoid copying raw logs into general-purpose analytics or collaboration platforms without an approved purpose and retention period.

A log exposed through unauthorised access may also require assessment under the Notifiable Data Breaches scheme when the statutory threshold is met. Establish who can view query traces, how long records remain available, and when they are securely deleted. Redaction must occur before logs reach third-party support services, and production credentials should never be embedded in a diagnostic query or example.

Query tooling also needs a clear owner when it becomes part of a custom developer portal. Architects building extensible authoring and domain-specific language tools can compare relevant practices from the Xtext developer conference. Those development-experience lessons do not replace Australian privacy controls; they help make the tooling usable while keeping data governance explicit.

Verify the change and operationalise the result

Repeat the original workload after tuning, using both cold and warm requests. Compare median and high-percentile timings, rows examined, returned results, and downstream response time. A convincing fix improves the intended query without slowing publishing, indexing, or unrelated content delivery. Keep the before-and-after plans together so future reviewers can see which predicate or index produced the gain.

Store the representative query, sanitised parameters, expected plan shape, and alert thresholds in the team’s performance runbook. Include a lightweight regression check in controlled environments, but avoid turning expensive production-shaped queries into routine CI tests. Monitor query frequency and slow-query duration after release, since a changed access pattern can create a new bottleneck once content volume grows.

For recorded talks and further technical material from CIRCUIT’s Chicago conferences, browse the CIRCUIT session archive. Choose one slow query this week, capture its explain plan, and bring the measured before-and-after result to your next AEM performance review.