A Guide to AP Query Diagnosis and Tuning

How to Diagnose Analytical Queries Systematically in Distributed AP Tuning

1. Why AP Queries Need a Dedicated Tuning Approach

In an HTAP architecture, TP queries and AP queries have fundamentally different execution profiles. TP queries typically touch a small amount of data, relying on primary keys or indexes for precise lookups and targeting millisecond-level response times. AP queries, by contrast, must scan large volumes of data, join multiple tables, and perform complex aggregations and subqueries. Their execution plans run deep, involve many operators, and depend heavily on parallel execution and columnar scans.

This difference dictates entirely different tuning strategies. When a TP query is slow, adding an index usually does the trick. AP query slowness rarely has such a simple fix — the problem may lie in join order, unexpanded subqueries, statistics skew, data skew, or a combination of these factors.

In a distributed database, AP query complexity escalates further. Data is spread across multiple nodes, and query execution requires multi-node coordination — a HASH JOIN may need to shuffle the driving table’s data across the network to all participating nodes, making network latency and bandwidth new bottleneck dimensions. Even more troublesome is uneven data distribution: if the join key values are heavily skewed, large amounts of data get routed to the same thread, leaving other threads idle while overall elapsed time is determined by the slowest thread. On top of that, EXCHANGE operators in distributed execution plans introduce additional overhead for serialization, transmission, and deserialization.

Traditional single-node database tuning experience does not fully apply to distributed AP scenarios. In a single-node environment, bottlenecks usually concentrate on CPU and disk I/O, with relatively clear optimization targets. In a distributed environment, network transfer, data redistribution strategies, degree-of-parallelism allocation, and load balancing across nodes all become additional considerations. An execution plan that performs well on a single node may degrade significantly in a distributed setting due to a poor shuffle strategy.

Therefore, AP query tuning requires a systematic approach: first understand the execution plan, locate the real bottleneck, filter out harmless noise, and then choose the right optimization technique for the root cause.

2. Common AP Query Problem Types

In practice, the vast majority of slow AP queries can be classified into the following categories:

• Excessive scan volume: Full table scans lack effective skip mechanisms, or the wrong index is chosen. Fact tables in AP queries often contain hundreds of millions of rows; a single unnecessary full table scan may read tens of GB of data, with only a tiny fraction of rows being relevant. Columnar storage reduces read width, but the waste at the row level is still costly.

• Suboptimal join algorithm or order: NLJ, HASH, or MERGE join algorithms are poorly chosen, or the join order causes intermediate result bloat. A typical star schema in AP scenarios involves joins of five to ten tables. The number of possible join orderings is enormous — once the optimizer picks wrong, intermediate results can balloon from millions to hundreds of millions of rows, dragging down every subsequent operator.

• Unexpanded subqueries: Correlated subqueries degrade into SUBPLAN FILTER, re-executing once for every outer row. Nested subqueries are common in AP reports; once they cannot be expanded into joins, the subquery must execute as many times as there are rows in the outer table, turning linear complexity into quadratic.

• Excessive aggregation and sort overhead: A sort-based aggregation implementation is chosen, which is expensive at large data volumes. AP query aggregation inputs typically range from millions to hundreds of millions of rows. A sort-based implementation must sort all input data, with a time complexity of O(N log N), far higher than the O(N) of hash aggregation.

• Statistics skew: The optimizer’s row estimates deviate significantly from actuals, triggering a cascade of poor decisions. Multi-table joins in AP queries amplify estimation errors — a 3× error at the base table level can become 30× after two joins, ultimately warping the entire plan shape.

• Parameter/type mismatch: Type inconsistency on both sides of a predicate causes implicit CAST, which invalidates indexes. AP queries involve many tables and columns; when SQL is assembled across systems, type alignment is easily overlooked. A single implicit conversion can turn an index scan into a full table scan.

• Insufficient parallelism or PDML limitations: Too low a degree of parallelism fails to leverage multi-core advantages, or writes have not enabled Parallel DML. AP queries process large data volumes; setting parallelism too low artificially creates a bottleneck. Without PDML enabled, large batch writes can only proceed in a single thread.

• Data skew and poor shuffle strategy: In distributed scenarios, uneven data distribution causes long-tail threads. When join keys contain hot values, HASH shuffle on those keys funnels large amounts of data to a few threads. Other threads finish and wait; overall elapsed time is determined by the slowest thread.

• SQL Work Area memory spill: Sort or hash areas run out of memory, causing data to spill to disk. AP queries handle large data volumes; when memory cannot hold the data, spills are triggered, and performance can drop by one to two orders of magnitude.

