OceanBase Database Enterprise Edition V3.2.1

Version information

• Release date: September 30, 2021

• Version: V3.2.1

Overview

OceanBase Database V3.2.1 provides enhanced performance and features, such as increased compatibility with Oracle and MySQL databases, HTAP capability, database performance, and cost efficiency of small instance types. OceanBase Database continuously improves its compatibility with Oracle and MySQL, ease of use, stability, and user experience. In this version, OceanBase Database allows you to conveniently migrate your applications, uniformly deploy your transaction processing (TP) and analytical processing (AP) services, and significantly reduce your application development, deployment, and O&M costs.

Core features:

• Increased compatibility with Oracle

  • More system packages are provided, and the functions of some system packages are improved. Specifically, the UTL_FILE and DBMS_DESCRIBE packages are provided, and the functions of the DBMS_SQL and DBMS_CRYPTO packages are improved.

  • More functions, triggers, and data types are supported. Specifically, the DBMS_JOB package, statement-level triggers, INSTEAD OF triggers, user-defined aggregate functions, and CONVERT function are supported.

• Increased compatibility with MySQL

  An auto-increment column can be used as a partition key. DML triggers and the UUID_SHORT functions are supported.

• Performance

  • Compared with OceanBase Database V3.1.X, the performance of this version is improved by 24% in running the Sysbench benchmark, 36% in running Benchmark SQL, and 655% in running the TPC-H benchmark.

  • This version optimizes index pruning rules for execution plans, improves buffer refresh algorithms, removes duplicate expressions, and optimizes the execution efficiency and memory consumption of TABLE SCAN and many other operators.

• Ease of use

  Time-based data recovery at the tenant level is supported. Settings can be configured to recycle Schema History files. The recycle bin can be automatically purged. Object tasks can be presented by using directed acyclic graphs (DAGs). Errors can be quickly located during PL debugging.

• Stability

  Global deadlocks can be detected. Local route tables can be automatically refreshed. Strong consistency follower read is supported. The detection of system exceptions is enhanced. The network traffic between clusters is better controlled.

• Kernel enhancements

  You can manually collect optimizer statistics, invalidate SQL execution plans, and manage tenant disk space limits. In addition, the storage space is optimized. Non-template-based subpartitions can be added to a table group. Indexes can be automatically encrypted by transparent data encryption (TDE). Tenant-level snapshot backup and restore are supported. The performance of online DDL modifications is enhanced.

New features

Compatibility with Oracle

• More system packages are supported or improved.

  • The UTL_FILE package is supported.

    You can use the package to read and write system files in PL by calling functions such as FOPEN(), PUT(), FFLUSH(), FCLOSE(), FILE_TYPE(), and PUT_LINE().

  • The DBMS_DESCRIBE package is supported.

    You can use the package to obtain the details of a PL object by calling the DESCRIBE_PROCEDURE() function. The supported types are NUMBER_TABLE and VARCHAR2_TABLE.

  • The DBMS_SQL package is improved.

    You can use the package to call dynamic SQL statements in PL. The following functions are added: EXECUTE_AND_FETCH(), DESCRIBE_COLUMNS(), DEFINE_ARRAY(), and IS_OPEN().

  • The DBMS_OUTPUT package is improved.

    The CHARARR data type is supported.

  • The DBMS_UTILITY package is improved.

    The GET_HASH_VALUE() function is added.

  • The DBMS_CRYPTO package is improved.

    The HASH() function is added to support HASH algorithms such as MD5. The ENCRYPT() and DECRYPT() functions are improved to support DES and 3DES algorithms.

  • The GET_DDL() function of the DBMS_METADATA package is improved.

    SQL statements to retrieve the TYPE_SPEC and TYPE_BODY types are supported.

