When to Increase Parallelism for Analytics Without Breaking Your Transaction Latency Under Mixed HTAP Workloads
Introduction: Why Parallel Execution Deserves Attention
In HTAP scenarios, transactions and analytics often share the same tenant resources. When an analytical query slows down, many teams’ first instinct is to add a parallel hint or raise the session DOP. Inside a batch window, this sometimes does help; but under a mixed workload, the same tactic can bring side effects — the response time of a single report SQL comes down, yet the transaction tail latency starts to jitter, and other analytical jobs and background tasks get dragged down along with it.
Parallel execution is one of the capabilities in OceanBase that tends to pay off relatively easily for analytical queries. The basic idea is to split work that would otherwise be done by a single execution thread across multiple workers that cooperate, thereby shortening the critical path of a large query through its scan, join, and aggregation stages. Given a reasonably large data volume, a query that genuinely has room to parallelize, and enough resource headroom in the current window, setting a sensible degree of parallelism (DOP, Degree of Parallelism) generally makes better use of multi-core CPUs and distributed resources.
The catch is that parallelism does not affect only this one SQL. It directly changes how parallel threads, CPU, I/O, and network resources are allocated within the tenant. A single report getting faster does not mean the system as a whole is more stable; and Auto DOP being on does not mean resource control can be handed over entirely to the optimizer. The optimizer can pick a degree of parallelism for a single query, but it will not judge for you whether the tenant as a whole is still in a state where it makes sense to keep scaling parallelism up.
So when we discuss DOP under a mixed workload, the focus is not “how to tune a given SQL to run faster,” but three questions: which scenarios are worth tuning, how to tell whether the tuning actually took effect, and when to rein it back in.
1. What DOP Actually Optimizes in OceanBase
In OceanBase’s parallel execution framework, DOP determines the number of worker threads that a single DFO (Data Flow Operation — one sub-plan segment within a parallel plan) can use during execution. In other words, DOP expresses “how many parallel resources this query is willing to request during its parallel stage,” not a fixed “speedup multiplier.”
Within a SQL execution plan, not every stage can run in parallel. Final result aggregation, the handoff between parallel stages, and some output-organizing steps still often have to be done serially. What DOP can accelerate is mainly the stages that are naturally suited to being split: wide-range table scans, Hash Join, aggregation, cross-node data redistribution (EXCHANGE), and the like. The appearance of PX (Parallel Execution) operators in a plan only means the optimizer believes certain stages can be parallelized; it does not guarantee that the bottleneck of the whole SQL lies in those stages.
1.1 Which Execution Stages DOP Mainly Accelerates
In practice, the following execution characteristics tend to be a better match for DOP:
Wide-range scans, especially scans on partitioned tables that need to read large amounts of data;
Heavy Hash Join and heavy aggregation;
Data redistribution across OBServers or across partitions;
AP-leaning tasks such as reporting, batch jobs, and ad hoc analytics, where the data processed in a single run is large enough.
For this kind of SQL, if the slowest operator already falls on a parallelizable stage such as scan, join, or aggregation, raising DOP appropriately or enabling Auto DOP is usually worth considering.
1.2 The Boundaries of DOP
DOP needs to be kept separate from “scanning less data.” Partition pruning, index selection, and predicate pushdown address the problem of scan scope; DOP addresses “given that this data must be processed, how to process it faster.” If a SQL genuinely needs to scan and process a large amount of data, then parallel execution is a reasonable way to speed it up; but if the actual scan volume is clearly larger than the business expects, you should first investigate whether there is an execution path that can be optimized, rather than relying on higher DOP alone to paper over the problem.
Likewise, lock waits, serial hot spots, and wrong plans caused by badly skewed statistics are not things that adjusting DOP can fundamentally fix. When you run into these, treat DOP first as a boundary for judgment: confirm whether the problem really lies in the parallel stage, and only then decide whether to tune or to stop.
1.3 Where Auto DOP Fits
The value of Auto DOP lies in reducing the maintenance cost of adding hints one SQL at a time, but it relies on the optimizer’s estimates, and whether those estimates are trustworthy is inseparable from the quality of the statistics. More importantly, Auto DOP optimizes the parallel choice for a single query, which is not the same as tenant-level optimality under a mixed workload, and cannot replace resource isolation, time-window policies, and parallel budget control.
2. When DOP Tuning Fits, and When It Doesn’t
2.1 Scenarios Where DOP Is Worth Considering First
The execution plan is dominated by wide-range scans, heavy joins, and heavy aggregation, and the amount of data actually processed is large enough; the query involves a fair amount of cross-node or cross-partition data redistribution; the task type leans toward reporting, batch jobs, or periodic analytics; and the current period is off-peak, or the tenant still has headroom in parallel threads and CPU.
Analytical queries on partitioned tables, given a reasonable execution path, are often better candidates for a DOP discussion than point lookups on small tables.
2.2 Scenarios Where “Keep Adding DOP” Should Not Be the Main Lever
Point lookups that return a single row or a small number of rows; queries where a highly selective index has already squeezed the scan scope down to something small; high-frequency short queries on typical OLTP paths; and cases where the bottleneck clearly lies in lock waits or serial stages, or where there is no PX in the plan at all.
When you force all queries in a session to use the same DOP, short queries that did not need parallelism in the first place also get dragged down by the parallel framework.
Having PX in the Explain output does not mean “parallelism is already in place,” either. If the slowest operator is not in a parallel stage, adding more DOP usually just amplifies coordination cost and EXCHANGE overhead.
If the scan scope is clearly on the large side, you also need to distinguish further: is this large-scale data that the business genuinely needs to process, or something you can first bring down through partition pruning, index selection, predicate pushdown, and similar techniques?
2.3 A Simple Test: Is This SQL Worth Tuning DOP For
The judgment can be narrowed down to four questions:
First, are the data volume and scan scope large enough, and does the slow part fall on a large scan, a heavy join, or a heavy aggregation?
Second, if the scan scope is large, first determine whether this is data the business genuinely needs to process, or whether it is actually scanning more than expected. In the former case you can keep discussing DOP; in the latter case you should first investigate path issues such as partition pruning, index selection, and predicate pushdown, rather than adding DOP first.
Third, does the query type and the current period allow more parallel resources to be allocated to it?
Fourth, if most of the above conditions are not met, should you instead turn toward statistics, the execution path, or other non-parallel directions?
Only tune DOP once “the data volume is large enough, the hot operator is parallelizable, the task leans analytical, and the period allows it” all hold at the same time; otherwise, stop first, and do not treat DOP as an all-purpose knob.
3. How to Tell Whether DOP Actually Took Effect for a Single SQL
For a single SQL, the questions are: did parallelism get applied where it should have, and did the elapsed time improve noticeably? You can approach this in three steps.
Step one, first check whether this SQL is suitable for parallelism. Against the execution plan, confirm whether the slow part falls on stages such as large scans, heavy joins, or heavy aggregation. If the number of scanned rows or the range of partition access is seriously out of line with business expectations, you should first put the suspicion on the execution path — for example, whether partition pruning is taking effect, or whether the right index is being used — rather than defaulting to “the DOP isn’t big enough.”
Step two, next check whether parallelism is landing on the slowest operator. Having a PX operator in the plan does not mean the slowest stage is actually being accelerated effectively. A typical counterexample: most of the total time is spent in some serial join or table-lookback stage, while PX only appears on a secondary scan. In that case, even if you raise the DOP, the share of time spent by the hot operator may barely change. When judging, focus on the stage type and time share of the slowest operator, rather than only on whether the total RT of the SQL dropped a little.
Step three, verify the actual execution result. A hint, session setting, table-level PARALLEL attribute, or the DOP given by the optimizer are all only "expected values." At actual execution time, the DOP may be downgraded due to resource limits such as parallel_servers_target, or it may be delayed in getting threads because of queuing. So you cannot just look at dop= in the Explain output; you also need to bring in runtime monitoring.
You can usually use GV$SQL_PLAN_MONITOR to look at the actual execution of each operator, focusing on three things: whether the actual DOP is close to what was expected; whether the time spent by the slowest operator drops as DOP is adjusted; and, if the plan has PX but the time of the slowest stage barely changes, then parallelism has not landed on the real hot spot, and you should go back to investigating at the path and operator level rather than keep adding DOP.
If you use Auto DOP, you also need to pay extra attention to statistics quality. When statistics are badly off, you may see cases that should have parallelized but didn’t, or a DOP that is clearly unreasonable. Auto DOP is well suited as a way to reduce manual intervention, but it still needs to be verified against actual execution results, just like a manually specified DOP.
4. Why Bigger DOP Is Not Always Better
4.1 DOP Scales Resource Requests, Not Just Speed
A larger DOP usually means more parallel workers, more EXCHANGE channels, and higher scheduling and coordination cost. Given limited tenant resources, both the benefits and the side effects get amplified at the same time. Thinking of DOP as an “acceleration factor” easily misleads judgment; a more accurate way to put it is: how many extra threads and data-channel resources this query has to request in exchange for stronger parallel processing capability per unit time.
The scale of threads a tenant can use for parallel execution is constrained by variables such as parallel_servers_target. When parallel thread resources are exhausted, new parallel requests have to queue. You may have written parallel(32), but the DOP actually achieved could be far below 32 — and in that case, continuing to raise it usually makes little sense.
4.2 Parallel Gains Usually Do Not Grow Linearly
Within a parallel SQL, there are always serial stages, which cannot possibly shrink in proportion to DOP. Beyond that, there are several common kinds of limits:
If the plan has multiple DFOs running at the same time, the parallel threads have to be distributed across several stages, and will not all be piled onto one hot operator;
Data skew causes some workers to finish early while others drag out the overall progress with a long tail;
The higher the DOP, the higher the scheduling and buffering cost of EXCHANGE and data transfer tends to be;
If I/O, CPU, or network is already the system bottleneck, adding more DOP may just make more threads wait together rather than run faster together.
So raising DOP from 4 to 16 does not necessarily cut the elapsed time to a quarter; sometimes it barely changes, or even gets slightly worse due to framework overhead. These are phenomena to face squarely, not signs that “the parameter just isn’t cranked high enough.”
4.3 Under a Mixed Workload, the Side Effects of High DOP Deserve More Attention
In an HTAP same-tenant scenario, an analytical SQL running a bit faster after its DOP is raised does not mean the system is more stable. When several high-DOP queries run concurrently, you may see parallel threads queuing, CPU contention, and large scans maxing out I/O and network bandwidth, which then squeezes TP queries and the write path. A single SQL getting faster and the overall stability of the tenant need to be looked at separately — the latter is the harder constraint when governing DOP.
4.4 When You Should Not Keep Raising It
When the following signals appear, you should stop and investigate first rather than keep increasing DOP:
The time share of the hot operator barely changed;
The actual DOP is clearly below expectation;
CPU, I/O, and network pressure on the system rose, but the improvement in SQL elapsed time is limited;
PX queuing is starting to increase.
All of these indicate that the problem is more likely in the execution path, resource budget, or where parallelism lands, rather than “the DOP value still isn’t big enough.”
5. How DOP Should Be Governed Under HTAP
5.1 Define the Governance Goal First
Our goal is not to make every analytical SQL as fast as possible, but to improve the execution efficiency of analytical tasks such as reporting and batch jobs within an acceptable impact on TP. In other words: allow the analytical side to get moderately faster, but not at the cost of transaction tail latency spinning out of control, worse write jitter, or widespread degradation of background tasks.
5.2 Set Policies by Time Window and Workload Type
Usually you layer by time window and session type, rather than forcing parallelism uniformly across the whole tenant and all sessions.
Transaction peak periods and online-transaction-related sessions: keep the parallel policy conservative, prioritizing stability and tail latency. You can pair this with a higher parallel_min_scan_time_threshold so that only scans with a long enough estimated time enter parallelism, and use parallel_degree_limit to cap the upper bound of Auto DOP.
Off-peak periods and reporting/batch windows: given available resource headroom, you can relax the parallel policy somewhat and use manual hints or dedicated session configurations for the key batch tasks.
Reporting, batch, and ad hoc analytics sessions: manage them separately from OLTP sessions as much as possible. Beyond using different parallel policies, you can also combine resource groups for isolation, so as to avoid using the same aggressive configuration to directly contend for CPU and parallel thread resources during transaction peaks.
5.3 The Role of Resource Group Isolation in DOP Governance
Under a mixed workload, managing only the DOP upper bound and the Auto DOP threshold is usually not enough. They can decide whether parallelism is turned on and how large it can go at most, but they cannot stop reporting, batch, and transactional queries from fighting each other for resources.
For TP, reporting, batch, and ad hoc analytics coexisting within the same tenant, it is more appropriate to combine resource groups for isolation: let transactional sessions and analytical sessions use different resource groups, constraining CPU and other resource usage separately, so that high-DOP queries do not directly squeeze online transactions during peak periods.
The DOP-related parameters govern how large a single SQL can parallelize; resource groups govern whether, once that SQL parallelizes big, it can drag other business down with it. The former solves “how large it can go,” the latter solves “whether the impact can be contained” — neither can be left out.
5.4 Control Risk with Upper Bounds and a Parallel Budget
Effective DOP control is usually a combined set of settings, not a change to a single parameter:
Decide which SQL is more likely to enter parallelism — for example, through the Auto DOP threshold, or by using hints to enable it only for key SQL;
Decide how large parallelism can go once entered — for example, parallel_degree_limit, manual hints, and the table-level PARALLEL attribute;
Decide the scale of parallel threads the tenant as a whole can bear — for example, variables such as parallel_servers_target, and whether to pair them with resource isolation.
Decide whether different types of workload need further isolation — for example, using resource groups to separate OLTP from reporting/batch sessions, so that high-DOP queries do not directly squeeze transactional resources.
Under a mixed workload, you cannot rely solely on the optimizer to pick a DOP for each SQL individually; you have to first define the boundaries clearly from the tenant’s perspective.
5.5 Where Auto DOP Sits in Governance
For teams unfamiliar with the rules for setting the degree of parallelism, setting parallel_degree_policy to AUTO is a reasonable starting point, but you still need to adjust parallel_min_scan_time_threshold and parallel_degree_limit according to the scenario. In a "query performance first, almost no other load on the tenant" scenario, you can lower the scan parallelism threshold and relax the upper bound somewhat; in a "many concurrent queries, need to protect overall system throughput and latency" scenario, you should limit the maximum DOP and raise the parallelism trigger threshold, to avoid many queries contending for resources at high DOP all at once.
There is a priority relationship among Auto DOP, manual hints, session-level DOP, and the table-level PARALLEL attribute (hints generally take precedence over session and table attributes). When governing, be explicit about which SQL is handed over to Auto DOP, which core reports are precisely controlled by hints, and which OLTP sessions should stay under the MANUAL policy.
6. How to Assess Whether the Current DOP Policy Suits This Tenant
A single SQL running faster does not mean this parallel policy suits the tenant. To judge whether a DOP policy is appropriate, you have to look at both the improvement on the analytical side and the cost on the system side.
6.1 Check Whether the Analytical Side Shows Clear Improvement
If the overall direction of the parallel policy is right, the analytical side should show improvement along observable dimensions, for example:
Whether the overall RT of typical analytical SQL drops;
Whether the total completion time of reporting and batch tasks shortens;
Whether the time to produce results for periodic tasks becomes more stable;
Whether the queuing and execution time of analytical SQL stays within an acceptable range.
These metrics need to be looked at over a time window, comparing before and after the policy change together, not just a single execution of one SQL.
6.2 Check Whether the System Cost Is Too High
At the same time, you also need to look, from the tenant as a whole, at whether the side effects are being amplified in step:
Whether parallel thread utilization stays near the upper bound for long stretches — this usually means the policy, while speeding up some analytics, is also compressing the tenant’s parallel headroom, making queuing more likely later on.
Whether PX queuing is starting to become noticeable.
Whether CPU, I/O, and network pressure rise in step as the parallel policy is relaxed.
Whether write latency, Compaction, or other background tasks start to fluctuate.
If the improvement on the analytical side is limited while these system metrics deteriorate in the same direction, then the current DOP policy is already too costly for the mixed workload, and it should be reined in rather than pushed further.
6.3 What Counts as Well-Governed
The relatively ideal state can be summed up in three points:
The analytical side shows clear but not excessive improvement, for example the RT of typical analytical SQL drops and batch time shortens.
TP and overall stability do not degrade noticeably, for example write latency and transaction tail latency stay within the business-acceptable range.
Parallel resources are not in a state of sustained queuing or overload for long stretches.
If you end up in a situation of “the report got faster, but the order database started to jitter” — where analytical tasks speed up while TP tail latency, writes, or background tasks degrade in step — that is a clear signal that the policy needs to be rolled back or re-layered, not something you can solve by continuing to fine-tune the DOP value.
7. A Concise Way to Use DOP
In a mixed-workload scenario, DOP adjustment can be driven by two judgments first.
First, judge whether it is worth parallelizing.
If the data volume is small, the query itself is short, and the bottleneck is not in a parallelizable stage such as a scan or join, then DOP should not be your first lever. Only when the data volume is large enough, the hot operator is parallelizable, and the current period has resource headroom is parallelism worth considering.
Then judge whether to keep going or stop after tuning.
Start from a conservative policy rather than cranking DOP high from the outset. Observe whether the single SQL actually got faster, and whether TP latency, PX queuing, and CPU and I/O pressure are still within an acceptable range. If the gain is limited while the system cost starts to rise, rein it in rather than keep scaling parallelism up.
For example, for a report SQL that does a wide-range scan plus aggregation, you can start with a smaller hint rather than writing a very high degree of parallelism directly:
SELECT /*+ parallel(4) */ c_nationkey, COUNT(*)
FROM customer
GROUP BY c_nationkey;
If you would rather let the optimizer decide automatically whether to parallelize, you should still set the policy on the conservative side first — for example, enable Auto DOP while limiting the parallel upper bound, to avoid many queries scaling up at the same time:
ALTER SESSION SET parallel_degree_policy = AUTO;
ALTER SESSION SET parallel_degree_limit = 4;
Whether you use a hint or Auto DOP, the first round is better aimed at “verifying whether it is worth parallelizing and whether the system can bear it,” rather than “getting the DOP to its maximum as fast as possible.”
8. Boundaries to Remember When Using DOP
Do not treat DOP as a speedup multiplier. Total elapsed time is also constrained together by serial stages, data distribution, system bottlenecks, and mixed-workload contention.
Do not assume PX is effective just because you see it. What matters is whether parallelism landed on the hot operator and whether the actual DOP reached expectation (see Section 3).
Do not treat Auto DOP as automatic governance. It can reduce manual maintenance, but it cannot replace policy control, resource isolation, and time-window management under a mixed workload (see Sections 1.3 and 5.4).
Do not look only at whether a single SQL got faster. In HTAP scenarios, TP tail latency, PX queuing, and the stability of writes and background tasks often say more about whether a policy is appropriate than the RT of a single report (see Section 6).
The signals these point to are usually issues in the execution path, statistics, resource budget, or where parallelism lands, rather than something “a few more notches of DOP can solve.”
Conclusion
In HTAP scenarios, DOP is not a speedup switch for a single SQL, but a resource-allocation choice under a mixed workload.
The question that truly matters has never been “can we add a bit more parallelism,” but: should this parallel resource go to this kind of query, should it go to this time window, and once it is given out, can transactions and background tasks still hold steady? Only when DOP is viewed within tenant-level resource control does it become a capability that remains usable over the long term, rather than turning a local optimization into a global risk.
Top comments (0)