• Plan regression and stale SPM baselines: After data changes, a previously fixed baseline may no longer represent the optimal plan. AP scenarios see rapid data growth; a baseline bound a month ago may no longer suit the current data distribution.

These problems frequently appear in combination. The key to diagnosis is identifying the primary contributor from the execution plan.

3. Diagnostic Framework

Diagnostic Toolchain

OceanBase provides a three-tier diagnostic toolchain, ranging from quick triage to operator-level deep analysis:

SQL Audit is the lightest-weight entry point for investigation. The GV$OB_SQL_AUDIT view records execution summary information for every SQL statement. By sequentially checking RETRY_CNT, QUEUE_TIME, GET_PLAN_TIME, and EXECUTE_TIME, you can quickly determine whether the problem lies in the execution environment or the plan itself. The logic behind this check order is: first, check whether RETRY_CNT is greater than zero — if the statement was retried, it encountered environmental issues such as lock conflicts or leader switches, and the execution time includes retry overhead, so you cannot directly assess plan quality; next, check QUEUE_TIME — if queuing time dominates, the issue is resource contention rather than the plan; then check GET_PLAN_TIME — abnormally high values may indicate hard parse overhead or low Plan Cache hit rates; finally, check EXECUTE_TIME — only after confirming it is purely slow execution is it worth analyzing the execution plan in depth.

SELECT svr_ip, trace_id, query_sql, retry_cnt, queue_time, get_plan_time, execute_time
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE tenant_id = <tenant_id>
  AND request_time > time_to_usec(now() - INTERVAL 10 MINUTE)
ORDER BY execute_time DESC
LIMIT 20;

DBMS_XPLAN / Execution Plan Inspection is the critical bridge between identifying a problem and performing operator-level analysis. It reveals the join order, join algorithms, access paths, and distribution strategies chosen by the optimizer. The core purpose of this step is to determine whether the problem is at the plan level or the execution level: if the plan shape itself has obvious flaws (e.g., wrong join algorithm, full table scan instead of index scan), prioritize adjusting the plan; only if the plan is sound but execution is still slow should you drill down into per-thread operator-level execution data.

SQL_PLAN_MONITOR provides operator-level real-time monitoring. The GV$SQL_PLAN_MONITOR view records thread count, output rows, scanned rows, and execution time for each operator, enabling deep analysis of per-thread execution details. This view answers key questions: Which operator is the bottleneck — determined by comparing each operator's CPU TIME share; is parallel execution balanced — determined by comparing processed rows and elapsed time for the same operator across different threads to detect load skew; what is the I/O pattern — by examining each operator's I/O data, including read volume and spill volume, you can directly determine whether disk spill is occurring and which operator is spilling.

The typical usage sequence for diagnostic tools follows a three-step progression: first, use SQL Audit to confirm the problem and rule out environmental factors such as retries and queuing; second, use DBMS_XPLAN to examine the execution plan and determine whether the issue is at the plan level or the execution level; third, if the plan is sound but execution is still slow, use SQL_PLAN_MONITOR to drill into per-thread operator execution details and pinpoint bottleneck operators and skew issues.

Core Analysis Dimensions for Each Tool

Now that the toolchain and usage sequence are clear, the next question is which metrics to focus on when using these tools. Below is an overview of several core analysis dimensions; specific execution plan interpretation details are covered in subsequent sections:

CPU TIME for hotspot identification: Normalize each operator’s CPU TIME against the total time — operators with a high share are hotspots. Operators accounting for less than 10% can generally be ignored. However, high CPU TIME does not necessarily mean there is a problem; you must also consider the operator type to determine the root cause — if a TABLE SCAN operator has high CPU time and scans a large volume, it may indicate a missing index; if a HASH JOIN operator has high CPU time but reasonable input row counts, the data volume may simply be large, and there may not be much to optimize.

IO TIME for detecting memory spills: If a sort or hash operator’s IO TIME is abnormally high, it can almost certainly be attributed to insufficient SQL Work Area memory causing data to spill to disk. The rule of thumb is: when an operator’s IO TIME exceeds 50% of its total elapsed time, and the operator type is SORT or HASH JOIN, you should consider the possibility of a memory spill.

Scan-to-output ratio for detecting ineffective scans: For scan operators, if the scanned row count divided by the output row count exceeds 100, it means large amounts of data are being scanned and then filtered out — a strong signal for index optimization. A ratio below 10 is generally acceptable; between 10 and 100 is worth noting but not necessarily urgent; above 100 should be treated as a high-priority optimization target.

