Checkout Latency After a Database Failover
An e-commerce checkout flow runs on three tiers: a Java front end (App Server tier), a Node.js order service, and an Oracle database. Baseline checkout p95 is 420 ms. After a database failover on Tuesday, checkout p95 climbs to 2.1 s and stays there. The App Server tier shows no increase in its own average response time, and the order service's average is only slightly up (from 90 ms to 110 ms), yet business transaction (BT) response time for 'Checkout' is consistently 1.6-1.9 s. The DBA reports database CPU is normal. Exit calls from the order service to Oracle show p95 of 1.8 s with a wide spread, while the median is 95 ms. The team wants to know where to look first and how to prove the cause without guesswork.
1. The tier-level averages look almost healthy but the BT is slow. Explain why, and describe the first diagnostic step you would take in the flow map and transaction snapshots.
Read the worked answer
The mismatch is a classic averaging artifact. Tier averages are diluted by the many fast transactions (e.g., catalog reads) that share the tier, while the checkout BT is a specific, smaller population. A p95 of 1.8 s on the Oracle exit call with a healthy 95 ms median means a minority of calls are very slow - exactly the pattern that inflates a BT's tail without moving tier averages much.
First step: open the flow map filtered to the Checkout BT, not the whole tier. Confirm whether the time is spent in the order service or downstream at the Oracle exit. Then pull transaction snapshots for slow Checkout BTs and look at the exit-call timing breakdown. Because the change correlates with a failover, check whether the connection is hitting a different node/listener or waiting on connection acquisition. Compare a slow snapshot against the pre-failover baseline snapshot to see which segment grew.
2. You suspect the slow calls are waiting rather than executing SQL. What evidence in the snapshot would distinguish 'waiting to get a connection' from 'slow query execution', and what would you recommend?
Read the worked answer
In the snapshot's exit-call detail, look at where the time sits relative to the SQL statement. If the JDBC/connection-pool acquisition segment is long but the actual SQL execution segment is short, the app is waiting for a connection, not for the database. Corroborate with connection-pool metrics (active vs. idle connections, wait time, pool exhaustion counters) on the order service tier.
If instead the SQL execution segment itself is long and the DB CPU is normal, the query is likely blocked on a lock, waiting on I/O, or hitting a plan change after failover. The distinguishing evidence is which sub-segment absorbs the time and whether pool wait metrics spike in the same window as the slow exit calls.
Recommendation: if it's pool wait, the failover likely left stale/half-open connections or reduced effective pool size - recycle the pool and verify max pool size vs. concurrency. If it's execution, capture the SQL and ask the DBA to compare the plan and lock waits for the affected statements.
3. Leadership asks for a single number to track recovery. What would you choose and why, given the data described?
Read the worked answer
Track the Checkout BT p95 (or the exit-call p95 to Oracle), not tier averages. The tier averages barely moved (90→110 ms) and would falsely suggest recovery is nearly complete, whereas the customer-facing tail is what actually degraded (420 ms → ~1.9 s). p95 captures the slow minority that the median (95 ms) hides. Pair it with a business metric (checkout completion/abandonment) so the number maps to impact, and set the success criterion at returning p95 toward the 420 ms baseline rather than to the diluted tier average.