• The DBMS_JOB package is supported.

  You can use the package to schedule and manage the following custom tasks: creating a task by calling DBMS_JOB.SUBMIT(), changing the task attributes by calling DBMS_JOB.CHANGE(), modifying the task content by calling DBMS_JOB.WHAT(), modifying the task execution time by calling DBMS_JOB.NEXT_DATE(), modifying the task execution interval by calling DBMS_JOB.INTERVAL(), modifying the nodes on which the task runs by calling DBMS_JOB.ZONE(), deleting the task by calling DBMS_JOB.REMOVE(), enabling or disabling the task by calling DBMS_JOB.BROKEN(), and running existing tasks by calling DBMS_JOB.RUN(). OceanBase Database can execute the preceding tasks as scheduled.

• Statement-level triggers are supported.

  The definition of a statement-level trigger does not contain a FOR EACH ROW clause. A statement-level trigger executes only once regardless of the number of rows that meet the conditions.

• The INSTEAD OF trigger is supported.

  You can use the trigger to skip the specified DML statements on tables or views and execute other statements defined in the trigger.

• User-defined aggregate functions are supported.

  OceanBase Database provides many built-in aggregate functions, such as MAX(), MIN(), and SUM(). These functions do not work with complex data types such as object data types and LOB. You can use custom aggregate functions to aggregate complex data types in the same way as the built-in aggregate functions.

• The AT TIME ZONE clause is supported.

  Example: SELECT CAST((SYSTIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '0 00:05' DAY TO MINUTE) AS TIMESTAMP) FROM DUAL;

• The CONVERT() function is supported to convert the default character set of a string to the specified character set.

  Example: CONVERT(TMP_STRING, 'ZHS16GBK');

• The SYS_CONTEXT function is improved.

  The following user environment options are added: DB_NAME, INSTANCE, INSTANCE_NAME, SID, LANG, and LANGUAGE. Example: SELECT SYS_CONTEXT('USERENV','DB_NAME') FROM DUAL;

• DICTIONARY objects can be created.

  The applications can use the UTL_FILE package to write data to the local file system or import data from the local file system to the database.

• The information of rows affected by DML statements can be returned to OceanBase Call Interface (OBCI).

  OBCI obtains the information about rows affected at the end of a DML operation. This reduces the number of interactions between the client and the server for higher application execution efficiency.

• The FORCE option is supported when you create a view.

  This feature allows you to create the definition of a view when the dependent objects do not exist or you do not have the CREATE VIEW privilege.

• Full-width characters in an SQL statement can be parsed.

Compatibility with MySQL

• An auto-increment column can be used as a partition key.

  Example: create table t2(inv_id bigint not null auto_increment ,c1 bigint, primary key (inv_id) ) partition by hash(inv_id) partitions 8;

  Note that when you use an auto-increment column as a partition key in OceanBase Database, the values in the auto-increment column are globally unique, but they may not always be incremental in a partition. This is different from the behavior of a native MySQL database. Compared with partitioned tables that use other partitioning methods, the performance of insert operations on a table partitioned by using an auto-increment column can be lower.

• DML triggers are supported.

  OceanBase Database V3.2.1 is compatible with the syntax of MySQL 5.6 and allows you to create a trigger on a table. When the specified DML operation is performed on the table, the specified actions are triggered. Examples:

  CREATE TRIGGER TRI_TEST_BEFORE_UPDATE BEFORE UPDATE ON TEST FOR EACH ROW
  BEGIN
  INSERT INTO MSG VALUES (NEW.C1, NEW.C2, NEW.C3);
  INSERT INTO MSG VALUES (OLD.C1, OLD.C2, OLD.C3);
  END;
  

• MySQL-specific comments, such as certain C-style comments that include a MySQL version number, are identified.

  A C-style comment can be specified in the /*!consecutive-version-number SQL*/ format, where the consecutive version number is converted from the MySQL version number. For example, MySQL 5.6.11 is converted to 50611 in /*!50611 ALGORITHM = 1 */. In this example, OceanBase Database V3.2.X, executes ALGORITHM = 1 along with the surrounding SQL, regardless of the MySQL version number specified in the comment.

• The UUID_SHORT() function is supported. You can call the function to return a 64-bit unsigned integer.

  Example: SELECT UUID_SHORT();