EST vs REAL deviation for diagnosing statistics issues: Compare the optimizer’s estimated row counts against actual row counts. Base table scan estimation deviations exceeding 5× require close attention; join result deviations of 2–10× are within the normal range. The reason base table deviations are more serious than join deviations is that the base table estimate is the input assumption for the entire plan — if the base table row count is estimated wrong, all downstream join cost estimates will be wrong as well, causing the optimizer to choose incorrect join orders and algorithms. Join result deviations, on the other hand, are typically the cumulative effect of multiple estimation errors; correcting any single one may not improve the overall plan.

Filtering false positives: The following signals can be safely skipped — high CPU on EXCHANGE/PX COORD operators is normal, as these operators coordinate data transfer between parallel threads; row count deviations on the right side of NLJ are cumulative results rather than statistics issues, since the right side is probed multiple times in the loop, and the displayed total row count is the sum across all probes; deviations with row counts below 100 have limited impact; deviations downstream of Runtime Filters are expected behavior, because Runtime Filters are dynamic filters that take effect at runtime, and the optimizer cannot predict their filtering effect. Attempting to optimize these signals without understanding the underlying mechanisms may introduce incorrect hints that interfere with plan selection.

4. Optimization Capabilities Overview

Once the diagnostic tools above have pinpointed the specific problem, different optimization techniques can be applied depending on the scenario. The following sections cover common problem scenarios.

Poor Join Order or Algorithm Selection

When diagnosis reveals join-related issues — such as HASH JOIN operator time share being too high, intermediate result row counts far exceeding expectations, or NLJ driving-side row counts being too large — optimization revolves around two levers: join order and join algorithm. The reason join order has such a massive impact is the bloat effect of intermediate results: if joining two tables produces an intermediate result much larger than expected, that bloat propagates to all subsequent join steps. For example, joining two large tables first and then filtering with a small dimension table can produce intermediate results reaching hundreds of millions of rows; filtering with the dimension table first and then joining may yield only millions. The LEADING hint controls join order, ensuring that tables with strong filtering power and small result sets participate in joins early, controlling intermediate result size at the source. Regarding join algorithms, HASH JOIN is suitable for equi-joins where both sides have large data volumes, with cost proportional to the build-side row count; NLJ is suitable for scenarios where the driving-side result set is small (typically under 10,000 rows) and the probed side has efficient indexes, with total cost equal to the driving row count multiplied by the per-probe cost. If the driving-side row count is misjudged, NLJ performance degrades sharply. Hints such as use_hash and use_nl can guide the selection. Additionally, Semi JOINs (IN/EXISTS) can be converted to inner joins with filter pushdown for speedup.

Excessive Scan Volume or Ineffective Indexes

When diagnosis shows that a scan operator’s scan-to-output row ratio is excessively high, or the execution plan uses a full table scan instead of the expected index scan, an ineffective scanning problem exists.

OceanBase supports composite index design to reduce ineffective scans and table access-by-index costs. The column order of a composite index follows a core logic: equality condition columns come first, enabling precise positioning within a specific index segment; range condition columns follow, leveraging ordering for range scans; columns needed for output but not for filtering are placed in STORING to form a covering index. The value of a covering index lies in avoiding table access — AP queries involve scanning large numbers of rows, and each table access is a random I/O. When scan volumes reach millions, table access overhead can be several times higher than the index scan itself. For plan misselection caused by statistics skew, dynamic sampling is available to correct estimates without affecting global statistics. For index misuse in ORDER BY + LIMIT scenarios, the Row Goal optimization can be disabled to correct the behavior.

Excessive Aggregation and Sort Overhead

When diagnosis shows that a SORT or MERGE GROUP BY operator’s CPU TIME share is excessively high, or a sort operator exhibits significant IO TIME, the aggregation implementation is likely poorly chosen.

Sort-based MERGE GROUP BY and MERGE DISTINCT are expensive at large data volumes and can be switched to hash implementations via hints. The reason sort-based implementations are suboptimal is that they must sort all input data, with a time complexity of O(N log N), and sorting is a blocking operation — no output can begin until all data is sorted. By contrast, hash implementation has a time complexity of O(N) and, with sufficient memory, can process and output simultaneously, achieving higher pipeline efficiency. The advantage of hash implementation is especially pronounced when the aggregation cardinality is small relative to the input row count. Multi-dimensional aggregations (ROLLUP, CUBE, COUNT DISTINCT) benefit from enabling parallelism to distribute computation across multiple threads.

Unexpanded Subqueries

When the execution plan contains a SUBPLAN FILTER operator with an abnormally high time share, it indicates that correlated subqueries have not been expanded.

