DEV Community: Charles Wu

OceanBase Real-Time Analytics: Ecosystem Compatibility and Supporting Capabilities

Charles Wu — Fri, 24 Jul 2026 14:59:00 +0000

How to Unify Your Analytics Ecosystem Without Rebuilding Your Data Pipeline From Scratch

1. Introduction

When it comes to real-time analytics, people tend to focus first on how fast the engine runs. But in an enterprise setting, raw speed is only one piece of the puzzle. For an analytical database to truly land in production, it must integrate smoothly with the data ecosystem the enterprise has already built. Data is typically scattered across file systems, message queues, and existing operational databases, while analytical results need to flow back into reports, dashboards, and downstream applications. If every integration point requires custom adapter code to develop and maintain, the adoption cost quickly becomes prohibitive.

The traditional approach is to stitch together multiple specialized components into an analytics pipeline. Data might first go into Kafka for buffering, then through Flink for stream processing, land in Hive for historical storage, and finally be loaded into ClickHouse for querying. Each layer means another round of synchronization and transformation. The longer the pipeline, the harder it is to control end-to-end latency, and the greater the consistency risk between systems. More practically, development teams spend enormous effort on data movement and pipeline maintenance, leaving less time for actual business logic.

Therefore, real-time analytics capabilities require a complete set of supporting features, centered on three core concerns: how data comes in, how results get out after processing, and how the entire process is managed. These three concerns span the full pipeline from data ingestion through processing to consumption. OceanBase’s approach is to use a unified engine to interface with the existing ecosystem, minimizing intermediate layers rather than asking enterprises to add even more components. At the same time, OceanBase supports in-database data warehouse layering, allowing data to be progressively refined from ODS (Operational Data Store) to DWS (Data Warehouse Summary) to ADS (Application Data Store) within a single database, with Flink handling inter-layer data flow to further reduce dependence on external systems. The following sections walk through these capabilities in order of data flow.

2. Data Integration: Ingestion and Flow of Multi-Source Data

Getting data into the database is the first step of analytics. Data from different sources, at different scales, and with different freshness requirements calls for different ingestion methods. OceanBase provides capabilities across three dimensions: batch import, external tables, and real-time synchronization.

2.1 Batch Import

For scenarios that require loading large volumes of data in one shot, OceanBase provides Direct Load. Data bypasses the SQL parsing and transaction processing layers and directly generates the underlying storage files (SSTables), skipping the overhead of per-row transaction commits and delivering significantly higher efficiency for bulk writes.

This approach is well suited for initial database seeding, historical data migration, monthly archiving, and ETL batch loading. These scenarios share common characteristics: large data volumes and low requirements for per-row transaction semantics, which play directly to Direct Load’s strengths. Compared to row-by-row inserts, Direct Load processes a batch of data as a whole, reducing repeated transaction commits and log writes. The larger the data volume, the more pronounced the time savings.

Complementing Direct Load, the OBLOADER tool handles batch import and export of structured data, managing the mapping between data file formats and database table structures to make bulk loading operations simpler and more controllable.

2.2 External Tables and Data Lake Integration

Much analytical data does not reside inside the database but is stored as files in object storage or distributed file systems. The core idea behind OceanBase’s external table capability is to avoid moving data altogether — instead, it defines mappings to external files directly within the database.

In terms of file formats, external tables support the universal CSV text format as well as Parquet and ORC columnar storage formats. Columnar formats organize data by column, achieving high compression ratios and reading only the columns needed during scans, making them ideal for analytical scenarios. For storage systems, external tables are compatible with S3, OSS, and other object storage services, as well as HDFS and ODPS. For metadata management, external tables can connect to Hive Metastore (HMS) and ODPS Catalog to form a unified metadata view, and support table formats such as Hive and Iceberg.

Building on this, External Catalog can register database and table information from multiple external data sources into OceanBase in a unified manner, allowing external data to be queried directly with standard SQL without the need to manually create external tables one by one. Data already sitting in a data lake does not need to be copied into the database again, reducing both storage redundancy and the latency introduced by data movement.

2.3 Real-Time Synchronization and Data Collection

Analytics scenarios often demand data that is as fresh as possible, requiring continuous synchronization of changes from operational databases and timely ingestion of various log and event data. OceanBase supports integration with a wide range of synchronization and collection tools, covering everything from log collection to database synchronization.

For log collection, Flume and Logstash collect log data from application servers such as Nginx and Apache Tomcat, performing simple parsing and formatting before pushing data into OceanBase. This type of log data is high-volume and high-frequency, typically used for behavioral analysis, operations monitoring, and similar scenarios.

For database synchronization, OMS (OceanBase Migration Service) is OceanBase’s built-in migration and synchronization tool, supporting both full migration and incremental sync across migrations from MySQL, Oracle, PostgreSQL, PolarDB, and other databases into OceanBase. DataX is Alibaba’s open-source heterogeneous data source synchronization tool, supporting batch data synchronization across multiple database types, suitable for periodic full or incremental extraction tasks.

On the data processing side, dbt handles SQL-level ETL transformation logic, while DataWorks provides data integration and development platform capabilities on Alibaba Cloud. Kafka, as a message queue, handles buffering and distribution of streaming data and is often used in conjunction with the synchronization tools mentioned above.

The fact that these tools can connect directly means that most existing enterprise data pipelines can be reused — there is no need to build an entirely new synchronization pipeline just to integrate with OceanBase.

2.4 Flink Connector Ecosystem

In real-time data integration scenarios, Apache Flink is one of the most widely used stream processing engines today. OceanBase provides multiple official Flink Connectors covering the full pipeline from data writing to change data capture, adapting to different performance requirements and use cases.

The JDBC Connector reads and writes OceanBase through the standard JDBC protocol, supporting both MySQL and Oracle compatibility modes. It includes built-in Druid connection pool management and batch write optimization (buffering plus batch commit), along with retry mechanisms. It is suited for common scenarios such as real-time data synchronization, CDC incremental writes, and writing stream processing results to the database, making it the most versatile choice.

The DirectLoad Connector is based on the Direct Load API. Data bypasses the SQL parsing layer and writes directly to the underlying storage files. Write throughput is far higher than the JDBC approach, making it ideal for high-throughput scenarios such as large-scale data import, ETL batch processing, and historical data migration.

The OBKV HBase Connector writes to OceanBase via the OBKV HBase API, suited for HBase-compatible scenarios and wide-table storage requirements.

Flink CDC (Source) reads change data from OceanBase, supporting both MySQL and Oracle compatibility modes, and synchronizes incremental data to downstream systems. It is suited for scenarios such as syncing data out of OceanBase and data lake ingestion. At the same time, Flink CDC can also listen to change logs from external databases (MySQL, Oracle, PostgreSQL, etc.) and write incremental data into OceanBase in real time, making it one of the core channels for data ingestion.

In addition, the Flink Connector JDBC (community edition) and Apache SeaTunnel can also be used to process OceanBase data through Flink, providing more options for existing technology stacks.

In an in-database data warehouse layering architecture, Flink also takes on the role of inter-layer processing. Data flows from ODS through cleansing into DWS, then aggregates into ADS, with real-time flow between each layer handled by Flink. This way, OceanBase provides unified storage while Flink handles inter-layer computation, and together they form a lightweight real-time data warehouse solution that reduces dependence on multiple external systems.

A quick selection guide: use the JDBC Connector for regular writes; choose DirectLoad Connector for bulk data loading; use OBKV HBase Connector for HBase compatibility needs; use Flink CDC when you need to read changes from OceanBase.

2.5 Spark Connector Ecosystem

If Flink’s strength lies in real-time stream processing, Spark excels at large-scale batch processing and interactive analytics. The two are complementary: Flink handles continuous incremental computation, while Spark handles periodic full scans, complex ETL, and ad-hoc queries. OceanBase provides connectivity for both, allowing users to flexibly choose the compute engine based on the scenario.

Starting from v1.1, the OceanBase Spark Connector is deeply integrated with Spark Catalog, which is the currently recommended usage approach. Its key features include:

Zero-code access with full SQL interaction. Spark-SQL can directly read, write, and manage databases and tables in OceanBase, supporting common operations such as SHOW DATABASES, SHOW TABLES, and CTAS without writing additional code.
Adaptive partitioning and parallel reads. The Connector automatically recognizes OceanBase partition table structures and splits read tasks based on partition information, enabling multi-partition parallel scanning that fully leverages the distributed computing power of the Spark cluster.
Predicate pushdown. Filter conditions are pushed down to OceanBase for execution, reducing the amount of data transferred over the network and improving query efficiency.
Automatic schema inference. No need to manually define table structures — the Connector automatically discovers column information and type mappings for OceanBase tables.
Flexible write modes. Supports both JDBC writes and Direct Load writes. JDBC is suited for regular write scenarios, while Direct Load is designed for bulk data loading, bypassing the SQL layer and writing directly to storage for higher throughput.
Upsert support for primary key tables. In MySQL mode, this is based on the INSERT … ON DUPLICATE KEY UPDATE syntax; in Oracle mode, it is based on the MERGE syntax.
Supports both MySQL and Oracle compatibility modes simultaneously.

Typical use cases: leveraging Spark’s distributed computing power for batch analytics and ETL processing on massive data in OceanBase; syncing data from Hive or data lakes into OceanBase via Spark; completing heavy computation tasks in the Spark cluster and efficiently writing results back to OceanBase via Direct Load.

In addition to the Catalog Connector, OceanBase also provides the OBKV HBase Connector for Spark for data access in HBase-compatible scenarios. The legacy Spark Connector (for Spark 2.x) is being gradually retired; new projects are recommended to use the Catalog approach.

3. SQL Compatibility: Lowering the Barrier to Entry

Once data is in the database, how it gets queried is another critical concern. If the query syntax differs significantly from the enterprise’s existing systems, application modification and staff training both incur costs. OceanBase has invested heavily in SQL compatibility, with the goal of enabling existing applications and tools to connect with minimal changes.

3.1 MySQL Compatibility

OceanBase’s MySQL compatibility operates on two levels. At the protocol level, OceanBase communicates using the MySQL protocol, so existing MySQL clients and drivers — such as JDBC and Python connectors — can connect directly without replacement. At the syntax level, DDL, DML, and common functions are largely consistent.

The implications for upstream and downstream systems are straightforward: BI tools, ORM frameworks, and applications originally built against MySQL typically do not need code changes when switching to OceanBase. For teams already built on the MySQL ecosystem, this significantly reduces migration costs.

3.2 Oracle Compatibility

For systems that have traditionally used Oracle, OceanBase provides Oracle compatibility. Perhaps the most practically significant aspect is compatibility with PL/SQL stored procedures. Many traditional enterprises have encapsulated core business logic in Oracle stored procedures. If the database is not compatible, it means rewriting all that business logic with a new technology stack — high cost, long cycle, and significant risk.

OceanBase’s Oracle compatibility allows these stored procedures to be migrated almost as-is. SQL compatibility reaches 99.9%, application modification costs are very low, and the majority of existing stored procedures and business logic can be migrated smoothly.

3.3 Standard SQL Analytical Capabilities

Analytical queries are often more complex than online business queries, commonly involving large table joins, multi-level nested subqueries, window functions (such as ranking and sliding averages), and recursive CTE queries. OceanBase covers these commonly used standard SQL syntax features for analytics, enabling analysts to express complex statistical logic in familiar ways.

Building on this, OceanBase also provides materialized view capabilities. Materialized views pre-compute and store the results of complex queries, so subsequent queries can read directly from the materialized view instead of recomputing every time. OceanBase supports near-real-time and real-time materialized view refresh, as well as automatic SQL rewriting — when a query can match a materialized view, it is automatically routed to it. For high-frequency, repeated analytical queries, this can dramatically reduce response times.

By contrast, some big data platforms have limited support for standard SQL. When encountering complex joins or window functions, developers are often forced to use low-level APIs or hand-written UDFs, which raises the barrier and increases ongoing maintenance costs.

4. Database Management and Operations Ecosystem

Once data can flow in and be queried, the next requirement is ensuring the entire system can be managed stably. Management and operations capabilities determine the system’s long-term maintainability. OceanBase integrates with the common operations ecosystem across three dimensions: scheduling and orchestration, observability, and resource management.

4.1 Scheduling and Orchestration

A complete analytics task is rarely accomplished in a single step. It typically involves multiple stages — data extraction, cleansing and transformation, aggregation computation, and result writing — with dependencies between steps that need to be triggered in order or based on conditions. The role of a scheduling platform is to define these steps as a DAG (Directed Acyclic Graph) and execute them automatically according to a schedule.

OceanBase supports integration with mainstream scheduling and data flow management tools such as Airflow, DolphinScheduler, and NiFi. Airflow and DolphinScheduler excel at defining complex task dependencies and timed scheduling policies. Apache NiFi is a data flow management tool that specializes in visually defining data collection, routing, and transformation processes, suited for scenarios that require flexible orchestration of data flows. Once integrated with these platforms, analytics task orchestration can directly reuse the enterprise’s existing scheduling infrastructure without developing a separate scheduling logic layer.

4.2 Observability

Operations teams need to understand the system’s running state at all times, especially in multi-tenant environments where different business lines share a cluster and need to independently monitor their resource usage and query performance. OceanBase integrates with the Prometheus and Grafana monitoring stack, with Prometheus collecting metrics and Grafana handling visualization and alert configuration.

In addition to the open-source approach, OceanBase also supports integration with commercial APM platforms such as Datadog, meeting the needs of enterprises already using Datadog for unified monitoring and avoiding the need to maintain a separate monitoring system.

Monitoring coverage includes node CPU, memory, and disk I/O; query latency distributions; slow query statistics; and tenant-level resource consumption. With these metrics, issues can be quickly pinpointed when they arise, and the data also supports capacity planning and performance tuning on an ongoing basis.

4.3 Multi-Tenant Resource Management

When online transactions and analytics tasks run on the same cluster, resource contention is a common problem. If analytics queries consume too much CPU or I/O, online business may be affected. OceanBase provides multiple levels of resource isolation to handle this.

Tenants are logically independent database instances with independent resource quotas that do not interfere with each other. Foreground and background tasks are also isolated — background operations such as compaction and merging do not disrupt foreground user queries. At a finer granularity, OceanBase supports user-level and SQL-level resource groups, allowing specific users or specific SQL statements to be assigned dedicated resource groups for fine-grained control. For mixed TP/AP workloads, OceanBase supports strong isolation between TP/AP replicas — analytics queries can be routed to read-only replicas, physically separated from the primary replicas handling online transactions.

This way, multiple workload types can safely coexist on the same physical cluster without interfering with each other.

5. BI and Data Visualization

Analytical results ultimately need to be seen and used by business users, so the ability to connect seamlessly with BI tools directly determines the practical value of analytics capabilities. OceanBase is compatible with mainstream platforms including Superset, Grafana, Tableau, Power BI, QuickBI, and GuanData, allowing analytical results to be displayed directly in these tools. Superset and Grafana are open-source options suited for flexible customization; Tableau and Power BI are enterprise-grade business intelligence tools with rich capabilities in data modeling and interactive exploration.

The significance of self-service analytics is that business users can drag and drop fields, set filter conditions, and build reports on their own, without submitting every request to the data development team and waiting for scheduling. This shortens the cycle from asking a question to seeing the data, enabling people who understand the business to validate ideas directly, and extending analytics capabilities from the development team to the business team.