Ease of use

• The RESTORE command restores a tenant to its latest state recorded in clog by default.

  A physical recovery operation takes effect on an entire tenant. The RESTORE TENANT command must contain an UNTIL clause that specifies the timestamp to which the tenant is to be restored. However, in most cases, you may want to restore a tenant to its latest state recorded in the clog. Therefore, in OceanBase Database V3.2.X, if you do not specify the timestamp, the RESTORE command restores the tenant to its latest state.

• The Schema History files can be recycled. This feature is provided because too many Schema History files can slow down the start of OBServers.

  The recycle interval of Schema History files is specified by the hidden parameter _schema_history_recycle_interval. The default value is 0, which specifies to disable the Schema History recycle feature.

• The recycle bin can be automatically purged.

  OceanBase Database provides a recycle bin for each tenant. When the disk space available is insufficient, the recycle bin is automatically purged based on a first-in-first-out (FIFO) strategy. The recyclebin_object_expire_time parameter is added to specify the expiration time of objects in the recycle bin.

• The major compaction, minor compaction, index creation, and data migration tasks are performed in the form of DAGs in OceanBase Database. Each DAG is an execution process of several tasks. To better display the DAGs of all background tasks, the virtual table __all_virtual_dag is provided to display the basic information of all DAGs in the DagScheduler. The dag_type and dag_key fields display the running tasks in a DAG. The dag_net_key field displays the basic information of the DagNet to which the DAG belongs. The dag_id and dag_status fields display the status of the DAG. To save memory, only a limited number of DAGs are displayed. The virtual table __all_virtual_dag_scheduler is provided to display the information of the DagScheduler, including the number of DAGs in each priority queue, the upper and lower limits of the number of threads, and the concurrency.

• A tenant_memory_limit field is added to the virtual table __all_virtual_tenant_ctx_memory_info to record memory usage of the tenant.

• When a PL statement execution error occurs, the system automatically returns the call stack and the corresponding row number in the error message for you to quickly locate the problematic code.

Cost-effectiveness

• The index selection is optimized.

  If a small amount of data is to be processed, the system automatically modifies the index pruning rules when generating the execution plan. The system also evaluates existing plans based on the execution time and the number of rows to be scanned, and then invalidates irrational plans.

• The refresh of the clog aggregation buffers can be triggered by the number of clogs to improve the execution performance of INSERT and UPDATE statements.

• The execution of the SELECT FOR UPDATE statement is optimized in MySQL mode to prevent the same row from being called twice, and the rescan operation from being performed in a TABLE SCAN.

• To achieve higher performance, markers are used to replace HashSet and remove duplicate expressions. Expressions marked by using markers are not added to the expression array.

• The COUNT(DISTINCT) operator is optimized. To group scalar values with distinct parameters, the values are pulled to locally calculate the aggregation results. Then, the results are pushed down.

• The execution efficiency of NESTED LOOP JOIN, SUM, DISTINCT, ORDER BY, and other operators is improved.

Stability

• A cluster deadlock can be released by rolling back the current statement of a transaction. Deadlocks in nested executions, stored procedures, triggers, and foreign keys can be detected. This feature is under constant improvement.

• Local route tables are automatically refreshed to ensure that upper-layer applications, such as those that handle transactions, clogs, and SQL queries, can quickly identify the new leader after a leader switchover in events such as network exceptions and node failures. This feature reduces the response time (RT) and prevents unnecessary errors. In previous versions, if the refresh of the location cache is triggered by errors, the RT of the first SQL query on the relevant partitions increases drastically after the cache changes. When an exception occurs to the communication between the local host and the location cache, the mechanism of triggering the refresh of the location cache based on timeouts tends to increase the RT of business SQL queries. As a result, the probability of timeout errors increases. To solve this issue, the following parameters are added in OceanBase Database V3.2.1:

  • enable_auto_refresh_location_cache: specifies to enable or disable the automatic refresh of the location cache.

  • auto_broadcast_location_cache_rate_limit: the maximum number of changed locations that can be automatically broadcast per second.

  • auto_refresh_location_cache_rate_limit: the maximum number of locations that are automatically refreshed per second.