The SUBPLAN FILTER operator’s execution model is: for every row in the outer table, the subquery must be fully executed once. Its cost equals the outer row count multiplied by the subquery’s per-execution cost — if the outer table has one million rows and the subquery scans one thousand rows each time, the total processing volume reaches the billions. OceanBase supports unnesting subqueries into joins via UNNEST, avoiding per-row repeated execution. After expansion, the optimizer can choose efficient algorithms such as HASH JOIN to complete matching in one pass, reducing cost from O(M×N) to O(M+N). Regarding the tradeoff between automatic and manual rewriting: the optimizer’s automatic expansion guarantees semantic equivalence, correctly handling edge cases with NULL values and duplicates; manually rewriting IN subqueries as JOINs may introduce semantic differences when NULL values or duplicates are present, so automatic rewriting is recommended as the first choice. For full table scans caused by OR conditions, OR_EXPANSION can split them into multiple branches, each using its own index.

Distributed Parallelism

Most AP queries execute in distributed parallel mode. OceanBase supports multiple shuffle strategies (LOCAL, PARTITION, BROADCAST, HASH), and the choice depends on the data volume comparison and distribution characteristics on both sides of the join. BROADCAST is suitable for scenarios where one side has a small data volume, broadcasting the small table to all nodes to avoid moving the large table; HASH shuffle is suitable for equi-joins where both sides are large tables, redistributing data by join key so that matching key values land on the same node; PARTITION shuffle leverages existing data distribution when the join key happens to be the partition key, avoiding redistribution overhead. The PQ_DISTRIBUTE hint can guide data redistribution. For load skew in parallel execution, the cause is typically hot values in join keys or GROUP BY keys, funneling large amounts of data to the same thread. The diagnostic method is to compare processed row counts by thread using SQL_PLAN_MONITOR or ODC Query Profile; if the maximum thread's processing volume exceeds 3× the average, skew is confirmed. Mitigation includes adjusting the shuffle strategy or performing pre-aggregation before the shuffle to reduce data transfer. Parallelism is set via query-level PARALLEL hints; PDML can be enabled for large-volume writes.

Resource Management and Plan Stability

When sort/hash operators frequently spill to disk, adjusting the SQL Work Area memory allocation can help. The performance impact of spills goes beyond disk I/O latency — the spill process involves serializing and writing data out, then merging it back in during read, and a single spill can increase operator elapsed time by more than 10×. When multiple operators spill simultaneously, I/O contention further amplifies the impact. For plan regression caused by stale SPM baselines, NO_USE_SPM can temporarily bypass baseline verification; after confirmation, the baseline management process should be followed to update it. A typical scenario for baseline expiration is: after significant data growth, a plan that originally used NLJ is no longer suitable because the driving-side row count has increased, but the baseline still forces the old plan. In this case, use hints first to verify that the new plan is indeed better, then replace the baseline through the SPM process.

5. Tuning Recommendation Priority

When facing the same problem, select techniques from top to bottom according to the following priority, escalating to the next level only when the current level cannot resolve the issue:

The logic behind this priority is a tradeoff between speed of effect and magnitude of side effects. It is important to emphasize: for inaccurate row estimates, dynamic sampling should be preferred over gathering statistics. Dynamic sampling performs small-scale sampling on relevant tables only when the current SQL is compiled, without consuming large amounts of cluster resources or affecting other queries’ plan choices like full statistics collection does. Dynamic sampling results affect only the current compilation, whereas gathering statistics changes global statistics and may cause plan changes for other SQL statements. Therefore, unless statistics are genuinely missing or clearly stale, dynamic sampling is the safer choice. Hints must directly target the root cause — do not push irrelevant hints just for the sake of following priority.

6. Closed-Loop Process from Diagnosis to Optimization

A complete AP query tuning cycle is a closed loop:

• SQL Audit identifies the problematic SQL (ruling out retry / queue / plan cache interference)

• DBMS_XPLAN examines the execution plan to determine whether the issue is at the plan level or the execution level

• SQL_PLAN_MONITOR / ODC Query Profile drills into operator-level, per-thread execution details

• Locate hotspots (CPU TIME / IO TIME / scan-to-output ratio / EST vs REAL)

• Filter out false positives

• Match problem scenarios to optimization techniques

• Formulate a plan by priority (Hints → Indexes → Rewrites → Statistics)

• Verify results (compare plans and elapsed times before and after optimization)

Tuning is not a one-shot deal — after making changes, you must re-examine the execution plan to confirm improvement and ensure no new issues were introduced. This closed-loop process provides a repeatable methodology: follow fixed steps to progressively narrow down the problem scope, avoiding guesswork and repeated trial-and-error. There is no one-size-fits-all solution for AP query tuning, but there is a stable diagnostic methodology — read the primary contradiction from the execution plan, then select the appropriate technique according to priority. With this approach, most slow queries can be resolved systematically.