In terms of real-world results, one international retail brand previously used a combination of ADB, Deltalake, and ClickHouse, with different technology stacks for ETL and serving, resulting in lengthy integration cycles and difficult maintenance. After migrating to OceanBase, a single cluster provides both ETL and serving capabilities with MySQL compatibility, reducing overall database costs by more than 40%. Another example is a provincial mobile carrier that used external tables to read CSV, ORC, and other files directly from HDFS, then used Direct Load for real-time import, achieving a 69% overall performance improvement over the existing system.

6. AI/ML Integration

With the proliferation of large language models and AI applications, databases serve not only as the foundation for traditional analytics but are increasingly taking on data storage and retrieval needs for AI applications. Through standard interfaces and vector search capabilities, OceanBase can integrate with mainstream AI/ML ecosystem tools.

Python is the most widely used programming language in the AI/ML space. Because OceanBase is compatible with the MySQL protocol, developers can connect directly to OceanBase using common drivers such as pymysql and SQLAlchemy, performing data reads, writes, and analysis in Python environments without an additional adaptation layer.

In LLM application development, LangChain and LlamaIndex are the currently dominant frameworks. They support using OceanBase as a vector storage backend for storing text embeddings and providing similarity search capabilities. Combined with model services such as OpenAI, enterprises can build RAG (Retrieval-Augmented Generation) applications on OceanBase, enabling large models to retrieve relevant context from the database when answering questions, improving both accuracy and timeliness. OceanBase’s vector search capabilities allow structured data queries and semantic retrieval to be completed within the same database, eliminating the need to deploy a separate vector database for AI applications.

7. Data Governance

Data quality is the prerequisite for trustworthy analytical results. If source data has missing values, duplicates, or format anomalies, downstream analysis and decision-making will be affected. OceanBase is compatible with mainstream data quality governance tools, enabling enterprises to perform quality control at every stage of data ingestion, processing, and output.

Soda is a data quality monitoring tool that supports defining data quality rules (such as field non-null rates, numerical ranges, uniqueness constraints, etc.) and periodically checking data against these rules, alerting when anomalies are detected. Great Expectations is a data validation framework in the Python ecosystem, used to define and verify expected data characteristics, well suited for integration into ETL pipelines for automated validation. Data Profiler focuses on data profiling, helping understand data distributions, outliers, and statistical features, which is especially useful during initial data onboarding. These tools connect to OceanBase through standard database connections, allowing enterprises to embed data quality checks into existing data pipelines without building a separate governance system.

8. Summary

The core of ecosystem support is making data flow more smoothly, rather than forcing enterprises to adapt to the database. OceanBase’s approach is to use a unified engine to interface with the existing data ecosystem — from data ingestion, layered processing, query analytics, and visualization through to AI applications and data governance — minimizing the glue code and synchronization pipelines between systems, so that data can come in, be processed quickly, be seen clearly, and be managed effectively.

Compared to the traditional approach of stitching together multiple components, this reduces pipeline complexity and lowers the latency and consistency risks that come with overly long pipelines, making it easier for real-time analytics capabilities to truly land in enterprise production environments.

A Guide to AP Query Diagnosis and Tuning

Charles Wu — Wed, 22 Jul 2026 14:59:00 +0000

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.

Governing DOP Under Mixed Workloads

Charles Wu — Tue, 21 Jul 2026 14:59:00 +0000

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.

How Direct Load Delivers Strong Transactions and High Concurrency for Real-Time Analytics

Charles Wu — Wed, 15 Jul 2026 14:59:00 +0000

How to Load Data in Bulk — Keeping Writes Fast, Transactions Safe, and Analytics Instant

In scenarios such as e-commerce flash sales, financial risk control, and telecom network big data, businesses now expect data freshness measured not in hours or minutes but in seconds or even less. The value of data for decision-making decays quickly once it is delayed — an inventory update that arrives a minute late can lead to overselling, and a risk-control signal that lags by a few seconds may only fire after a fraudulent transaction has already gone through. Whether data can join analytical queries the moment it is written directly determines how timely and accurate business decisions can be.

This places demanding requirements on a data platform’s ingestion capabilities: it must not only write large batches of data into the system quickly, but also make that data queryable immediately after the write completes, all without disrupting the online workloads already running. Traditional approaches tend to trade one of these dimensions off against another. This article looks at OceanBase’s direct load mechanism and how it balances high-throughput writes, strong transactional guarantees, and coexistence of multiple workloads.

Ingestion Challenges in Real-Time Analytics

To serve both writes and analytics, many teams adopt multi-system architectures like Lambda or Kappa, where data has to travel through components such as Kafka, Flink, Hudi, Hive, and ClickHouse, with each stage involving ingestion, synchronization, transformation, and governance. The longer the pipeline, the more latency accumulates, and the harder it becomes to keep data consistent across systems — making write-then-analyze difficult to achieve in practice. Traditional batch-oriented ETL adds T+1 latency on top of this, so analytical results inherently lag behind the events they describe.

Turning to ingestion itself, traditional ETL and standard SQL write paths have a clear throughput bottleneck. Every write has to go through SQL-layer parsing, transaction lock contention, and the synchronization cost of Redo log. When the data volume reaches millions or tens of millions of rows, these accumulated per-row costs substantially slow down ingestion and fall short of the write-then-analyze goal.

In HTAP mixed-workload environments, the problem grows more complex. On one hand, large-batch ingestion must not block the online transactions already running — TP and AP workloads need to coexist. On the other hand, data consistency cannot be compromised: written data must satisfy ACID guarantees and become visible to analytical queries immediately after commit.

Finding the balance between high-throughput ingestion and online transactions is a question every real-time analytics platform has to answer.

The Core Mechanism of Direct Load

To understand what direct load optimizes, it helps to split a single ingestion into two stages:

Stage one: data reaches the storage layer. Data goes through SQL parsing, execution plan generation by the optimizer, and reading and scheduling by the executor before finally arriving at the storage layer.
Stage two: data is written into the storage layer. Data is actually persisted at the storage layer. The standard path writes data into the MemTable, whereas direct load builds SSTables directly.

The core optimization of direct load lives in the second stage: it bypasses the MemTable, sorts data by primary key, and builds SSTables directly to insert into the storage layer. This is the most fundamental difference from the standard write path.

The Write Path

The overall write path can be summarized as:

Raw data → type conversion → sort by primary key → build SSTable directly → insert into storage layer

Compared with the standard path of writing into the MemTable row by row, building SSTables directly offers the following advantages:

Substantially lower lock overhead: a table lock is taken only once at the start of ingestion; there is no row-lock contention during the write, and multiple threads build in parallel without interfering with one another.
More efficient conflict detection: tables without a primary key can skip conflict detection entirely, while tables with a primary key scan the existing data at most once.
Less log volume: instead of logging each row individually, the encoded macroblock data is written to the log as a whole.
No stalls from a full MemTable: because the MemTable is not involved, there is no scenario where a full MemTable must be frozen and flushed, and no risk of writes stalling because background compaction cannot keep up.
Data is persisted directly in the storage engine’s final format (SSTable), skipping the multi-round compaction chain from MemTable to Mini SSTable to Major SSTable.

Full Load and Incremental Load

Depending on where data is written, direct load comes in two forms: full direct load and incremental direct load. The difference lies in which layer of the LSM-Tree the data ultimately lands in.

Full direct load: sorts the incoming data by primary key, merges it with existing data, and writes it directly into the baseline Major SSTable. It suits empty tables or tables with a small data volume.
Incremental direct load: sorts the incoming data by primary key and writes it directly into the Mini SSTable of the incremental layer. It suits tables that already hold a large data volume, avoiding a full rewrite of the baseline.

Both approaches bypass the layered structure of the LSM-Tree — such as the complex BTree structure within the MemTable — and instead generate SSTable files directly through efficient sorting, significantly improving the speed of bulk data writes.

Integration with the LSM-Tree Storage Engine

OceanBase’s storage engine is built on an LSM-Tree architecture, in which data is naturally divided into two layers:

Incremental data: newly written user data lands quickly in the MemTable in row-store form, and is later flushed into Mini SSTable, Minor SSTable, and so on. Incremental data is maintained independently by each replica and contains all multi-version information.
Baseline data: generated as Major SSTables through periodic or adaptive Major Compaction. Under the same version number, the baseline data across all replicas is physically consistent, and in AP scenarios it can be configured in column-store mode to serve analytical queries directly.

Baseline compaction here uses a daily-compaction mechanism: a tenant periodically, or based on user action, selects a global version number, and all replicas complete a round of Major Compaction at that version number, ultimately producing baseline data at the same version. Built on distributed multi-replica foundations, the baseline data across all replicas is physically consistent under the same version number, providing a stable and reliable read foundation for analytical queries.

Both direct load paths fit naturally into this incremental-baseline two-layer structure. Full load lands directly in the column-store baseline Major SSTable, while incremental load first lands in the Mini SSTable of the incremental layer and is later incorporated into the column-store baseline through the normal compaction flow. Whichever path is taken, the entire process requires no extra data movement or format-conversion pipeline.

After Ingestion: Column Store and the Vectorized Engine Accelerate Analytics

Direct load solves the throughput problem of getting data into the database; once the data enters the column-store baseline, analytical query performance is further improved by the vectorized execution engine. In column-store mode, each column is stored as a separate SSTable, and the SSTables of all columns combine into a virtual SSTable that serves as the column-store baseline data. This layout lets analytical queries read only the columns involved, reducing scans of irrelevant data.

Building on this, the vectorized engine processes data in batches rather than iterating row by row, significantly reducing iteration overhead, using the CPU cache more efficiently, and parallelizing computation with SIMD instructions. In public benchmarks, the OceanBase vectorized engine, compared with the previous version, improves ClickBench by about 14x, TPC-H 100G by about 40x, and TPC-DS 100G by about 26.5x. Together, direct load, the column-store baseline, and vectorized queries form a coherent path from write to analysis.

Full Database Feature Support

While improving write throughput, direct load does not sacrifice existing database functionality. Indexes, constraints, partitions, transactions, and a wide range of data types are all supported during ingestion.

Indexes and constraints

Primary keys, global indexes, and local indexes are built automatically and in parallel during ingestion based on the PX framework.
Uniqueness and not-null constraints are validated in real time during ingestion.
Conflict handling supports strategies such as IGNORE and REPLACE.

Partitioned table support

Supports partitioning strategies such as Range, Hash, and List.
Compatible with multi-level partitioning.
Partition-level parallel ingestion, leveraging the strengths of the distributed architecture.
Supports specifying partitions to import, loading only specific partitions.

Transactional consistency

Data is not externally visible during ingestion.
Automatic rollback on ingestion failure.

Data type support

All standard SQL data types.
LOB large objects (CLOB/BLOB).
Extended types such as JSON and GIS (continually being improved).

Data Sources and Formats

In real-world business, the data to be ingested often comes from a variety of heterogeneous systems. Combined with its external table capability, OceanBase direct load supports efficient ingestion directly from many data sources:

File formats: common analytical data formats such as CSV, JSON, Parquet, and ORC.
Storage sources: local disk, OSS, S3, HDFS, NFS, COS, OBS, ODPS, and more.
Catalog integration: Hive Metastore, ODPS Catalog, and the Iceberg table format.
Character sets: multiple character sets such as UTF-8 and GBK.
Compression formats: gzip, snappy, lz4, zstd, and others.

Data can be read directly from external storage systems and written into OceanBase via direct load, eliminating the multi-step chain of staging, cleansing, and reloading found in traditional approaches.

Ingestion Methods

Direct load offers several methods, so DBAs, data engineers, and application developers can each choose the one they prefer.

SQL support is native and compatible with MySQL’s LOAD DATA syntax, so a single command does the job:

-- Import from a local file
LOAD DATA /* direct(true, 0) parallel(32) */ INFILE 'orders.csv' INTO TABLE orders;

-- Import from remote storage (OSS/S3 supported)
LOAD DATA /* direct(true, 0) parallel(32) */ INFILE 'oss://bucket/path/data.csv' INTO TABLE orders;

INSERT SELECT, combined with internal or external tables, can perform ETL entirely within OceanBase or import directly from external files:

-- ETL with OceanBase INSERT SELECT
INSERT /* direct(true, 0) enable_parallel_dml parallel(32) */ INTO summary_table 
SELECT * FROM detail_table 
WHERE date >= '2026-01-01';

-- Import data via an external table with OceanBase INSERT SELECT
INSERT /* direct(true, 0) enable_parallel_dml parallel(32) */ INTO detail_table 
SELECT * FROM FILES(location = 's3://bucket/path', type = 'parquet', pattern='*');

For scenarios that require loading files in bulk from the command line, you can use the OB Loader tool:

obloader -h 127.0.0.1 -P 2881 \
         -u user@tenant#cluster -p password \
         --table=logs \
         --direct-load \
         --file-path=/data/logs/

In addition, in KV scenarios, batch writes through the OceanBase Table API can also take the direct load path. These integration methods work out of the box, with no extra components or middleware required.

High-Concurrency Guarantees: How Direct Load Coexists with Online Workloads

Once transactional correctness is settled, another key question is: how can large-batch ingestion avoid affecting the online workloads already running? OceanBase addresses this challenge through multi-layer resource isolation and an elastic architecture.

A Multi-Layer Resource Isolation System

OceanBase provides multi-level resource isolation, from coarse-grained to fine-grained:

Isolation across tenants: ingestion tasks for different business lines run in different tenants, with independent resource quotas and no mutual interference.
Isolation between foreground and background tasks: direct load can run as a background task, with the system setting upper limits on its CPU and I/O priority so that the response time of foreground online transactions is not affected.
User-level and SQL-level isolation: through the Resource Group mechanism, administrators can set resource quotas for specific users or specific types of SQL, preventing a single large ingestion task from crowding out the compute resources of other workloads.

Strong Isolation Between TP and AP Replicas

In mixed-workload scenarios, OceanBase supports routing ingestion traffic and analytical queries to different replicas:

Write replicas take on the data write traffic of direct load.
Read replicas (read-only replicas) take on concurrent analytical query requests.

The two workloads are isolated at the physical level; what they share is the same strongly consistent copy of the data, not the same compute resources.

Elastic Scaling and Automatic Load Balancing

To handle ingestion peaks — such as data warm-up before a flash sale or end-of-day batch loading — OceanBase offers flexible scaling options:

Both integrated and disaggregated storage-compute forms: enterprises can choose the architecture that fits their deployment environment. In the storage-compute disaggregated mode, compute nodes can scale independently without affecting data storage.
Fast scaling in and out: within a single Zone, compute nodes can be added or removed in minutes to handle traffic spikes.
Built-in dynamic data balancing: after scaling out, the system automatically redistributes the data load across nodes, avoiding hot shards or bottleneck nodes.

Summary

Direct load, strong transactional guarantees, and resource isolation work together to form a complete capability loop for OceanBase in real-time analytics scenarios:

Direct load solves the throughput problem of loading large batches of data at high speed, and adapts to different data scales through its full and incremental paths.
Full database feature support keeps indexes, constraints, partitions, and a variety of data types usable even during high-speed ingestion.
Diverse integration methods cover native SQL syntax, OB Loader, and the Table API, letting different roles integrate conveniently.
Multi-Paxos + MVCC + WAL ensure the consistency and durability of ingested data, so transactional semantics are not sacrificed in the pursuit of speed.
Multi-layer resource isolation and replica routing ensure that ingestion does not disrupt the normal operation of online workloads.