• Strong consistency follower read is supported. After a leader OBServer node is switched to the follower role, the route table may not be updated in time. As a result, the strong consistency read requests are forwarded to the follower, and the OB_NOT_MASTER error occurs. In this case, the readability of a record is determined based on the snapshot time of the transaction and the value of weak_read_time. The hidden parameter _follower_snapshot_read_retry_duration is provided to set the read wait time.

• The network traffic is better controlled to optimize the usage of network bandwidth in scenarios such as clog synchronization, data migration, data synchronization to replicas, and static data copying in rebuilding operations.

• The status of the suspended observer process may not be detected based on the keepalive mechanism of the TCP protocol or the timeout mechanism in the upper layer. Therefore, a keepalive mechanism is added to libeasy to check and identify the status of a suspended observer process.

Database improvements

• You can manually collect optimizer statistics.

  The timely update of statistics optimizes execution plans and prevents SQL performance degradation due to inaccurate execution plans. In Oracle mode, functions such as GATHER_TABLE_STATS(), DELETE_TABLE_STATS(), LOCK_TABLE_STATS(), and SET_TABLE_STATS() are added to the DBMS_STATS package to collect statistics of tables, schemas, and partitions. In MySQL mode, an update is triggered by executing the ANALYZE TABLE statement.

• You can invalidate the execution plans of individual SQL statements.

  When an irrational SQL execution plan is found, the DBA can invalidate the plan based on the SQL ID.

  • The sys tenant can invalidate a specific or all SQL execution plans of a user tenant by executing the following statement: ALTER SYSTEM FLUSH PLAN CACHE [ [SQL_identifier] [db_list] tenant_list ][global].

  • An Oracle tenant can invalidate a specific SQL execution plan by calling the function DBMS_PLAN_CACHE.PURGE( SQL_ID VARCHAR2 NOT NULL, SCHEMA VARCHAR2 DEFAULT NULL GLOBAL BOOLEAN DEFAULT FALSE), or refresh all execution plans of the tenant by executing the following statement: ALTER SYSTEM FLUSH PLAN CACHE [global];

  • A MySQL tenant can invalidate a specific or all SQL execution plans of the tenant by executing the following statement: ALTER SYSTEM FLUSH PLAN CACHE [ [ SQL_identifier ] [db_list] ] [global];

• Tenant-level management of disk space limits is supported.

  The tenant-level tenant_disk_max_size parameter is provided to specify the maximum disk space that a tenant can use. If the disk space occupied by the tenant exceeds the value of tenant_disk_max_size, an INSERT operation returns an error.

• The storage space is optimized.

  To reduce the disk overhead for data files, the punch hole feature is provided for you to delete files that are no longer needed. The _enable_block_file_punch_hole parameter is provided to enable or disable the punch hole feature.

• Non-template-based subpartitions can be added to table groups.

  When you create or drop a table group in Oracle mode, you can specify whether to include RANGE-RANGE or LIST-LIST non-template-based subpartitions. Tables can be added to an existing table group. Partitions can be dropped.

• Automatic index encryption is supported.

  When TDE is enabled on the primary table, the index table is automatically encrypted by TDE.

• You can specify table_id to perform a minor freeze on a specific table.

  Example: alter system minor freeze table_id = '1234';

• Using the NOT NULL constraint in the Alter Table statement is supported.

• Tenant-level snapshot backup is supported. To use this feature, log archiving must be enabled. You can specify the backup directory. Example: ALTER SYSTEM BACKUP TENANT tenant1 TO "file:///ob_backup/";

Performance report

Test environment specifications:

Tested version:

Deployment mode:

Three physical servers are used to deploy a three-replica cluster in the 1:1:1 mode. In this mode, three OBServers are evenly distributed in three zones.

Sysbench OLTP benchmark

Tenant information:

• max_cpu = 22