From write to query, this set of capabilities forms a clear division of labor: direct load handles high-speed ingestion, the column-store baseline and vectorized engine accelerate queries, materialized views handle precomputation, and resource isolation ensures that multiple workloads coexist in the same cluster. Working together, they let enterprises perform high-throughput writes and real-time analytical queries within a single system, without maintaining multiple technology stacks and data-synchronization pipelines. Data becomes queryable the moment it enters the system, achieving a smooth transition from write to analysis.

The Scan Optimization Boundaries of OceanBase Skip Index in HTAP

Charles Wu — Tue, 14 Jul 2026 14:59:00 +0000

When to Enable Skip Index — Cutting Wasted I/O in Mixed Workloads Without Extra Maintenance Pain

When transactions and reporting share the same OceanBase cluster, a drop in analytical query performance is usually first investigated from the angles of SQL, B+ tree indexes, and parallelism. Even when partition pruning is already in effect in the execution plan and the scan range has converged to a reasonable interval, a query may still read large amounts of data within a partition. This drives up response times, increases CPU and I/O overhead, and — during business peaks — affects short transactions and background compaction on the same node.

These problems are often not simply a matter of a table being too large or a missing index. More commonly, the scan phase reads in a large amount of data, only for part of it to be filtered out later. To arrive at the final matching results, the system still has to read all of that data first. This cost is what we call wasted scanning.

Skip Index targets exactly this problem. It is column-level pre-aggregated data written into the intermediate micro-block index layer of the SSTable — commonly MIN_MAX and NULL_COUNT, and in some scenarios SUM as well. Once a filter condition is pushed down to the storage layer, the engine uses these statistics to decide whether a data block still needs to be read: if a block cannot possibly contain the target rows, it skips reading and decoding that block, thereby reducing wasted I/O.

That said, Skip Index mainly addresses wasted reads in scan-oriented workloads, not point-lookup positioning — and it is not a universal capability that “speeds things up whenever there’s a WHERE clause." Under mixed workloads, its more important value lies in letting analytical queries consume less shared I/O and CPU, rather than simply making a single SQL statement faster.

Why Wasted Scanning Still Happens

As described above, wasted scanning refers to the I/O and decoding cost incurred by data that is read during the scan phase but later excluded by filtering. This cost does not automatically disappear just because routine optimizations have already been applied.

HTAP queries in OceanBase typically read both the merged baseline data and the newer incremental data at the same time. The columnar path can read only the columns involved in the query, and B+ tree indexes can narrow the row-positioning range for point lookups — but the two operate at different levels: the former reduces column-level reads, while the latter avoids entering a large-range scan. Once a query has already entered the scan phase, it may still read in large amounts of data at the block granularity that ends up being filtered out. When the filter conditions themselves cannot exclude many data blocks in advance, the system may still pay the cost of reading and decoding many blocks that will ultimately be filtered away.

If the problem stems from an oversized table, a missing suitable index, or a poorly designed partition key, then the focus of optimization usually does not lie at the Skip Index layer.

Skip Index is better suited to scenarios like this: the execution plan has already landed on a large-range scan within a partition (row store or column store both work), and the real bottleneck genuinely comes from “reading a lot but keeping little.” In a cluster where transactions and analytics share resources, this kind of waste does not always first show up as one particular SQL statement being slow. More often, analytical tasks continuously occupy shared resources and, during business peaks, affect short transactions and background compaction on the same node.

Where Skip Index Fits in the Scan Optimization Chain

Wasted scanning happens at the data-block granularity. To judge whether Skip Index applies, you first need to be clear about which layer it and other scan-related capabilities each operate on.

Skip Index and B+ tree indexes do not solve the same kind of problem. B+ tree indexes are good for quickly positioning a small number of target rows; Skip Index targets a different scenario: the query has already entered the scan phase, but the system still wants to read as few of the ultimately non-matching data blocks as possible. It does not turn a scan into a point lookup, but it can reduce unnecessary disk reads during the scan.

The division of responsibilities among several common capabilities is as follows: partition pruning narrows the range of partitions to be scanned; column store decides which columns to read and which columnar scan path to use; B+ tree indexes handle row positioning for point lookups and high-selectivity access; and only if a large number of data blocks still need to be read within the scan range after the above steps does Skip Index become applicable. When the preceding optimizations have not resolved the corresponding problem, or when filters cannot be pushed down and the query is mostly point lookups, enabling Skip Index alone usually brings little noticeable improvement. Only when the execution plan has already landed on a large-range scan (row store or column store) and the bottleneck manifests as a high scan-read volume with a low post-filter retention ratio does it enter the scope of Skip Index evaluation.

In scenarios such as hybrid row-column storage or a row-store primary table paired with columnar indexes, having Skip Index configured on a table does not mean every query will use it. The filter conditions must be pushable down to the storage layer, and the query must ultimately adopt a scan method that can leverage block-level statistics. The baseline side of a column-store table usually already carries default MIN_MAX statistics, which may not necessarily be shown in DESC when created by default; a row-store table requires explicitly declaring SKIP_INDEX(...) on the column. This brings additional storage and compaction maintenance overhead, but in low-selectivity filter and aggregate-pushdown scenarios it can likewise significantly reduce wasted I/O.

Why Skip Index Can Reduce Scanning

Skip Index works by relying on the block-level statistics maintained during the write and compaction processes. For a specified column, the system records statistics such as the minimum value, maximum value, and null count in the intermediate index layer of the baseline data.

How These Statistics Are Used During Query Execution

After a filter condition is pushed down to the storage layer, the engine — before prefetching a data micro-block — first uses these statistics to judge whether the block still needs to be read. If, judging by its value range, a data block cannot possibly satisfy the condition, it can directly skip the disk read and decoding; the remaining data blocks are still read normally and confirmed again by the subsequent filter, so no target rows are missed.

In certain aggregate queries, the system can also leverage the additionally maintained aggregate values to further reduce access to data micro-blocks.

Which Scenarios Show the Effect More Readily

Column-store tables: The scan data volume is usually larger, so the room for skipping is often greater as well; the baseline side typically already carries MIN_MAX statistics by default.
Row-store tables: In large-range scans with filters, the index layer can first judge whether a data block might contain the target rows before deciding whether to read the data micro-block. After explicitly declaring SKIP_INDEX(...) on a few key columns, wasted I/O can likewise be noticeably reduced in low-selectivity filter and aggregate-pushdown scenarios.
Aggregate queries: SUM mainly provides additional optimization room in aggregation scenarios. If the business genuinely depends on SUM aggregate pushdown, you can create it explicitly and then decide whether to keep it based on actual conditions. Column-store tables do not create SUM-type statistics by default, partly because it adds to the burden of direct load and compaction tasks.

The core role of Skip Index is to read fewer of the ultimately non-matching data blocks during the scan phase, thereby reducing wasted scanning and freeing up I/O and decoding resources. In HTAP scenarios, these statistics are built primarily on the baseline data; the portion that has already been compacted can participate in query acceleration first.

How to Evaluate Whether It Is Worth Enabling in HTAP

In a pure analytical environment, the main role of Skip Index is to reduce resource consumption during scanning; in HTAP scenarios, you have to look one level deeper: are analytical queries still heavily consuming the I/O and CPU that should be left for short transactions and background compaction? Every data block not read means giving back a portion of I/O and decoding capacity to the shared resource pool. Therefore, in HTAP scenarios, whether to enable Skip Index should not be judged solely by whether the analytical query itself gets faster — you also need to consider whether the resources it saves are enough to offset the extra storage overhead, the compaction maintenance overhead, and the impact on transactions and background tasks.

First, Check Whether the Query Path Matches

Skip Index is worth considering only when the following conditions are met:

The execution plan is dominated by large-range scans, in either column store or row store; partition pruning has already converged the scan range to a reasonable interval, yet large amounts of data still need to be read within the partition.
The filter fields are stable over the long term, and the filter conditions can be pushed down to the storage layer.
The data distribution allows a substantial portion of data blocks to be excluded in advance — for example, reporting-style queries filtered by low-selectivity conditions such as time or status.
For column-store tables, first confirm whether the default MIN_MAX covers the commonly used filter columns; for row-store tables, it is more appropriate to explicitly declare SKIP_INDEX only on a few key columns, confirm that there is genuinely significant room for skipping, and then consider whether SUM is needed—avoiding redundant configuration across too many columns.

If the query is mostly point lookups, or the filter conditions cover almost all data blocks, you should generally prioritize investigating partition design, SQL phrasing, or B+ tree indexes first. If these problems that should be solved first have not yet been resolved, Skip Index usually cannot make up for that layer.

Which Scenarios Are Usually Suitable for Enabling It

Skip Index is better suited to analytical queries that have already entered the scan phase but where a substantial portion of data blocks still does not need to be read — not to every query with a WHERE clause. Common situations include:

The execution plan has already landed on a large-range scan within a partition, with the bottleneck manifesting as a high scan-read volume and a low post-filter data retention ratio.
The filter conditions themselves cannot precisely position a small number of rows, but they can let a portion of data blocks be excluded in advance via block-level statistics.
The filter fields are stable and appear over the long term in analytical workloads such as reporting and batch jobs.
A few key columns have already been identified on a row-store table, and these columns have significant room for skipping.

Which Scenarios Usually Show Limited Effect

In the following scenarios, Skip Index often struggles to bring noticeable improvement:

The table itself is small, or analytical queries are rare and the workload is mostly point lookups.
The filter conditions require scanning almost all data blocks. In this case, even with Skip Index, most blocks cannot be excluded in advance, so the disk reads and decoding that can be saved are limited.
The filter fields used by queries change frequently, making it hard to consistently hit the columns maintained by Skip Index.
The data is dominated by random updates. When data within a certain range is frequently updated at random, the reference value of the statistics declines and fewer data blocks can be skipped, yet the maintenance overhead does not disappear accordingly.
When preceding optimizations such as partition pruning, B+ tree indexes, or scan paths have not been done well, enabling Skip Index alone often yields little.

Applicability Boundaries and Additional Costs

When deciding whether to enable it, you also need to factor in the additional costs and the pace at which it takes effect:

The system must pay extra storage for these statistics, and they also need ongoing maintenance during compaction.
Skip Index does not take effect across the entire table immediately upon configuration. After an attribute change, it requires progressive compaction to gradually rewrite the intermediate index layer and build statistics on the baseline side step by step; during this process, the portion that has already taken effect can participate in queries first. When judging whether it is effective, you should not rely solely on results from the launch day or before compaction is complete.
It cannot replace partition pruning, B+ tree indexes, or appropriate scan path selection.
Before adding Skip Index to a column, first confirm that it genuinely corresponds to a stable query scenario; an oversized Skip Index on a single table can also raise errors.
If the business strongly depends on the latest data that has not yet been compacted into the baseline, it is not appropriate to judge whether Skip Index is effective based on this data alone.

How to Tell Whether It Is Working After Enabling It

A common approach is to first identify stable workloads, configure Skip Index on only a few columns, wait for compaction to progress for a while, and then decide whether to keep it based on query performance and system metrics. Focus on the following points:

After Skip Index is specified for a column, statistics are gradually built during compaction; you should compare the scan volume and response time of typical analytical SQL after compaction has progressed.
The Skip Index-related storage statistics in execution plan monitoring — for example, the number of data blocks skipped in advance by Skip Index — can be used to confirm whether skipping actually happens.
During business peaks, observe whether short transactions are still noticeably dragged down by analytical queries.

Therefore, judging whether Skip Index is effective is better done after statistics have gradually been built on the baseline side, combined with actual query performance.

Don’t Look Only at the Response Time of a Single SQL

In HTAP scenarios, a drop in the response time of a single SQL statement does not mean the tenant as a whole is more stable. When deciding whether Skip Index is worth keeping, you also need to consider the compaction burden, storage footprint, and peak-time resource contention together:

If analytical queries improve slightly but compaction slows down, or online transaction latency shows noticeable fluctuations, it may not be worth it overall.
If a single SQL statement gets faster but the cluster still shows noticeable fluctuations during peaks, it often means the assessment still lacks observation of resource usage and contention.
Beyond the response time of a single SQL statement, you usually also need to look at I/O, CPU, and background task performance at the same time.

Summary

Under the LSM-Tree architecture, OceanBase unifies row store, column store, and transactional capabilities: column store mainly answers “which columns to read,” while Skip Index goes a step further on both row and column stores to answer “which data blocks can be skipped.” Discussing it separately in HTAP scenarios is warranted because the extra cost of wasted scanning spills over to short transactions and compaction tasks; and given that the execution plan is already reasonable, further reducing wasted reads in analytical queries at the storage layer remains an optimization worth considering.

Skip Index is not a universal acceleration capability, but within clearly defined applicability boundaries, it remains an option in mixed-workload governance — one that can be rolled back through configuration and is genuinely worth carefully evaluating before adoption.

When Agent Skills Start Accessing Memory: A Technical Path with MoonBit and Wasm

Charles Wu — Thu, 09 Jul 2026 13:01:43 +0000

Agent memory doesn’t fail on storage. It fails when Skills can read and write without boundaries.

For a while now, we’ve been circling the same question inside seekdb (OceanBase’s AI-oriented database) and PowerMem (OceanBase’s memory management layer for agents):

How do you give an AI agent memory that actually works over the long run?

An agent that starts from zero every conversation is a poor long-term worker. It needs to remember user preferences, project context, past decisions, and lessons from tool use — including what worked and what didn’t.

Early agent memory setups were usually simple: local files, Markdown logs, a workspace folder, or whatever still fit in the current context window.

That’s fine for experiments. But once an agent is used day after day, the cracks show up fast. How do you sync memory when you switch devices? How do you carry experience across workspaces? How do teams share what their agents learned? And how do you separate user-private memory, project memory, and team memory?

There’s a deeper issue, too: memory isn’t solved by “store everything.”

If an agent just piles up chats, logs, and task traces, it doesn’t automatically get smarter. Often the opposite happens — you end up with more memory and less ability to find what matters when it matters.

So the hard part isn’t persistence. It’s ongoing organization, precise retrieval, and safe reuse.

That’s the problem seekdb and PowerMem are built around.

seekdb focuses on the data and retrieval layer underneath memory — combining structured data, full-text search, and vector search so an agent can pull back what’s relevant to the current task instead of shoveling history back into context.

PowerMem sits closer to a Memory Management Layer for agents. It isn’t only about storage. It’s about the full lifecycle: what to extract, what to keep, what’s stale, what to merge, and what’s worth reusing in future tasks.

User preferences, project decisions, bug-hunt notes, team workflows, tool habits — these shouldn’t die in a single chat. They should graduate from ephemeral context into durable memory the agent can use later.

At larger scale, in production environments that demand higher reliability, OceanBase (a distributed SQL database) can also carry long-term memory with stronger operational guarantees.

In short: seekdb, PowerMem, and OceanBase address the data plane, retrieval, and lifecycle of agent memory.

But once memory is real inside an agent runtime, a new class of problems appears.

After memory is a service, Skills become the new boundary

In an agent runtime such as OpenClaw — where agents load Skills, call tools, and run tasks — memory isn’t just an internal history blob. Third-party Skills may want access to it too.

One Skill might need project history. Another might summarize user preferences. Another might write new lessons back into memory. Others might combine memory with external tools.

That raises very practical questions:

Can a third-party Skill read user-level memory?
Can it write workspace memory?
Can it touch team-shared memory?
Can it obtain database URLs, API keys, or direct connection details to the memory service?

Without clear answers, “memory as a service” can increase risk instead of reducing it.

Long-term memory may hold preferences, project history, team decisions, business context — and also code snippets, configs, error logs, and execution notes. Once that material becomes a durable asset, it can’t be freely read or modified by every Skill.

From an infrastructure perspective, the question isn’t only:

How do we store and retrieve memory?

It’s also:

How do we distribute, execute, and authorize third-party Skills that access memory?

The problem isn’t who can call an API

Technically, calling a memory API is easy.

JavaScript works. Python works. Go works. Rust works. MoonBit (a statically typed language with multi-backend compilation, including WebAssembly) works too.

The real issue isn’t which language can send HTTP. It’s how the runtime enforces boundaries when any third-party Skill can reach memory.

If a Skill holds a PowerMem API key and connects directly, its blast radius is hard to control. It might query memory it shouldn’t see, write where it shouldn’t write, exfiltrate results over the network, or bypass runtime audit — expanding access without the user understanding what happened.

A better model: Skills don’t connect to memory services directly. They request controlled capabilities through the runtime.

For example:

memory.search — retrieve memory within an authorized scope
memory.add — write new memory
memory.update — update existing memory
tool.call — invoke external tools on an allowlist
log.emit — emit controlled logs

In this model, a Skill doesn’t know how to reach PowerMem, seekdb, or OceanBase, and it doesn’t hold API keys. It only knows which capabilities it has been granted.

The runtime performs the actual access.

It checks user, agent, workspace, scope, and Skill permissions; allows or denies the call; and when needed, trims, redacts, or blocks results — while writing an audit trail.

Memory stops being a global resource any Skill can read or write. It becomes a set of capabilities the runtime explicitly grants.

That’s the shift from Memory API to Memory Capability.

An API is an interface.

A capability is something granted — scoped, auditable, and revocable.

Why we started looking at MoonBit + Wasm

A quick intro to MoonBit: it’s a modern, statically typed language aimed at engineering ergonomics and multi-target compilation. It can compile to WebAssembly (Wasm) — a portable binary instruction format — and is a good fit for packaging small, well-defined units of logic.

For agent Skills, we care about MoonBit less as “yet another language” and more as a way to make Skills easier to govern.

A third-party Skill shouldn’t be a one-off script. Over time it may be distributed, installed, reviewed, and granted access to memory, tools, the filesystem, or external services. It should declare: what inputs it accepts, what it returns, what errors it can surface, which permissions it needs, and which memory scopes it touches.

If those boundaries live only in dynamic JSON, prompt conventions, or developer discipline, you can ship quickly — and struggle to govern later. MoonBit’s static typing and compile-time checks can make boundaries explicit before runtime.

Compiled to Wasm, a Skill can run as a lightweight, sandbox-friendly module. It still doesn’t get API keys or direct connections to PowerMem, seekdb, or OceanBase. When it needs memory, it goes through runtime capabilities — same as before.

MoonBit → clearer, more engineering-friendly Skill definitions
Wasm → a module shape that sandboxes well
Runtime → real authorization, audit, and enforcement

Today, many Skills are written in JavaScript or Python. Mature ecosystems, fast iteration — great for proving ideas.

But an open third-party Skill marketplace pays a governance tax: runtimes to install, version skew, long dependency chains, harder supply-chain review.

Native binaries are the other pole. Go, Rust, Zig, C/C++ can produce clean deploy artifacts — excellent for high-trust infrastructure: runtimes, CLIs, daemons, plugin managers, API gateways.

If every third-party Skill shipped as a native binary, though, it behaves more like a normal host process: filesystem, network, environment variables, subprocesses, direct DB connections. Audit and trust costs go up.

We wanted something between scripts and native binaries:

Light to distribute
Clear execution boundaries
Small default privileges
External power only when the runtime grants it

That’s why MoonBit + Wasm landed on our radar.

Wasm is often introduced as “fast code in the browser.” Fair — but on the server, edge, and plugin side, the interesting property is sandboxed execution.

Without explicit host capabilities, a Wasm module doesn’t get the filesystem, network, environment, or subprocesses by default. What it can do depends on what the runtime exports.

That maps cleanly to third-party Skills.

The platform doesn’t run a full-privilege host program. It loads a module in a sandbox: inputs in, computation, results out. Memory, tools, and external services require explicit capabilities.

MoonBit + Wasm doesn’t magically solve security by itself.

Permission checks, audit, redaction, and revocation still belong to the runtime. Wasm supplies a sandbox-friendly module; MoonBit supplies clearer types and boundaries.

Together, they make it easier to fold third-party Skills into capability-based memory access.

From memory to Skills — the problem leads here naturally

Back to the starting question: how do you make agent memory truly durable?

Step one: memory can’t live only in context or local files. It must persist, be searchable, migrate, and reuse.

That’s seekdb, PowerMem, and OceanBase.

Step two: once memory lives inside a runtime like OpenClaw and third-party Skills consume it, you need answers to:

Who can access memory?
What scope — user, workspace, team?
Write access — or read-only?
Can results leave the trust boundary?
Can you audit what happened?

Memory shouldn’t be a casually callable API. It should be a runtime-granted capability.

In that picture:

seekdb — retrieval substrate (structured + full-text + vector)
PowerMem — extract, manage, update, reuse
OceanBase — production-grade persistence at scale
MoonBit + Wasm — Skills as bounded, distributable, sandboxed units that access memory only through the runtime

This isn’t “bolt seekdb onto MoonBit” for its own sake.

It’s what happens when agent memory becomes real in production runtimes: Skill execution and authorization become the next layer.

MoonBit + Wasm is one serious path on that layer.

References

PowerMem: https://github.com/oceanbase/powermem
seekdb: https://github.com/oceanbase/seekdb
MoonBit: https://www.moonbitlang.com/

Speeding Up Columnar Queries in OceanBase: Solving the Real-Time Update Bottleneck with Delete_Insert Table Mode

Charles Wu — Thu, 09 Jul 2026 12:22:13 +0000

When to trade write cost for scan stability — choosing Delete_Insert mode for update-heavy columnar tables

Columnar tables are a proven win for bulk scans and analytical aggregation. But the moment a workload involves frequent updates, query performance tends to drift downward as incremental data piles up. OceanBase’s Delete_Insert table mode tackles this by splitting every update into two complete rows at write time, one that deletes the old version and one that inserts the new. The query path no longer has to merge row by row, so scan efficiency stays steady even under continuous update load. This article starts from the root cause, then walks through the design, how to use it, measured performance, and where it fits.

Why Columnar Queries Slow Down Over Time

Many teams go through a similar arc after moving analytical queries from row store to column store. Right after the switch, bulk scans and aggregation queries get dramatically faster, and everyone is happy. But as upstream data keeps flowing in, especially once the workload involves frequent status changes, such as order states, inventory counts, or backfilled user tags, queries that used to return in seconds gradually stretch to tens of seconds. The more updates there are, the slower queries get, and the degradation is persistent. It does not recover on its own.

To understand the root cause, it helps to look at how an ordinary columnar table handles updates. The columnar storage format is naturally suited to large sequential scans, but it is not built for modifying a single row in place. To keep writes efficient, ordinary columnar tables typically use a read-time merge strategy: the baseline data already written to disk and organized by column stays untouched, while every new update and delete is first written into a separate incremental structure. When a query arrives, the storage engine has to compare and merge the baseline and incremental data row by row on the primary key, reconstruct the latest version of each row, and only then hand the result up for filtering and aggregation.

The cost of this mechanism shows up on the query path. When primary keys in the incremental data overlap with those in the baseline, a process that could otherwise scan block after block sequentially has to stop and check, row by row, whether each row was updated or deleted and which version to keep. The very strengths of columnar storage, block-level filtering, columnar computation, and vectorized execution, get repeatedly interrupted because the engine has to wait for multiple data sources to be merged before it can continue. The more incremental data there is, the more often these interruptions happen, and the more noticeable the slowdown becomes.

How the Industry Approaches the Problem

There are a few broad schools of thought for resolving the tension between columnar storage and frequent updates.

One approach sticks with read-time merge. ClickHouse’s MergeTree engine is append-oriented; updates and deletes are deduplicated through background merges or by the FINAL keyword at query time, which still means handling multi-version data while reading. TiFlash uses a similar two-layer LSM structure (Delta plus Stable), with incremental data eventually folded into the Stable layer through full merges. Both perform well when data is mostly appended, but once high-frequency updates enter the picture, the merge cost on the query path is just as unavoidable.

Another approach is Merge-on-Write. StarRocks primary key tables are a typical example: on write, the primary key index locates the position of the old row, marks it as deleted in a delete bitmap, and appends the new row to a new data file. At query time, the engine simply skips old data using the bitmap, with no row-by-row merge. Hologres does something similar, coordinating three structures: a primary key index, columnar data files, and delete bitmap files. This keeps query cost low, but it requires maintaining a global bitmap structure on the write path and implementing multi-version visibility control for it.

OceanBase takes a middle path. As an HTAP database, OceanBase sorts baseline data by primary key during merges, which means row numbers shift as merges happen. Relying on row numbers to locate a delete bitmap, as StarRocks does, would require rebuilding the mapping after every merge, which adds considerable complexity. So OceanBase chose an implementation that does not depend on a delete bitmap: at write time, each update is split into a delete plus an insert with full column data stored, and at query time each incremental data block filters independently, then skips old versions using delete markers. This avoids maintaining a global bitmap, keeps changes to the existing LSM-Tree architecture minimal, and already covers the performance needs of most real-time update scenarios.

Getting to Know the Delete_Insert Table

The core idea of the Delete_Insert table is simple: rather than stitching data together at query time, split it apart and store it ready at write time. Specifically, every UPDATE is rewritten as two actions, delete the old row and insert the new row. Both rows land in storage in full, all-column form, and a delete marker distinguishes which version is valid. As a result, every data block is self-contained at query time. Filter conditions can be pushed directly down to each block and evaluated independently, with no need to merge against baseline data first; at the end, the engine only has to skip old versions based on the delete markers. In other words, Delete_Insert turns read-time merge into write-time split, trading a bit more write cost for large, continuous scans on the query path.

Enabling a Delete_Insert table is lightweight. You specify it through the merge_engine table attribute at table creation, with no extra database-level or tenant-level switch required. It is supported only for columnar tables, and the attribute cannot be changed after the table is created, so it needs to be chosen deliberately at design time.

The most common use is to enable Delete_Insert on a pure columnar order table:

CREATE TABLE orders (
  order_id      BIGINT       NOT NULL,
  user_id       BIGINT       NOT NULL,
  status        VARCHAR(16)  NOT NULL,
  amount        DECIMAL(18, 2),
  updated_at    TIMESTAMP,
  PRIMARY KEY (order_id)
) merge_engine = delete_insert
  WITH COLUMN GROUP (each column);

If the upper layer needs both columnar storage for analysis and row storage for point lookups, you can create the table with redundant columnar and row storage, maintaining both organizations in the same table:

CREATE TABLE orders (
  order_id      BIGINT       NOT NULL,
  user_id       BIGINT       NOT NULL,
  status        VARCHAR(16)  NOT NULL,
  amount        DECIMAL(18, 2),
  updated_at    TIMESTAMP,
  PRIMARY KEY (order_id)
) merge_engine = delete_insert
  WITH COLUMN GROUP (all columns, each column);

If you do not specify merge_engine, it defaults to partial_update, which follows the traditional read-time merge mode. Only columnar tables that explicitly declare merge_engine = delete_insert will persist updates as delete the old row plus insert the new row.

How Storage and Queries Differ

The difference between Delete_Insert and an ordinary columnar table comes down to two paths: the write side and the query side. Because the cost lands in different places, the two suit different scenarios.

The write-side difference is about how much gets written. On UPDATE, an ordinary columnar table records only the modified columns plus the primary key. Unchanged columns are not rewritten, so the incremental data stays small, but each incremental record is incomplete on its own and must rely on baseline data to reconstruct the full row. A Delete_Insert table is different: every UPDATE writes two rows, an old version carrying a delete marker and a new version, both in full-column form. The incremental data is larger, but every row is self-contained and independently readable.

The query-side difference is about how much gets computed. To run a query, an ordinary columnar table must first merge baseline and incremental data row by row on the primary key to determine the latest value of each row, and only then apply the filter conditions to the result. During this process, the block-level filtering and vectorized execution that columnar storage relies on most get frequently interrupted. In a Delete_Insert table, because each incremental data block is complete in itself, filter conditions can be pushed directly down to the block and evaluated independently, with blocks not interfering with one another; at the end, the engine only needs to skip old versions of the same primary key based on delete markers.

The table below lays the key differences side by side:

In short, Delete_Insert shifts part of the cost that the query used to bear forward onto the write side. The payoff is that bulk scans are no longer frequently interrupted, and query time does not keep degrading as updates accumulate. That is precisely what makes it a good fit for real-time analytics.

Performance Gains

To show the gap between the two modes under update load directly, we ran a comparison on a columnar table of 100 million rows. The ordinary columnar table and the Delete_Insert columnar table have identical schemas, differing only in merge_engine. The benchmark query is a full-table scan with a filter:

SELECT * FROM test_column_table WHERE c3 > 99999990;

By controlling the proportion of rows touched by random UPDATE or random INSERT, we observed how the two tables behaved at different incremental scales. All timings below are in milliseconds.

The first scenario is random UPDATE. As the proportion of updated rows rose from 0% to 20%, query time on the ordinary columnar table grew rapidly, while the Delete_Insert table fluctuated only slightly.

With no updates at all, the two are essentially even, which shows that Delete_Insert does not sacrifice baseline scan performance on static data. As soon as 1% of rows are updated, the ordinary columnar table jumps to the 20-second range because of merge overhead, while Delete_Insert stays in the millisecond range. At a 20% update ratio, the ordinary columnar table degrades to the minute range, while the Delete_Insert table only rises from milliseconds to seconds. The gap keeps widening as updates grow, consistent with the expectation that read-time merge overhead grows linearly, or even super-linearly, with incremental scale.

The second scenario is random INSERT. New data does not create primary key conflicts with the baseline, but it still forms incremental data blocks that have to be scanned.

In the INSERT scenario, the Delete_Insert table is almost unaffected by the incremental ratio, because each newly inserted data block is complete in itself and filter conditions can be evaluated directly on the block. The ordinary columnar table still has to process these new data blocks along the primary key path, so its timing keeps climbing as the insert volume grows.

Putting both sets of results together, the conclusion is clear: with zero increments the two modes are even, but once there is more than 1% updated or newly inserted data, the Delete_Insert table’s query time is significantly lower than the ordinary columnar table’s. Under a high update load at the 20% level, the gap reaches two to three orders of magnitude. Put another way, Delete_Insert keeps columnar query performance from continuously degrading as write volume accumulates.

When to Use a Delete_Insert Table

Once you understand the mechanism and performance characteristics, how do you decide in practice? Three kinds of scenarios are especially well suited to Delete_Insert tables.