• max_memory = 80 GB

• primary_zone = 'RANDOM'

Data volume:

A total of 30 non-partitioned tables are used. Each table contains 1,000,000 rows of data.

Results:

BenchmarkSQL TPC-C benchmark

Tenant information:

• max_cpu = 22

• max_memory = 80 GB

• primary_zone = 'RANDOM'

• locality = FFF

Test configuration:

• warehouses = 600

• loadWorkers = 30

• terminals=600

JDBC Driver:

oceanbase-client-1.1.10.jar

Table information:

The following tables are used in the same Partition Group: bmsql_warehouse, bmsql_district, bmsql_customer, bmsql_history, bmsql_new_order, bmsql_oorder, bmsql_order_line, and bmsql_stock. Each table is partitioned into 128 partitions.

The bmsql_item table is set to a replica table.

Results:

TPC-H benchmark

Tenant information:

• MAX_CPU = 80

• MIN_CPU = 80

• MIN_MEMORY = '500G'

• MAX_MEMORY = '500G'

Data volume:

1 TB

Results (in seconds):

Compatibility changes

Parameters

Behavior changes

• In Oracle mode, the SQL statement for data loading can be executed without running the GRANT READ ON DIRECTORY XX TO USER command.

• In Oracle mode, you cannot use UTF16 as a database character set.

• The restriction against creating tables in OceanBase system databases is removed.

Upgrade path

• To upgrade your database to OceanBase Database V3.1.2, you must first upgrade the database to V3.2.0 BP1 and then to V3.2.1. After that, you can upgrade the database to a version later than V3.2.1 in the future. However, this does not guarantee 100% success in business requests during the upgrade.

• If you upgrade from V2.2.77 to V3.1.2, make sure that the latest slog is produced before you upgrade to V3.2.0 BP1 and then to V3.2.1.

Supported components

We recommend that you use the following platforms or tools along with OceanBase Database V3.2.1.

Fixed issues

• The DEGREE field in DBA_INDEXES does not display the value that you specify for the degree of parallelism when you create an index.

• When you create a procedure in Oracle mode, the following error occurs: ORA-00979: not a GROUP BY expression. This issue occurs when you use clauses such as CASE WHEN.

  Examples:

  CREATE TABLE T1(C1 INT, C2 INT); SELECT CASE WHEN C1 IN (SELECT C1 FROM T1) THEN C1 ELSE C2 END FROM T1 GROUP BY CASE WHEN C1 IN (SELECT C1 FROM T1) THEN C1 ELSE C2 END;
  

• OBServer returns the following error when you call the OCILobWrite API to write data in Chinese: ORA-00600: internal error code, arguments: -4002, Invalid argument.

{#ub6d80ad6}

{#u7cb0acb3}

V3.2.1 BP1

Version information

• Release date: September 30, 2021

• Version: V3.2.1 BP1

Enhanced features

• Automatic verification and cache updates for the MIN and MAX file IDs of clog files are supported.
• The number of logs generated by the TMP.FILE files is reduced.
• PL allows you to access the data elements of an array by using placeholders.
• Tables can be encrypted by using the Chinese cryptographic algorithm (SM4).
• Array variables can be bound to ANONYMOUS and PROCEDURE blocks.

Fixed issues

• An error is returned if the DISTINCT TO_CHAR() function and the ORDER BY clause are executed on the same column in Oracle mode. Example: SELECT DISTINCT TO_CHAR(C1) FROM T1 ORDER BY C1;
• In Oracle mode, an error is returned during SQL execution if the OR and IN expressions in the statement contain an external link operator (+).
• When the first or second argument of the TO_DATE() or TO_TIMESTAMP() function is CONST NULL, the result is incorrect.
• The execution of user-defined functions (UDFs) consumes a large amount of memory.
• Global temporary tables cannot hit the plan cache after a session switchover.
• The creation of a global index fails after the observer process is terminated.
• After the statement execution in PL fails, the rollback of XA transactions is inconsistent with that in the Oracle database.