The first is the real-time data warehouse. The upstream continuously syncs changes from business databases through CDC or Kafka, while the downstream needs to run analytical queries at any time, with both data freshness and query response guaranteed. Ordinary columnar storage easily falls into query degradation under this write-while-read load, whereas Delete_Insert moves the cost forward to the write path, which matches this pattern well.

The second is HTAP mixed workloads. The same table has to support updates from online business while also serving BI reports and ad hoc queries. Ordinary columnar storage struggles to do both, and row storage is not suited to large-scale scans. A Delete_Insert columnar table paired with redundant row storage lets point lookups go through row storage and analytics go through columnar storage, without the two interfering.

The third is high-frequency status update workloads. Fields such as order status, device status, and user tags get repeatedly backfilled and overwritten. An ordinary columnar table faces a large amount of incremental data with overlapping primary keys, where merge overhead is heaviest; Delete_Insert avoids exactly this by writing two full-column rows and filtering by block at query time.

Conversely, if a table is essentially read-only after writing with almost no updates, or is small in scale with simple queries, then the extra storage and write cost of enabling Delete_Insert is not worth it, and an ordinary columnar table is the better choice.

There is a simple rule of thumb for deciding: if a columnar table sees continuous UPDATE or DELETE and its query performance shows observable degradation over time, it is worth trying the Delete_Insert table mode. Sometimes performance tuning does not require re-architecting anything; choosing the right storage mode is enough to solve the problem.

Multi-Model Data Types: The Foundation for AI-Driven Analytics

Charles Wu — Thu, 09 Jul 2026 11:52:50 +0000

How to eliminate AI data silos — unifying vector, text, and relational queries in one engine

When a database is no longer confined to transactions and reporting, but instead has to power AI inference, knowledge retrieval, and real-time analytics all at once, the data it must understand reaches far beyond the relational table. Vectors, full-text, JSON, arrays, and large objects are becoming the basic vocabulary of the data layer in the AI era. OceanBase takes the approach of natively supporting these multi-model data types inside a single database, so that structured and unstructured data share the same transaction engine and SQL entry point, eliminating the need to shuttle data between systems. This article walks through the technical details of these capabilities, how fused queries work, and how they play out in real AI workloads.

1. Industry Trends and Technical Challenges

The rapid adoption of GenAI is redrawing the boundaries of what a data layer must do. Training and inference on large models consume not only structured transactional data, but also text, images, audio and video, logs, and vectorized representations. A single end-to-end AI request — whether RAG retrieval, intelligent recommendation, or an Agent workflow — often has to fetch a user profile, a behavioral sequence, document fragments, and their corresponding embeddings together, all within milliseconds. This imposes three direct requirements on the data layer: native support for multi-model data types, real-time freshness so data is analyzable the moment it lands, and the ability to combine structured filtering, vector recall, and full-text matching into a single fused query.

Yet the mainstream approach today still stitches together several specialized systems: a relational database for transactional data, a vector database for embeddings, object storage for raw files, and a message queue to tie them together. Each system looks fine on its own metrics, but stacking them creates a fresh round of data fragmentation. The moment the business needs a cross-modal composite query — say, “filter a target segment by user profile, then run a vector nearest-neighbor search over their review text” — data has to be synchronized and joined across multiple systems, and freshness, consistency, and development complexity all blow up at once.

The governance cost is just as significant. With multiple metadata, permission, and lineage systems coexisting, enterprises struggle to build a unified view of their data assets. Different engines expose different query interfaces (Spark SQL, Flink SQL, dedicated vector APIs), so access control and governance standards fragment accordingly. Add the redundant storage, duplicated computation, and operational overhead of running several platforms in parallel, and a unified multi-model architecture shifts from an architectural preference to a hard TCO requirement.

2. A Panorama of OceanBase Multi-Model Data Types

Answering these challenges is not about simply wrapping several engines together; it is about letting multiple data models coexist natively within one storage, transaction, and SQL system. OceanBase is a distributed, multi-model integrated database that, in real-time analytics scenarios, handles structured, semi-structured, and unstructured data on a single SQL engine. Because all multi-model data shares one ACID transaction engine and one unified SQL entry point, cross-system data movement is eliminated at its root, and “one copy of data, many dimensions of analysis” becomes the default. Below we look at the role each of the five core data types plays.

2.1 LOB (Large Object)

LOB is the storage foundation of multi-model capability. The multimodal data in AI scenarios — images, text, audio, video — are all essentially large objects, and how efficiently they can be stored and accessed directly determines the throughput of model training and inference above. Through its SQL interface, OceanBase supports up to 512 MB per column and provides efficient TB-scale large-object access via the DBMS_LOB package, allowing bulky data such as video frame sequences, long documents, and raw logs to sit alongside structured fields. How solid this layer is determines how far the multi-model indexes and operators above it can go.

2.2 JSON

JSON plays a different role across three kinds of scenarios. In transactional workloads it serves as a schemaless, flexible column, well suited to write paths where the business structure evolves frequently. In analytical workloads, JSON multi-value indexes make flexible computation over multi-dimensional tags possible, without rebuilding the table schema for every tag combination. And in AI workloads, JSON acts as the bridge between models and applications — the standard input and output format for Agents and workflow pipelines.

Under the hood, OceanBase offers a rich set of computational expressions and multi-value indexing, so that complex JSON path expressions can execute efficiently with index support. On the storage side, it uses a JSON Binary format to optimize random reads and writes, and builds structured encoding on top of it: by extracting shared Schema information from structurally similar JSON documents, it further improves the compression ratio and markedly speeds up queries based on JSON Path. As a result, JSON retains its flexibility while approaching the execution efficiency of native columns under analytical loads.

2.3 Array / Roaring Bitmap / Map

Collection types address a class of problems that traditional relational models handle poorly: high-cardinality tags combined with large-scale analytics. Roaring Bitmap and Map target high-end data-mining scenarios such as large-scale tag analysis, audience segmentation, and deduplicated aggregation, using bitmap compression and on-demand decoding to dramatically cut memory and compute overhead. Array is a better fit for workloads that must preserve order and duplicate elements — log retrieval, behavioral trails, multi-dimensional tags — letting a variable-length event sequence be expressed within a single field. Pushing these types down into the database kernel means that recommendation, risk control, and similar workloads no longer need extra middleware for every collection operation.

2.4 Vector Type

The vector capability follows a fully in-house path built on vector algorithms plus the database, rather than bolting an external vector engine onto the side of a relational database as a plugin. The direct payoff of this choice is that the vector index can be deeply coupled with the relational query optimizer and transaction system: data is searchable the moment it is written, and index maintenance shares the same consistency semantics as DML. In public benchmarks such as VectorDBBench, OceanBase’s vector retrieval performance surpasses that of typical open-source vector database competitors, which means moving vector workloads into the primary database does not come at the cost of performance.

2.5 Full-Text Search

Full-text indexing is a native component in OceanBase, covering keyword-centric retrieval scenarios such as document search and log analysis. More importantly, full-text indexing can work alongside vector retrieval within a single SQL statement to form Hybrid Search: the keyword side handles exact matching and explainability, while the vector side handles semantic recall and similarity ranking. This combination maps precisely onto the dual demand of knowledge retrieval — to find accurately and to understand the meaning — and forms a key foundation for RAG-style applications.

3. How Multi-Model Capabilities Work Together in a Single SQL

Offering multiple data types within one system is not enough; the real value lies in whether they can work together in a single SQL statement. OceanBase is designed so that a multi-model query is no longer “an assembly of several single-model queries,” but a single plan understood by the optimizer and execution engine as a whole.

3.1 Fused Query Patterns

The most common fusion pattern is joint analysis of structured and semi-structured data. Standard SQL can directly filter, aggregate, and join over JSON fields and Array columns, with multi-value indexes ensuring such queries do not degrade into full table scans. The second pattern combines structured conditions with vector similarity search — for example, using a WHERE clause on a user-profile table to narrow down a segment, then ranking by vector distance in ORDER BY, all without any cross-system join. The third pattern is hybrid retrieval that uses structured filtering, full-text search, and vector retrieval together: keyword recall and semantic recall are merged within one query, and the optimizer decides the execution order. This is the core source of RAG recall quality.

The following is illustrative pseudo-code showing what a fused query looks like at the syntax level:

SELECT d.doc_id, d.title,
       MATCH(d.content) AGAINST ('OceanBase multi-model') AS bm25_score,
       VECTOR_DISTANCE(d.embedding, :query_vec) AS vec_dist
FROM   docs d
WHERE  d.tenant_id = 1001
  AND  JSON_VALUE(d.tags, '$.category') = 'whitepaper'
  AND  ARRAY_CONTAINS(d.audience, 'architect')
ORDER  BY 0.4 * bm25_score - 0.6 * vec_dist DESC
LIMIT  20;

This single query uses structured conditions, a JSON path, an Array containment check, full-text BM25 scoring, and vector distance all at once, and every operator is scheduled within one execution plan by a unified optimizer.

3.2 How the Underlying Engine Accelerates Multi-Model Analytics

Making these query patterns genuinely fast relies on several mutually reinforcing engine features. The first is a unified optimizer and execution engine: multi-model queries share the same cost model, vectorized execution engine, and parallel framework. The optimizer maintains a separate cost metric for columnar scans, automatically chooses a row-store or column-store path based on the shape of the data, and uses AUTO DOP to decide the degree of parallelism — no manual tuning required.

The second is a unified storage engine with hybrid row-column storage: incremental data accepts transactional writes in row-store form, while baseline data lands in column-store to speed up analytical scans, and the user’s DML operations remain completely unaware of the distinction. Multi-model types follow the same architecture; JSON, Array, and vectors all benefit on the column-store side from columnar compression and the scan savings of Skip Index.

The third is the vectorized execution engine: batch processing combined with SIMD instruction acceleration, with the batch size adapting to data characteristics, and TP and AP sharing the same execution framework. This mechanism applies not only to traditional numeric aggregation but also to multi-model operators such as JSON Path evaluation, Array element matching, and vector distance computation, so that “one SQL across multiple models” requires no performance compromise.

The fourth is a decoupled storage-compute architecture: the full dataset resides in object storage to keep costs down, while hot data is served through a local cache to guarantee query performance, and multi-model data needs no special treatment at this layer. Layered on top of the kernel-level integration of Btree indexes, JSON multi-value indexes, full-text indexes, and vector indexes, the entire multi-model query path — from storage through indexing to execution — is closed within a single stack.

4. Application Scenarios

Placing these capabilities into concrete workloads reveals how much a unified multi-model architecture simplifies the end-to-end design.

In intelligent recommendation and personalized marketing, the user profile is typically a set of structured fields, the behavioral sequence is well suited to Array, and product descriptions exist as vectors. With OceanBase’s fused queries, the recall stage can complete profile filtering, behavior-pattern matching, and product vector nearest-neighbor search in a single SQL statement, then hand off to a ranking model for scoring — the whole pipeline with no back-and-forth data movement between an OLTP database and a vector database.

In RAG knowledge-based Q&A, full-text search over enterprise documents, paragraph vector recall, and structured metadata filtering can all converge into one database. The retrieval stage leverages both keyword and semantic signals, permission and tenant boundaries are guaranteed natively by the database, and the context fragments needed to generate an answer can be fetched in a single query — noticeably reducing both the latency and the complexity of the end-to-end pipeline.

In real-time risk control, transaction records accept TP writes in row-store form, user tags carry multi-dimensional attributes as JSON and Bitmap, and anomaly patterns are represented as vectors. When a transaction arrives, the engine can complete rule-based hard-condition filtering, tag-based segmentation, and vector-based matching of similar suspicious patterns in milliseconds, fusing the multimodal signals into a single comprehensive judgment — avoiding the serial calls between a rule engine and a vector service that traditional architectures require.

In multimodal data lake integration, OceanBase connects to external data sources such as CSV, Parquet, ORC, and HDFS through its external table capability, bringing unstructured data that previously sat in the data lake into unified SQL analysis. No separate ETL pipeline is needed between the structured primary database and external lake data; teams can access internal and external tables with the same SQL semantics, turning the data lake from an “analytics island” into just another storage medium under a unified engine.

5. Summary and Outlook

Returning to the original premise, a unified multi-model architecture matters because it directly answers the two questions the AI-era data layer must solve: how to eliminate a new round of data silos, and how to reduce architectural complexity without sacrificing freshness or consistency. OceanBase’s differentiated positioning is not “hanging yet another vector engine next to the database,” but full-stack native integration of multi-model capabilities — from storage format and indexing system to optimizer and execution engine.

From an engineering standpoint, the value of this integration only grows as AI applications deepen: when Agents and workflows become the new data consumers, a unified SQL entry point, a unified permission model, and a unified execution engine will directly determine how fast the applications above can iterate. OceanBase will continue to strengthen full-text search, fused queries, and large-scale unstructured data processing, and round out a more complete set of data service interfaces around AI Agent scenarios — making “one copy of data, serving many forms of intelligence” a capability that engineering teams can rely on by default.

Write for OceanData4AI

Charles Wu — Sun, 05 Jul 2026 17:17:35 +0000

OceanData4AI is a global community for technical leaders and senior practitioners shaping data-intensive AI systems — from agents in production to enterprise RAG, real-time decisioning, and governance.

What we publish

Data architecture for AI agents (vectors, graphs, OLTP, OLAP, lakehouse)
Real-time decisioning (risk, customer intelligence, security)
Enterprise knowledge engineering & RAG at scale
Edge–cloud coordination for embodied AI & IoT
AI governance, security, and compliance
Career growth for Data+AI leaders

Want to write for us? Share vendor-neutral engineering stories and architecture lessons — stack-agnostic perspectives welcome.

Note: Initiated by the OceanBase community team; we welcome all viewpoints regardless of vendor.

OceanBase Vectorized Execution Engine — Accelerating Large-Scale Data Analytics

Charles Wu — Sun, 05 Jul 2026 13:33:46 +0000

What vectorized execution means for billion-row query performance

When people think about database query performance, the first things that come to mind are usually obvious optimizations such as indexes, partitioning, and parallelism. But in large-scale analytical scenarios, the factor that truly limits performance is often something more fundamental and far less visible — the way the query execution engine itself processes data. OceanBase introduced a vectorized execution framework starting in V3.2, and after several rounds of iteration, it completed a systematic rewrite of the data format, operators, expressions, and storage layer of the execution stack in V4.3, fundamentally improving the data-processing efficiency of the query execution engine. This article starts with the difficulties traditional execution models run into under analytical workloads, then gradually introduces the design philosophy and key technical implementation of OceanBase’s vectorized engine, along with the measured gains this technical evolution delivers.

1. Why a Vectorized Execution Engine Is Needed

To understand the value of a vectorized execution engine, we first need to understand what it replaces — the traditional Volcano model (also known as the Iterator model) — and the fundamental limitations this classic model, in use for three decades, exposes when facing modern analytical workloads. We will begin with how the Volcano model works, trace its execution through a real SQL query, and then analyze, one by one, the four layers of performance cost hidden behind the row-at-a-time processing pattern.

How the Volcano Model Works

Almost all relational databases use the Volcano model to execute queries. The core idea of this model is: each operator implements a next() interface that returns one row of data per call, and an upper-level operator pulls data by repeatedly calling the next() of the operator below it.

In the Volcano model, each operator only needs to care about its own logic; operators are composed through a unified interface, and adding a new operator does not affect the implementation of existing ones. This design works very well in OLTP scenarios (point lookups, small-range scans, processing tens to thousands of rows) — the cost of function calls is negligible compared to disk I/O and network latency.

The problem appears in analytical queries. Take a simple SQL statement as an example:

SELECT SUM(amount) FROM orders WHERE status = 'paid';

Under the Volcano model, the execution proceeds as follows:

The aggregation operator calls the next() of the filter operator
The filter operator calls the next() of the scan operator
The scan operator reads one row from the storage layer and returns it to the filter operator
The filter operator checks status = 'paid'; if not satisfied, it repeats steps 2-3 until it finds a row that meets the condition
The filter operator returns that row to the aggregation operator
The aggregation operator adds amount to the running SUM
Go back to step 1, until the scan is complete

If the orders table has 1 billion rows, of which 30% satisfy status = 'paid', then the scan operator's next() is called 1 billion times and the filter operator's next() is called 300 million times. That is roughly 1.3 billion virtual function calls just to complete a single summation.

Note that the business logic of this SQL statement is essentially just “iterate over an array and sum up the elements that meet a condition.” If you hand-wrote this logic in C, a simple for loop would do the job. But under the Volcano model, the same logic is scattered across three operators and coordinated through 1.3 billion function calls. The cost of this abstraction is acceptable in OLTP scenarios — when processing a few dozen rows, the overhead of a dozen-odd function calls is negligible. But when the data volume scales up to the billion-row level, this cost becomes impossible to ignore.

The Hidden Costs, Exposed Layer by Layer

1.3 billion function calls is already a startling number, but it is only the tip of the iceberg. When we look closely at the actual execution behavior of the Volcano model at the CPU microarchitecture level, we find that the row-at-a-time processing pattern systematically wastes hardware resources across four dimensions.

Function Call Overhead

Each virtual function call involves a series of operations: vtable pointer lookup, indirect jump, stack frame allocation, and register save/restore. On a modern x86 CPU, the cost of one indirect function call is about 5–10 nanoseconds. For 1 billion rows, the function calls alone consume about 5 seconds — 5 seconds during which no useful computation happens at all, with the system merely jumping back and forth between calls and returns.

CPU Cache Invalidation

The cache line of a modern CPU is typically 64 bytes. Under a row-store layout, even if the filter condition depends only on the status column, the system still has to load the entire row into the cache hierarchy, causing a large number of irrelevant fields to consume memory bandwidth and CPU cache. With main-memory access latency around 60-100ns, the cumulative wait time from cache misses over 1 billion rows can reach tens of seconds. If, instead, the same column is stored contiguously, a single cache line can hold 16 INT32 values, improving cache utilization by an order of magnitude.

CPU Pipeline Flushes

Modern CPUs use 15–20 stage pipelines to process instructions in parallel. Row-at-a-time processing introduces a large number of unpredictable branches through per-row NULL checks, filter-condition evaluations, and so on. For a filter condition with a 30% pass rate, the branch misprediction rate is about 20–30%, and each branch misprediction causes a pipeline flush of about 15 clock cycles. The cumulative loss from a single filter condition over 1 billion rows is about 1 second, and the compounding effect of multiple conditions and NULL checks in a complex query significantly slows down execution.

Idle SIMD Capability

Modern CPUs provide SIMD instruction sets (for example, AVX2’s 256-bit registers can process 8 INT32 values at once, and AVX-512 can process 16 at once). But in the row-at-a-time processing mode, a single comparison operation uses only 32 bits of data, leaving 94% of the hardware capability in a 512-bit register completely idle. This is like having a 16-lane highway but allowing only one car through at a time.

AP Workload Characteristics Amplify Every Problem

OLTP queries usually process a few rows to a few thousand rows, so the absolute amounts of function call overhead, cache misses, and pipeline flushes are all small and are masked by I/O latency. But the characteristics of AP queries are entirely different:

Full table scans: involving millions to billions of rows
Aggregation statistics: must traverse all the data
Multi-table JOINs: intermediate results may be larger than the original tables

When the number of rows processed jumps from the thousands to the billions, the per-row 5–10ns function call overhead swells from microseconds to seconds, and the per-row cache miss swells from milliseconds to minutes. There is a fundamental mismatch between the row-at-a-time processing of the Volcano model and the demands of analytical workloads.

The conclusion is now clear: the row-at-a-time processing pattern lags behind the capabilities of modern hardware across all four dimensions — function calls, cache utilization, pipeline efficiency, and SIMD utilization. We need a new execution model that changes, from the ground up, the way data flows between operators — so that every clock cycle of the CPU is used, as much as possible, on useful computation rather than wasted on waiting and jumping.

This is exactly the design starting point of the vectorized execution engine.

2. The Core Ideas of OceanBase’s Vectorized Engine

Once the fundamental problems of the traditional Volcano model are clear, the direction for a solution emerges as well: since the core ills of row-at-a-time processing are “one function call per row, one random memory access per row, one branch evaluation per row,” the most direct response is — to stop processing row by row. OceanBase’s vectorized engine is built around four core principles: batch processing to amortize overhead, a columnar memory layout to improve cache hit rates, SIMD instructions to unleash hardware parallelism, and branch elimination to protect the CPU pipeline. We will expand on each below.

Batch Processing

The core insight of vectorized execution is: amortize the overhead of a function call across N rows of data.

Returning to the earlier example SELECT SUM(amount) FROM orders WHERE status = 'paid', under vectorized execution (assuming a batch size of 1024):

The scan operator reads the status column and the amount column for 1024 rows at once
The filter operator compares 1024 status values in a tight loop and produces a bitmap marking which rows satisfy the condition
Based on the bitmap, the aggregation operator accumulates the amount values of the qualifying rows in a tight loop
The above process repeats until all data is processed

Number of function calls: 1 billion rows / 1024 = about 976,000 (roughly 2.93 million across the three operators combined). Compared with the 1.3 billion calls of the Volcano model, this is a reduction of nearly three orders of magnitude.

More importantly, the tight loops in steps 2 and 3 — simple for loops iterating over arrays — are exactly the pattern that CPUs are best at optimizing. The compiler can apply auto-vectorization, the CPU prefetcher can accurately predict the access pattern, and the branch predictor can maintain 100% accuracy when facing a branch-free loop body.

Columnar Memory Layout (Data Frame)

Inside OceanBase’s execution layer, data is organized by column, in a structure called the Data Frame. A Data Frame is a contiguous block of memory (up to 2MB) that holds the data of all columns in the current batch.

The reason the Data Frame size cap is set to 2MB: the L2 cache of a modern CPU is typically 256KB-1MB (per core). Keeping the hot working set near the L2 capacity minimizes L2 cache misses. The 2MB cap leaves headroom for multi-column scenarios; the effective amount of data actually used is usually smaller than the L2 capacity.

The tuning logic for vector size: suppose a query involves 10 INT64 columns (8 bytes per column). With a vector size of 1024, the data volume of one batch = 10 × 8 × 1024 = 80KB, which fits comfortably in the L2 cache. If the query involves 100 columns, the same vector size would produce an 800KB working set, which may exceed the L2 capacity. In that case the engine automatically reduces the vector size, ensuring the working set always stays resident in L2.

SIMD Instruction Utilization

Columnar batch data is naturally suited to SIMD processing. Take the filter condition WHERE amount > 100 as an example, assuming amount is of type INT32 and the batch size is 1024:

Scalar execution:

for (int i = 0; i < 1024; i++) {
    result[i] = (amount[i] > 100) ? 1 : 0;  // 1024 comparisons, 1024 branches
}

AVX2 SIMD execution:

__m256i threshold = _mm256_set1_epi32(100);
for (int i = 0; i < 1024; i += 8) {
    __m256i data = _mm256_loadu_si256((__m256i*)(amount + i));
    __m256i cmp = _mm256_cmpgt_epi32(data, threshold);  // 8 comparisons, 1 instruction
    // Write the result into the bitmap
}

128 loop iterations replace 1024 iterations, with zero branch instructions, processing 8 values per iteration. The theoretical speedup is 8×; in practice, due to memory bandwidth limits, it is usually 4–6×.

The OceanBase vectorized engine implements SIMD-accelerated paths on x86 (SSE/AVX2), ARM (NEON), and PPC architectures.

Branch Prediction Optimization

Vectorized operators internally adopt a “default-write-then-override” coding pattern. Take processing a column that contains NULL values as an example:

Traditional approach (one branch per row):

for (int i = 0; i < 1024; i++) {
    if (is_null[i]) {
        result[i] = NULL_VALUE;
    } else {
        result[i] = compute(val[i]);
    }
}

Every iteration of this loop has a conditional branch. If the NULL rate is 5%, the branch predictor has to handle a 95/5 distribution and will still produce about 5% mispredictions.

Vectorized approach (split loops, eliminating branches):

// First loop: branch-free, the CPU pipeline runs at full load
for (int i = 0; i < 1024; i++) {
    result[i] = compute(val[i]);
}
// Second loop: only processes NULL rows (usually very few)
for (int i = 0; i < null_count; i++) {
    result[null_positions[i]] = NULL_VALUE;
}

The first loop has no branches at all, so the compiler can fully unroll and vectorize it. The second loop only iterates over the positions of NULL rows (usually a tiny minority), and even though it contains a branch, its impact is negligible because the iteration count is small.

3. Key Technical Improvements

V4.3 is a systematic rewrite of the OceanBase vectorized engine. After the previous two versions validated the effectiveness of the core ideas — batch processing, columnar layout, SIMD utilization, and branch elimination — V4.3 shifted its focus to the “last mile” of performance engineering: deep, end-to-end optimization spanning the execution layer’s data format, the internal implementation of core operators, and the interaction between the storage layer and the execution layer. We expand on each technical module below.

Data Format Optimization

The execution-layer data format prior to V4.3 was inherited from an early design and carried indirect-addressing overhead: variable-length types referenced the actual data through an array of pointers, so accessing a single value required two memory hops (first read the pointer, then read the data). When processing billions of rows, this indirect addressing produced a large number of random memory accesses.

V4.3 redesigned the columnar data description format of the execution layer:

Fixed-length types (INT8/16/32/64, FLOAT, DOUBLE, DATE, TIMESTAMP, etc.): use a packed array to store values directly. This eliminates all pointer indirection, and data is laid out strictly contiguously in memory. The 1024 values of an INT32 column occupy only 4KB of contiguous memory, and the CPU prefetcher can perfectly predict the access pattern.
Variable-length types (VARCHAR, TEXT, BLOB, etc.): use a structure of an offset array plus a contiguous data region. The offset array records the starting position of each value within the data region, and the data region stores the actual content of all values. Accessing the i-th value simply requires: data[offset[i] ... offset[i+1]]. Compared with allocating memory row by row, this eliminates memory fragmentation and pointer hops.

The core design goal of these formats is: when the CPU processes a batch of data, all memory accesses are sequential and predictable. Sequential access patterns allow the CPU’s hardware prefetcher to load subsequent data into the cache in advance, reducing the actual cache miss rate to nearly zero.

Exploiting Batch-Level Metadata

In real business data, most batches contain no NULL values, and in most batches all rows pass the upstream filter condition. The V4.3 engine maintains metadata flags at the batch level:

The has_null flag: whether the current batch contains NULL values
The all_active flag: whether all rows in the current batch are unfiltered

When has_null = false, the operator can completely skip the NULL-checking logic internally — not skip it row by row, but skip the entire code path. This means that in the code the compiler generates for the "no NULL" scenario, the NULL-related instructions do not exist at all, occupy no instruction cache, and consume no execution resources.

When all_active = true, the operator does not need to check the skip bitmap and can perform an uninterrupted, continuous operation over the entire array. This allows both compiler auto-vectorization and manual SIMD optimization to reach theoretical peak performance.

These two seemingly simple flag bits have a cumulative effect over 1 billion rows: they skip billions of conditional evaluations and eliminate the corresponding branch-prediction overhead.

Sort Operator Rewrite

A traditional sort implementation moves entire rows of data during comparisons and swaps. Suppose we want to sort 1 million rows by 2 INT64 columns, where each row also has 10 non-sort columns (80 bytes of payload in total):

Total size per row: 2 × 8B (sort key) + 80B (payload) = 96 bytes
Each swap during sorting moves 96 bytes
Average number of comparisons in quicksort: 1 million × log₂(1 million) ≈ 20 million
Volume of data movement: 20 million × 96B = about 1.9GB

V4.3’s approach is to separate the sort key from the payload:

The sort phase materializes only the sort key columns and the row number (row_id): 2 × 8B + 4B = 20 bytes/row
After sorting completes, the payload columns are backfilled according to the sorted row numbers

The volume of data movement in the sort phase: 20 million × 20B = about 400MB, a reduction of nearly 5×. More importantly, 20 bytes/row means the L2 cache (256KB) can hold about 12,800 rows of sort data, whereas 96 bytes/row can hold only about 2,700 rows. The cache hit rate during sorting improves dramatically.

Hash Join Operator Rewrite

Multi-column join conditions are a common pattern in AP queries, for example:

SELECT ... FROM a JOIN b ON a.id = b.id AND a.dt = b.dt

The traditional implementation computes the hash value of the two join key columns separately and then combines them. V4.3 encodes a multi-column fixed-length join key into a single fixed-length column: two INT64 columns are encoded into a 16-byte contiguous value.

The benefits this brings:

Hash computation: a single hash operation on 16 bytes of contiguous memory, rather than two independent hashes plus a merge. This eliminates the overhead of one hash call and the merge operation.
Equality comparison: a single 16-byte memcmp (which can be done with one 128-bit SIMD comparison instruction), rather than two 8-byte comparisons plus a logical AND.
Memory access: probing the hash table only requires reading one contiguous block of memory, rather than jumping to the locations of two different columns.

When the hash table contains millions of records and the probe side has tens of millions of rows, the savings from these single operations accumulate into a substantial overall performance improvement.

Aggregation Operator Optimization

For high-frequency aggregation operations such as COUNT, SUM, and MIN/MAX, V4.3 provides specialized implementations that bypass the abstraction layer of the general aggregation framework. For example, SUM(int64_column) in a scenario with no NULLs and no filtered rows is compiled into a pure array-accumulation loop, and the compiler can directly generate SIMD accumulation instructions.

Storage-Layer Vectorization (Push-Down)

The execution flow before V4.3 was: the storage layer decodes the data → converts it to the execution-layer format → the execution layer applies the filter condition. V4.3 pushes a variety of computations down to the storage layer:

Filter Push-Down: filter conditions are executed directly on the encoded data. For dictionary-encoded columns, WHERE city = 'Shanghai' is converted into WHERE dict_code = 7 (assuming the dictionary code for 'Shanghai' is 7). Integer comparisons are an order of magnitude faster than string comparisons, and dictionary-encoded values are usually INT16/INT32 — more compact than the original strings and offering better cache utilization.

Aggregate Push-Down: leverages pre-aggregated metadata at the micro block level. If the storage layer has already recorded statistics such as the row count, SUM, and MIN/MAX of each micro block at write time, SELECT COUNT(*) FROM t can simply accumulate the row-count metadata of the micro blocks, with no need to decode or scan the actual data rows at all.

GroupBy Push-Down: when the grouping column is dictionary-encoded and the number of distinct values (NDV) is low, aggregation can be completed directly in the storage layer using the dictionary-encoded values as the grouping key, avoiding the overhead of decoding the original values and re-hashing.

SIMD-Accelerated Projection: projection templates are customized by column type and length, using SIMD instructions to decode and copy column data in bulk. For example, extracting 1024 INT32 values from the columnar format requires only 128 SIMD load + store operations using AVX2 instructions.

Adaptive Batch Size

OceanBase serves both TP and AP workloads simultaneously, and the vectorized engine automatically selects the batch size based on the query type:

TP queries (point lookups, short-range scans): use a smaller batch size. A point lookup usually returns only 1 row, and an overly large batch would bring unnecessary initialization overhead.
AP queries (full table scans, large-scale aggregations): use a larger batch size (such as 1024 or larger) to fully exploit the advantages of SIMD and caching.

This adaptive mechanism allows the same engine codebase to serve two completely different workload patterns without any manual tuning.

4. Measured Performance Gains

The following are OceanBase’s internal benchmark results, comparing the overall execution time with the vectorized engine turned on versus off under the same hardware environment:

The differences in speedup across the three benchmarks are worth analyzing. The improvement for TPC-H (40×) is significantly higher than for ClickBench (14×), because TPC-H contains a large number of multi-table JOINs and complex sort operations — exactly the scenarios where the Sort/Hash Join operator rewrites yield the greatest benefit. ClickBench is dominated by single-table, wide-table aggregations, where the bottleneck lies more in the I/O and decoding stages, and the function-call overhead of the Volcano model accounts for a relatively small share; therefore the speedup from vectorization is also relatively low. TPC-DS (26.5×) falls between the two, reflecting its query characteristics that mix simple aggregations with complex JOINs.

The incremental gains of V4.3 relative to the earlier vectorized implementations (V4.0/V4.2):

TPC-H: 68% of queries achieve a 10%-40% performance improvement
TPC-DS: 56% of queries achieve a 10%-40% performance improvement
ClickBench: an overall improvement of about 30%

The significance of this set of data is that V4.0 was already a complete vectorized engine, capable of achieving most of the acceleration shown in the table above. On top of that, V4.3, through its data-format rewrite and coordinated operator design, still achieved an additional 10%-40% improvement. This shows that “last mile” performance engineering — eliminating indirect addressing, optimizing memory layout, and exploiting batch-level metadata — can still unlock considerable performance headroom on top of an existing vectorized framework.

Summary

Through the four core techniques of batch processing, columnar memory layout, SIMD instruction utilization, and branch elimination, the vectorized execution engine frees the CPU from the inefficient state it endures under the Volcano model — where it spends most of its time waiting for data and jumping between functions — enabling it to concentrate its compute power on useful data computation. OceanBase introduced the vectorized framework starting in V3.2 to validate the technical direction, made vectorization the default execution path and implemented TP/AP adaptivity in V4.0, and then completed a systematic, coordinated rewrite of the data format and operator implementation in V4.3, achieving 14× to 40× query acceleration on industry-standard benchmarks such as ClickBench, TPC-H, and TPC-DS. Beyond this, the OceanBase vectorized engine continues to evolve, with broader operator coverage to come; operators that are not yet fully vectorized, such as Window Functions and Recursive CTEs, will be rewritten over time.

For users, all of these improvements take effect by default and require no additional configuration. Whether for everyday report queries, real-time data dashboards, or large-scale data exploration and multi-table correlation analysis, the vectorized engine quietly does its work at the underlying layer, allowing OceanBase to deliver significantly faster analytical query responses under the same hardware resources.

OceanBase Real-Time AP: From Data Scanning to Plan Stability

Charles Wu — Sun, 05 Jul 2026 13:18:10 +0000

How to keep real-time analytical queries cost-stable under sustained writes

Two Pain Points of Real-Time AP

Real-time AP is about whether a system can keep delivering stable analytical capabilities while writes, changes, and queries all happen concurrently over the same dataset. For the business, the key question is not “can the SQL execute,” but rather: as data keeps changing, can the system still maintain low-latency responses, high throughput, and controllable resource costs.

From an engineering perspective, the challenges of real-time AP mainly show up at two levels.

First, the analytical target is constantly changing. Queries must work directly against high-frequency, dynamically updated data rather than a stable, static snapshot. Under dynamic data, the cost of reading data grows, which in turn increases the compute burden.

Second, when there is a large amount of dynamic data, suboptimal execution plans become more likely. An unreasonable execution plan multiplies cross-node transfer and compute pressure.

Therefore, the key to real-time AP is not just making a single SQL statement run fast, but continuously controlling the overall cost of analytical queries under sustained writes.

OceanBase Handles a Dataset Where “Baseline + Incremental” Coexist

In real-time AP scenarios, OceanBase does not process a single static dataset, but rather a dataset where “baseline data + incremental data” coexist. Baseline data that can be reused stably corresponds to the Major SSTable; newly written and newly changed data first goes into the MemTable, then converts into the Minor SSTable, forming the incremental path.

The significance of this organization in OceanBase is that the system does not have to sacrifice analytical capability for the sake of the write path, nor give up analytical efficiency for the sake of writing data.
If all data were immediately reorganized into a form better suited for analysis, the higher data-reorganization cost would be pushed onto the write path, directly affecting sustained write efficiency. Conversely, if the system relied solely on the incremental path over the long term, analytical queries would bear higher read amplification and merge costs, making query latency and resource usage hard to keep stable.

Therefore, the difficulty of real-time AP is not simply optimizing one side — writes or analysis — to the extreme, but maintaining a long-term balance between these two kinds of constraints. OceanBase’s basic philosophy is: the incremental path prioritizes write efficiency, while the baseline prioritizes analytical efficiency.

The incremental path first ensures that new data can be continuously written and become visible to queries in a timely manner. As dumping (minor compaction) and merging (major compaction) proceed, this data is gradually folded into the baseline, and large-scale analytical scans are mainly handled by the columnar baseline.

The primary goal of the incremental side is still to serve write efficiency and transactional consistency, so overall it is closer to a row-based access path; but this does not mean it gives up analytical read efficiency. At the physical storage level, OceanBase balances compression and read efficiency for hot data, reducing the extra cost incurred when incremental data participates in analytical queries — without adding too much write overhead.

The six layers of capability discussed below are all built on this premise: what OceanBase aims to solve is not a one-time analytical speedup over static data, but long-term, stable support for analytical queries under dynamic data conditions.

Read Less: How Column Store, Encoding, Skip Index, and Pushdown First Drive Down Read Cost

In real-time AP, the first layer of cost usually comes from unnecessary reads. If large amounts of irrelevant data are already being moved during the read phase, the benefits of subsequent computation and parallel optimization will be noticeably weakened. So the first thing this layer must solve is: how to read as little as possible, and complete filtering as early as possible.

OceanBase’s approach is to push column pruning, filtering, and partial aggregation as close to the data as possible. For baseline data, it uses a columnar organization better suited for analytical reads; for incremental data, it keeps a row-based path better suited for sustained writes. During query execution, the system reads only the required columns where possible, reducing the I/O overhead from irrelevant columns.

Columnar encoding and Skip Index mainly operate on baseline column-store data. Columnar encoding helps reduce read and decode overhead, and supports partial filtering directly on compressed data; Skip Index maintains block-level statistics for block-level skipping — when a data block, statistically, cannot possibly satisfy the filter conditions, the system skips that block directly, avoiding unnecessary reads and decoding.

Incremental data is still primarily organized in a row-based form, and is merged with baseline results according to transactional visibility at query time. Compared with the baseline column store, the incremental path prioritizes write efficiency; however, at the physical storage level, it also tries to balance compression and filtering efficiency over recent data — though such capabilities should not be simply equated with the Skip Index on the baseline column store.

At the same time, filtering and common aggregations (including some Group By operations) can also be pushed down to the storage layer for execution, further reducing the volume of intermediate data sent up to the execution layer.

This layer addresses the read-cost problem: minimize ineffective scans, ineffective transfers, and ineffective intermediate results, providing a smaller and cleaner input for the next layer of “fast computation.”

Compute Fast: How Vectorized Execution, Storage Vectorization, and SIMD Improve Batch Computation Efficiency

Once the scan scope converges, the performance bottleneck usually shifts from I/O to compute efficiency. Real-time AP queries often involve aggregation, sorting, expressions, and join computation; if execution is still primarily row-by-row, CPU cache utilization and instruction execution efficiency will be constrained.

OceanBase’s approach is to replace row-by-row execution with vectorized execution. The vectorized execution engine processes data in batches, reducing frequent function calls and branch decisions, and lets operators and expressions run over a more contiguous data layout. The point is not simply to increase the number of threads, but first to improve the efficiency with which a single operator processes a batch of data.

Meanwhile, storage vectorization aligns projection, predicate, and aggregation-related computations more closely with the vectorized data format, reducing unnecessary data conversion and movement. Combined with SIMD, the system can process more data elements within the same compute cycle, further lowering the compute cost per unit of data.

This layer addresses: after “reading less,” can the data also be “computed fast.” Going one level deeper, the problem is no longer just local compute efficiency — it rises to the overall cost of distributed execution.

Globally Better: One-Phase Distributed Plans, Join Order/Algorithm, Data Distribution, Parallelism, and Auto DOP

Complex analytical queries typically involve multi-table joins, cross-partition access, and data exchange. At this layer, local optimality does not equal global optimality: a join path that looks reasonable in isolation may incur higher cross-node transfer costs; an access path that is faster locally may make overall execution slower.

During optimization, OceanBase adopts a one-phase distributed plan generation approach, evaluating access paths, join order, join algorithms, data distribution properties, and parallel properties within a unified cost framework. In other words, the system considers distributed execution cost during the plan generation phase, rather than first selecting a plan that looks reasonable on a single machine and then bolting on distributed processing afterward. The reason is that these factors are inherently coupled: join order affects the size of intermediate results, data distribution affects whether redistribution is needed, and the parallel strategy directly affects resource usage and response time.

On the parallelism side, OceanBase improves the response speed of complex queries through parallel execution, but a higher degree of parallelism is not always better. Auto DOP (Automatic Degree of Parallelism) leaves the decision of how much parallelism to use to the optimizer; it combines query cost and system state to decide whether to parallelize and what the degree of parallelism should be, balancing query latency against resource consumption.

This layer addresses: how to make distributed queries more reasonable from a holistic perspective. But “better now” does not mean “always effective afterward,” so the next layer must address plan stability under changing data.

Continuously Effective: Statistics, Cardinality Estimation, and Plan Stability

In real-time AP, performance fluctuations in the same class of SQL across different time periods are common. Often, the root cause is not that the SQL text changed, but that after data size, data distribution, and update cadence change, cardinality estimation and cost judgments become skewed, leading to plan regression or frequent plan switching.

OceanBase’s approach is to consider statistics maintenance and plan stability together. Statistics collection is not a one-time action; it must be continuously maintained in light of table size, partition characteristics, update patterns, and maintenance windows. For very large tables or high-frequency change scenarios, there are also more targeted collection strategies.

The quality of cardinality estimation directly affects access path selection, join strategy, and parallel decisions. Once statistics lag or become distorted, the optimizer may make wrong judgments in subsequent steps. For this very reason, in real-time AP, statistics relate not only to “whether this plan is chosen correctly,” but also to “whether the same class of queries can remain stable going forward.”

Scale Read Throughput: Column-Store Replicas, Routing, and Read-Traffic Isolation

When the concurrency of analytical queries keeps rising, path optimization of a single SQL statement alone can hardly guarantee overall throughput. As analytical read requests increase, a dedicated analytical read path needs to be scaled out, and routing strategies are used to direct different types of read requests to the corresponding replicas, reducing resource contention.

OceanBase’s approach is to introduce column-store replicas (C replicas) as an analytical read-only path. Column-store replicas support independent deployment and routing strategies, allowing analytical queries to execute on a more suitable read path and avoiding having all read requests concentrated on the same path.

At the routing layer, the system can combine policy conditions to decide whether to prioritize the column-store replica path, and work with weak/strong consistency reads and fallback mechanisms to balance consistency, availability, and throughput. Its core value mainly manifests in two aspects: first, scaling analytical read throughput; second, achieving TP/AP read-traffic isolation, reducing mutual interference between different workloads.

This layer completes the read-path expansion. The next step is to handle the repeated computation that recurs frequently in high-frequency analysis.

Reduce Repeated Computation: Materialized Views, Refresh, Mlog, and Query Rewrite

Real-time AP often involves a large number of periodic, repeated queries, such as fixed reports, repeated aggregations, and stable dimensional statistics. If everything is recomputed from the base tables each time, the system will repeatedly scan and compute, hurting both throughput and resource efficiency.

OceanBase’s approach is to move part of the repeated computation forward into maintainable precomputed results. Materialized views materialize and persist query results, relying on refresh to catch up with base-table changes: full refresh suits scenarios with few updates that can tolerate a longer maintenance window; incremental refresh suits scenarios with frequent changes that require controlling refresh overhead. Real-time materialized views typically no longer bind query freshness to refresh frequency; instead, they compute the already-materialized results together with incremental changes at query time, so that even under frequent changes, queries can still yield results closer to the current data state, supporting efficient real-time analysis.

Mlog (materialized view log) provides the change basis for incremental maintenance, enabling the system to update materialized results by the scope of changes rather than fully recomputing every time. The query rewrite capability lets the optimizer automatically hit materialized results when conditions are met, reducing the cost of manually rewriting SQL on the business side.

This layer addresses: for analytical tasks with high-frequency, stable patterns, how to avoid recomputing from raw detail every time. At this point, the six layers of capability form a complete optimization path for analytical cost.

Typical Scenarios: How the Six Layers Combine to Take Effect

These six layers are not used in isolation; they combine in different ways depending on workload characteristics. For single-table or wide-table aggregations, the main benefit usually comes from “read less + compute fast”; columnar reads, block-level skipping, and vectorized execution often determine the bulk of the performance gain.

It is worth adding that real-time AP does not correspond only to “ultra-large-scale data” scenarios. In gaming businesses, the more common need is online analysis with “moderate data size but high timeliness requirements,” such as campaign monitoring, retention-and-conversion dashboards, player-behavior segmentation, and real-time operational-metric reviews. Such scenarios likewise require second-level responses and stable throughput, so the six layers of capability still hold — only the optimization focus shifts more toward query latency stability and controllable resources under read-write concurrency.

For detail filtering and multi-dimensional statistics, the capability combination usually extends further to “globally better + continuously effective.” Such scenarios require not only more stable cardinality estimation and more reasonable distributed plans, but also rely more on an appropriate parallel strategy to avoid hard-to-explain performance fluctuations in the same class of queries at different times.

For periodic reports and fixed analytical tasks, “reducing repeated computation” is often key; materialized views, refresh, and query rewrite can significantly lower the cost of repeated aggregation. For systems with continuously growing analytical concurrency, “scaling read throughput” is a necessary complement; column-store replicas and routing strategies determine how far the analytical read side can scale, and how well it can be isolated from other read traffic.

Conclusion

The core difficulty of real-time AP is not getting a single analytical SQL statement to run, but keeping query latency, throughput, and resource cost within an acceptable range over the long term, under conditions of sustained writes and continuous change.
OceanBase’s answer does not rely on any single feature. Instead, on the premise of “baseline + incremental coexistence,” it progressively reduces the overall cost of online analysis across several layers: reading, computation, distributed execution, plan stability, read-path expansion, and precomputation.
For this very reason, when OceanBase real-time AP becomes a long-term capability rather than a one-off performance test, this technical framework is easier to understand, validate, and iterate on — and is better suited to continuously supporting business evolution.

Your OpenClaw Bill Is Bleeding Tokens. Here’s What We Measured — and How to Fix It

Charles Wu — Sun, 28 Jun 2026 13:57:43 +0000

Memory bloat, compaction loss, and a retrieval-first path: ~32% less token spend on the AppWorld dev split — without dumbing the agent down.

Photo by micheile henderson on Unsplash

Developers who actually ship with LLMs know one truth by heart: the context window is not free. Every extra thousand tokens nudges the invoice up and the latency out.

If you run OpenClaw (an agent stack that leans hard on long-horizon sessions), that anxiety gets concrete fast. Picture this: last week you spent two hours with your agent debugging production — logs, configs, experiments — and burned through 30k tokens of back-and-forth. This week you pick up where you left off, and the agent answers: Hi! Which refactor are we talking about?

So you spend a few thousand tokens re-explaining context. The model spends a few thousand more re-understanding. And you still might not land the same mental model you had last Tuesday.

Those 30k tokens? Mostly gone.

That is not a one-off glitch. OpenClaw’s default memory story quietly feeds two token black holes.

Two black holes that blow up your token budget

1) The more you remember, the more you pay

OpenClaw’s agent writes important state into MEMORY.md, and that file gets fully injected into the system prompt on every request. The longer you use the setup, the larger MEMORY.md grows—and every API call pays for the whole thing as input tokens.

Bootstrap caps exist (for example, a 20k-character default per file, 150k total), but long before you hit the ceiling, a bloated prompt starts crowding the model’s working space. OpenClaw’s agent knows information can get lost — so it writes even more aggressively into MEMORY.md, which accelerates the bloat.

2) The more you forget, the more you burn tokens fixing mistakes

When sessions get long, OpenClaw leans on two mechanisms:

Compaction: OpenClaw asks an LLM to summarize older conversation chunks to free context.
Memory flush: before compaction, OpenClaw spins up an embedded agent to decide what to persist into memory/YYYY-MM-DD.md.

But compaction is lossy compression by design, and OpenClaw’s retrieval-side slicing hard-cuts along line and character budgets (by default, 400 tokens per chunk) without respecting semantic boundaries. Important context can get cut mid-thought, recall quality drops, your agent makes mistakes, you rework, rework creates more chat, and you trigger compaction again sooner.

Tool calls are an accelerant

Tool outputs — web_fetch pages, exec dumps—can be huge per message—up to 400k characters per tool result in the worst case. That fills sessions fast. Those intermediates usually should not land in MEMORY.md, but they can still contain value you do not want to discard. Either way, tool-heavy runs tighten the doom loop.

The uncomfortable tradeoff: remembering everything gets expensive; forgetting costs correctness. You need a third path.

A third path: cloud memory that steers tokens instead of hoarding them

seekdb M0 is a cloud memory plugin for OpenClaw. The idea in one sentence:

Do not dump all memory into the system prompt. Before each turn, retrieve only the memory slices that match the current topic — and inject just those.

Unlike loading the full MEMORY.md on every request, M0 stores memory as discrete facts in a cloud database, with vector embeddings and full-text indexes. At conversation start, M0 runs hybrid retrieval (BM25 keyword scoring + vector similarity) and injects the top relevant facts. After each chat, M0 extracts new facts from the dialogue, compares them to what already exists, and decides whether to add, update, or skip.

What that buys you:

MEMORY.md stops ballooning—durable memory lives outside the always-on system prompt, so input tokens drop.
Session resets stop being catastrophic — memory persists and rehydrates without you paying again to restate context you already gave.
Cross-device continuity — your memory is not trapped on one laptop.

For most users, this is meant to feel invisible: you talk; M0 manages memory in the background.

OpenClaw’s native persistence tends to route through compaction over the full session (including tool outputs) and a flush agent that decides what to write — both are comparatively heavy and lossy. M0 splits what to store from how to store it into two phases.

Phase 1: fact extraction

After a conversation, M0 extracts facts from user ↔ assistant text only — not from tool-call intermediates — and uses an LLM to produce atomic facts.

Example: The user is Alex, a database engineer based in Austin. becomes three independent facts.

Hard rules we enforce during extraction:

Preserve time information (do not collapse went to Hawaii last year into a timeless went to Hawaii).
Keep the original language (no automatic translation during extraction).
Do not extract sensitive information.

Phase 2: memory decisions

M0 does not blindly insert facts. M0 retrieves similar existing memories, then asks an LLM whether the new fact should be:

ADD
UPDATE
DELETE (contradictions)
NONE (already covered)

In practice, M0 treats DELETE conservatively as NONE for auto-capture — M0 only adds and updates existing memories and does not proactively delete them, to reduce accidental erasure.

Example decisions:

New fact: "Went to Hawaii last May."
Existing memory: "Has been to Hawaii."
→ UPDATE (time detail added)

New fact: "Doesn't like pizza anymore."
Existing memory: "Likes pizza."
→ UPDATE (preference changed)

New fact: "Is a database engineer."
Existing memories: "Name is Alex" + "Is a database engineer."
→ NONE (already covered)

Implementation detail worth noting: in the memory-decision LLM call, each existing memory’s original ID is replaced with a short temporary index (0, 1, 2, …) so the decision model is less likely to hallucinate or garble long integer IDs. If the decision model returns an index that cannot be mapped back, M0 gracefully falls back to treating the fact as new.

Why this matters for tokens: M0’s fact-extraction stage ignores tool transcripts, so you avoid paying an LLM to read 400k-character blobs just to mint memories.

Tool-result compression: deterministic, zero LLM spend

M0 also attacks session inflation at persistence time. When OpenClaw persists tool results to session history, M0’s tool_result_persist hook replaces raw output with a structured summary—rule-based, no LLM tokens.

Illustrative shape:

Raw: curl returned a 3,000-line JSON payload

Compressed:
  tool: web_fetch
  status: success
  output: 3,000 lines / 48K characters
  preview: {"users":[{"id":1,"name":"Alice"... (300 chars)

M0’s summaries are not about perfect fidelity. They aim for high compression while preserving what happened, whether the tool succeeded, and a short preview.

Compared with OpenClaw’s native compaction, which feeds the entire session (including tool dumps) into a summarizer, M0’s hook-based compression is closer to upstream budgeting: you control what enters the LLM pipeline, instead of waiting until you overflow and then compressing reactively.

Experience + Skill: spend tokens on the right kind of reuse

M0’s memory layer answers who this user is and what they care about. Another common waste pattern in agent stacks is different:

Your OpenClaw agent may have skills, but not durable, reusable know-how distilled from real runs — so every similar task becomes another expensive exploration loop.

M0 splits playbooks into two layers:

Experience (strategy layer): a tight summary of approach + key cautions.
Skill (operations layer): structured steps, prerequisites, and pitfalls.

The two layers link by reference: your OpenClaw agent can pull strategy first, then expand operational detail only when needed — which helps keep the active prompt compact.

Under the hood, M0 stores these in OceanBase (a distributed SQL database) with separate tables for Experience and Skill, indexing title and description with both vector and full-text indexes. Retrieval runs four parallel signals — title vector, description vector, title full-text, description full-text — then merges with RRF (Reciprocal Rank Fusion).

Why four channels? In M0’s retrieval stack, title matching helps lock onto the right name, description matching helps lock onto the right content, vectors help with semantic equivalence (for example, build a playlist vs create a playlist), and full-text tends to win on exact strings like API names and error codes. That complementary mix is meant to make retrieval both accurate and broad: your OpenClaw agent should not need ten mid-confidence hits (think ~0.6 relevance) just to be safe, when three high-confidence items (~0.9) are enough to execute — and that gap maps straight to fewer tokens in the prompt.

M0 also stages knowledge ingestion: M0’s pipeline detects a procedure in traces → structures a Skill (steps / pitfalls / prerequisites) → dedupes (for example, vector similarity > 0.75 merges) → runs moderation → stores. When M0 extracts Experience records, M0’s extractor can see stored skills and reference skill IDs, which keeps links generated rather than hand-maintained.

AppWorld numbers: how much did we actually save?

Early on, we used LoCoMo to probe memory behavior, but found it skews toward chit-chat agents rather than work agents like OpenClaw — where evaluation is harder (skills, multi-step reasoning, structured API payloads).

For a fairer workload, we switched to the AppWorld benchmark — a suite of 750 autonomous agent tasks framed as realistic, stateful challenges. In short, AppWorld’s evaluation is built around state-based unit tests: an agent can complete tasks in different ways, and AppWorld’s harness still checks for unintended harm during the run.

The AppWorld benchmark paper (ACL 2024 resource paper, arXiv:2407.18901) states in the abstract:

The state-of-the-art LLM, GPT4O, solves only ~49% of our ‘normal’ tasks and ~30% of ‘challenge’ tasks, while other models solve at least 16% fewer.

The AppWorld blog (appworld.dev) puts it plainly:

Even the best LLM, GPT-4o, performs quite poorly. E.g., it completes only ~30% of the tasks in the challenge test set correctly.

In our controlled setup on AppWorld dev (54 tasks, 15-step cap, no pre-loaded distilled skills), GPT-4o’s baseline was ~24% (13/54 solved) — below the headline pass rates quoted in AppWorld’s public materials, which reflect a different task mix and evaluation harness than this stripped-down run.

Controlled comparison on AppWorld dev (54 tasks, 15-step cap)

Our setup: we ran traces with Hermes + Qwen 3.6-plus (34/54 solved, 63%), kept all 54 trajectories, then distilled into:

M0 path: 85 experiences (with skill_refs)
Hermes path: 44 SKILL.md files

Then we evaluated GPT-4o on each distilled knowledge base. Only two knobs differ: distillation + storage/retrieval.

Results:

Note: pp = percentage points (absolute change in pass rate, not relative % change).

Headline takeaways:

M0 net +8 tasks (examples mentioned: Spotify-style flows, cross-app tasks, Venmo-style flows), with some wins traded for losses.
Hermes net -1 on GPT-4o in this setup — no positive gain versus baseline.

Why M0 beat file-skill matching in our analysis

Retrieval precision: M0’s vector search can match the task description semantically; Hermes’ filename/tag matching does not understand semantics the same way, so Hermes misses paraphrases. Example (localized for a global audience): Create a Beyoncé playlist vs Bundle twenty Taylor Swift tracks together should route to the same underlying skill — M0’s vectors tolerate wording drift better than brittle naming.
Context hygiene: M0’s Experience records stay light (title-line scale); Hermes’ SKILL.md files can read like full manuals and crowd the model.
On-demand expansion + dedupe: M0 uses skill_refs to load operational detail only when needed, and M0 performs semantic deduplication by pairing vector-similarity checks with an LLM merge so near-duplicate skills fold together instead of piling up. Hermes may inject all matching skills at once, and collisions among Hermes’ SKILL.md filenames can overwrite useful variants.

Efficiency (same GPT-4o runs as the table): average steps 9.5 → 6.2 (-35%), tokens 2.56M → 1.74M (-32%). Even failures become cheaper failures — less thrash, less exploration tax.

Teach once with a strong model, run forever with a cheaper one

Rough cost sketch (our pricing assumptions — not a live vendor quote):

GPT-5.4 one full pass: ~$57.6 at $22.5 / 1M tokens
GPT-4o baseline: 2.56M tokens → ~$25.6 at $10 / 1M
GPT-4o + M0 distilled experience: 1.74M tokens → ~$17.4 at $10 / 1M

Note (GPT-5.4 line, illustrative): Blended $/M on ~2.56M tokens in our draft; not a literal line item on OpenAI’s price list. Recompute from your own traces, then confirm current rates on the OpenAI API pricing page before you budget.

Our playbook: let GPT-5.4 or Claude Sonnet 4.6 solve the hard version once; M0 distills traces into Experience + Skill; then route repeat work to GPT-4o (or cheaper) with higher pass rates, fewer steps, and a smaller bill than the old naive rerun.

The production takeaway is obvious: in a typical agent product, most requests are repetitive patterns. You do not need the most expensive model on every call — either let a strong model teach the task once, or have a human guide a weaker model through one clean run — and then later runs can finish on their own, grounded in distilled experience.

Beyond one user’s workspace: once an Experience picks up enough positive feedback, M0 can publish it to a shared space where any other M0-connected agent can retrieve it — your solved mistakes stop being only yours. M0’s vector dedupe folds overlapping discoveries together, contributor metadata accrues, and that crowd knowledge is meant to grow out of distillation itself — not through a separate manual editorial pipeline.

One-sentence install

OpenClaw is built around the idea that the assistant should do the heavy lifting, not a human babysitting every step — and seekdb M0’s install path is written the same way: you send your OpenClaw assistant a single line, for example:

Read https://m0.seekdb.ai/SKILL.md and install and configure M0 per the instructions.

After that, the agent is expected to check the installed OpenClaw version, obtain an Access Key, install the m0 plugin, apply the openclaw.json / gateway settings in one shot, and restart the gateway—without you clicking through a setup wizard.

Humans can still sanity-check the service:

# health check
curl -s https://m0.seekdb.ai/health

# create a memory instance
curl -s -X POST https://m0.seekdb.ai/api/instances/ \
  -H "Content-Type: application/json" \
  -d '{"name": "my-memory"}'

The returned ak field is your Access Key for authenticated memory operations.

Try it: wire up M0, then tell your OpenClaw agent a handful of real details about you — seekdb M0 will usually auto-extract about five or six facts, run them through the memory-decision step, and persist them in the cloud. On later chats it should pull your technical preferences back in instead of cold-starting the interview from zero.

At that point it already knows who you are — so you should not have to spend tokens re-introducing yourself.

Wrapping up

So why does OpenClaw token usage spike? Because the default memory path leans on MEMORY.md full-load plus reactive compaction and file-scattered recall. The prompt gets crowded; history gets summarized away; OpenClaw’s agent may not even know what to search for. You pay for remembering, you pay again for forgetting, and you pay a third time for re-discovery.

M0’s bet is simpler to state than it is to build:

Free memory from the always-on context — store independently, retrieve on relevance, persist across sessions.

More crucially: distill execution into reusable Experience + Skill, then retrieve sharply — M0-style high-precision recall beats padding the prompt with maybe relevant bulk.

Our AppWorld comparison is the punchline: same model, same tasks, swap the knowledge system, and you move from 2.56M → 1.74M tokens while pass rate climbs ~15 pp in our reported setup.

Spend tokens on thinking — not on re-learning what you already solved.

Sources

OpenClaw: https://openclaw.ai/
seekdb M0: https://m0.seekdb.ai/
PowerMem (open source): https://github.com/oceanbase/powermem
AppWorld: https://appworld.dev/
seekdb D0: https://d0.seekdb.ai/

Existing M0 users: this upgrade applies automatically — Experience and Skill records accumulate in M0 during normal agent use, with no extra configuration.

New users: send the one-liner install prompt to your OpenClaw agent and let it walk the setup.

The first time you pay tuition on a mistake, you should not have to pay full tuition again.