SQL FAQ

FAQ: Execute SQL operations

How do I locate errors in PL creation?

You can use the SHOW ERRORS command to view the error information during the creation of a stored procedure.

How do I analyze the error logs of PL?

You only need to pay attention to the error logs returned to the client. This is because OceanBase Database records the error information left during the Resolve process in the logs; and if the PL contains the EXPECTION OTHERS statement, an error message will be left in the logs when it is executed. Therefore, the information recorded in the PL error logs is inaccurate and can be ignored. You only need to pay attention to the error logs returned to the client.

How do I query the source code of already created PL objects?

You can query the source code of already created PL objects by querying the DBA_SOURCE, ALL_SOURCE, or USER_SOURCE views. The TEXT column contains the source code of the PL object.

Does MySQL mode support the INSERT ALL INTO syntax?

No. Currently, only Oracle mode supports the INSERT ALL INTO syntax, and MySQL mode does not support it.

How are resources allocated and isolated for tenants in SQL engines, transaction engines, etc.?

SQL engines and transaction engines are tenant-aware, and different tenants are completely isolated. Specifically:

• The plan cache of the SQL engine and the locks of the transaction engine are completely independent.
• CPU: The CPU usage of an SQL thread for a tenant is used to control the number of active SQL threads.
• Memory: The SQL memory and transaction memory of different tenants are managed separately. If the memory of one tenant is exhausted, it will not affect another tenant.
• Threads: The threads of the SQL engine and transaction engine for different tenants are completely independent. If the threads of one tenant are suspended, it will not affect another tenant.

What is batch execution in OceanBase Database?

When you use JDBC to interact with OceanBase Database, you can group multiple requests together and complete them with a single network transmission. This is known as batch execution, also referred to as "batch processing."

Why use batch execution?

Sometimes, batch execution is used to ensure data consistency. However, the primary benefit of using batch execution is improved performance, which is reflected in the following aspects:

• Batch statements are rewritten to enhance performance;
• Batch execution reduces the number of interactions with the database;
• OceanBase Database can perform optimization work upon receiving batch execution, further boosting performance.

Which classes or objects in JDBC can implement batch execution?

Both Statement and PrepareStatement can be used to implement batch execution.

What JDBC configuration properties need to be set for batch execution?

• Functionally, using batch execution requires setting rewriteBatchedStatements=TRUE;
• In terms of implementation and behavior, useServerPrepStmts determines different behaviors of batch execution;
• In terms of performance, cachePrepStmt, prepStmtCacheSize, prepStmtCacheSqlLimit, and maxBatchTotalParamsNum all contribute to performance improvements.

Here is a table explaining the configuration properties:

Which OceanBase Database parameters are related to batch execution?

The following parameters are related to batch execution:

What are the differences between a Statement and a PrepareStatement?

• Use a Statement object:

  conn = DriverManager.getConnection(obUrl);
  conn.setAutoCommit(false);
  Statement stmt = conn.createStatement();
  String SQL = "INSERT INTO test1 (c1, c2) VALUES (1, 'test11')";
  stmt.addBatch(SQL);
  String SQL = "INSERT INTO test1 (c1, c2) VALUES (2, 'test12')";
  stmt.addBatch(SQL);
  String SQL = "INSERT INTO test1 (c1, c2) VALUES (3, 'test13')";
  stmt.addBatch(SQL);
  int[] count = stmt.executeBatch();
  stmt.clearBatch();
  conn.commit();
  

• Use a PrepareStatement object:

  conn = DriverManager.getConnection(obUrl);
  conn.setAutoCommit(false);
  String SQL = "INSERT INTO TEST1 (C1, C2) VALUES (?, ?)";
  PreparedStatemen pstmt = conn.prepareStatement(SQL);
  int rowCount = 5, batchCount = 10;
  for (int k=1; k<=batchCount; k++) {
      for (int i=1; i<=rowCount; i++) {
          pstmt.setInt(1, (k*100+i));
          pstmt.setString(2, "test value");
          pstmt.addBatch();
      }
      int[] count = pstmt.executeBatch();
      pstmt.clearBatch();
  }
  conn.commit();
  pstmt.close();
  

The following table describes the different behaviors of batch execution when you use PrepareStatement and Statement objects. This table assumes that rewriteBatchedStatements=TRUE.

Use a PrepareStatement object:

Use a Statement object:

What types of batch execution are supported in OceanBase Database, and how are they optimized?

From the perspective of statements, OceanBase Database supports different batch execution methods for the INSERT, UPDATE, and DELETE statements. Specifically:

INSERT

• Scenario 1

  Use the PrepareStatement object:

  useServerPrepStmts	INSERT
  TRUE	The VALUES of multiple INSERT statements are concatenated into multiple "?" placeholders in one INSERT statement. For example: INSERT INTO TEST1 VALUES (?), (?),...,(?)

  In this scenario, the OceanBase Database server receives one COM_STMT_PREPARE request (with request_type=5) and one COM_STMT_EXECUTE request (with request_type=6) for the INSERT statement. From an optimization perspective, this approach offers the following benefits:

  • Only two communications are required to complete the batch execution of the INSERT statement.
  • The inherent properties of PrepareStatement can reduce compilation time.
  • If there are more executeBatch operations in the future and the cachePrepStmts and related parameters are set appropriately, the number of Prepare requests (with request_type=5) can be reduced, and only execute requests (with request_type=6) are needed.

• Scenario 2

  Use the PrepareStatement object:

  useServerPrepStmts	INSERT
  FALSE	The VALUES of multiple INSERT statements are concatenated into specific values in one INSERT statement. For example: INSERT INTO TEST1 VALUES (1), (2),...,(10)

  In this scenario, the OceanBase Database server receives one COM_QUERY request (with request_type=2) for the INSERT statement. From an optimization perspective, this approach offers the following benefit:

  • Only one communication is required to complete the batch execution of the INSERT statement.

• Scenarios 3 and 4

  Use the Statement object:

  useServerPrepStmts	INSERT	Scenario
  TRUE	Multiple separate INSERT statements are concatenated with ";" separators.	Scenario 3
  FALSE	Multiple separate INSERT statements are concatenated with ";" separators.	Scenario 4

  In scenarios 3 and 4, the OceanBase Database server receives a request that contains multiple INSERT statements concatenated with ";" separators and executes them sequentially. This approach also offers the following benefit:

  • Only one communication is required to complete the batch execution of the INSERT statement.

UPDATE

Use the PrepareStatement object:

Use the Statement object:

• If ob_enable_batched_multi_statement is not enabled, the batch execution of UPDATE statements in scenarios 1, 2, 3, and 4 will be executed sequentially on the OceanBase Database server without any special optimization.
• If ob_enable_batched_multi_statement is enabled, the OceanBase Database server will parse multiple UPDATE statements with consistent formats as a single statement and generate a batch physical plan based on the corresponding parameters and data distribution. This can significantly improve the efficiency of batch UPDATE operations. However, this feature requires an explicit transaction.

DELETE The current version does not support optimization for batch DELETE statements.

How to choose different configurations in different scenarios?

The following table describes the different behaviors of the Batch execution when you use the PrepareStatement and Statement objects. This table assumes that the rewriteBatchedStatements parameter is set to TRUE.

Use the PrepareStatement object:

Use the Statement object:

Batch INSERT Scenarios 1 and 2 can effectively improve the performance of Batch execution. We recommend that you use the following configurations:

• Scenario 1

  • JDBC object: PrepareStatement object

  • Server-side parameters:

    _ob_enable_prepared_statement=TRUE
    

  • JDBC configuration parameters:

    rewriteBatchedStatements=TRUE
    useServerPrepStmts=TRUE
    cachePrepStmts=TRUE
    prepStmtCacheSize=<specify a value based on your actual situation>
    prepStmtCacheSqlLimit=<specify a value based on your actual situation>
    maxBatchTotalParamsNum=<specify a value based on your actual situation>
    

• Scenario 2

  • JDBC object: PrepareStatement object

  • JDBC configuration parameters:

    rewriteBatchedStatements=TRUE
    useServerPrepStmts=FALSE
    

Batch UPDATE Scenarios 2, 3, and 4 use the text protocol for communication. Therefore, they can all take advantage of the batch processing of multiple UPDATE statements. We recommend that you use the following configurations:

• Scenario 2

  • JDBC object: PrepareStatement object

  • Server-side parameters:

    ob_enable_batched_multi_statement=TRUE
    _enable_static_typing_engine=TRUE
    

  • Server-side variables:

    _enable_dist_data_access_service=1
    

  • JDBC configuration parameters:

    rewriteBatchedStatements=TRUE
    useServerPrepStmts=FALSE
    allowMultiQueries=TRUE
    --This parameter is set to avoid differences in behavior between different versions of the JDBC driver.
    

• Scenarios 3 and 4

  • JDBC object: Statement object

  • Server-side parameters:

    ob_enable_batched_multi_statement=TRUE
    _enable_static_typing_engine=TRUE
    

  • Server-side variables:

    _enable_dist_data_access_service=1
    

  • JDBC configuration parameters:

    rewriteBatchedStatements=TRUE
    allowMultiQueries=TRUE
    --This parameter is set to avoid differences in behavior between different versions of the JDBC driver.
    

How to check whether Batch execution is effective in OceanBase Database?

The most common method is to check whether Batch execution is effective by using the gv$sql_audit view. The following examples describe different scenarios:

• If Batch INSERT is effective in Scenario 1, the following record will be displayed in the gv$sql_audit view:

    query_sql: insert into test_multi_queries (c1, c2) values (?, ?)
  request_type: 5
    ps_stmt_id: 1
  
    query_sql: insert into test_multi_queries (c1, c2) values (?, ?),(?, ?),(?, ?)
  request_type: 5
    ps_stmt_id: 2
  
    query_sql: insert into test_multi_queries (c1, c2) values (?, ?),(?, ?),(?, ?)
  request_type: 6
    ps_stmt_id: 2
  

• If Batch INSERT is effective in Scenario 2, the following record will be displayed in the gv$sql_audit view:

  query_sql: insert into test_multi_queries (c1, c2) values (1, 'PreparedStatement; rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true'),(2, 'PreparedStatement; rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true'),(3, 'PreparedStatement; rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true')
  

• If Batch UPDATE is effective in Scenario 2, the following record will be displayed in the gv$sql_audit view:

              query_sql: update test2 set c2='batch update1' where c1=1;update test2 set c2='batch update2' where c1=2;update test2 set c2='batch update3' where c1=3
              ret_code: 0
  is_batched_multi_stmt: 1
  

  Notice

  If ret_code = -5787 is returned, Batch UPDATE is ineffective. You must find the cause based on the preceding description.

What value does the executeBatch method return during Batch execution?

After the executeBatch method is called, an integer array of type int [] is returned. For Batch INSERT and Batch UPDATE:

• If OceanBase Client executes each operation in the Batch one by one, the array returns the number of rows modified by each operation in the Batch.
• If OceanBase Client executes the Batch as a whole, for example, the JDBC driver converts multiple INSERT statements into one INSERT statement with multiple values (Scenarios 1 and 2), or the UPDATE statements are executed as a Batch physical plan (Scenario 2), each element in the array returns -2, indicating that the execution is successful but the number of updated rows is unknown.

How to resolve the "large account" issue in SQL queries

• What is the "large account" issue:

  The "large account" issue refers to the significant performance differences in SQL queries due to different execution plans for the same SQL ID. For example, when an SQL statement is parameterized, the plan cache may store an execution plan that is not suitable for all scenarios. This can lead to the following impacts:

  When a system first executes an SQL statement (e.g., select * from items where store = 'taobao';), the execution plan (p1) may be a full table scan, which is suitable for scenarios with a very low selectivity.

  If a subsequent parameterized SQL statement (e.g., select * from items where store = 'xiaomaibu';) is executed with a high selectivity, the original plan p1 may no longer be the optimal solution, leading to a performance decline.

• How to identify "large account" SQL:

  The key to identifying "large account" SQL is to monitor the fluctuations in logical read rows, affected rows, and returned rows. The specific identification rules include:

  • The fluctuation in logical read rows exceeds 1,000 rows.
  • The fluctuation in affected rows exceeds 1,000 rows.
  • The fluctuation in returned rows exceeds 10,000 rows.

  Users can manually analyze the execution records and statistics in views such as v$ob_sql_audit to identify SQL statements that exhibit the "large account" issue.

• How to resolve the "large account" issue:

  • Disable the plan cache

    1. Disable plan cache for a single SQL statement:

       • Use the USE_PLAN_CACHE hint, for example:

         SELECT /*+ USE_PLAN_CACHE(none) */ * FROM items WHERE store = 'taobao';
         

       • Or set the session variable ob_enable_plan_cache:

         SET ob_enable_plan_cache = 0;
         

    Both methods require modifying the SQL statements in the application. If the issue is discovered after the application is deployed, a new version of the application must be released to address it. You can also use the SQL Outline Binding Hint method, where the database administrator adds the hint to the SQL statement.

    2. Use SQL Outline Binding Hint: Control the execution plan of SQL statements using SQL outlines, managed by the DBA.

       CREATE OUTLINE otl_no_plan_cache1
       ON select /*+ USE_PLAN_CACHE(NONE) */ * from items where store = 'taobao';
       # Or use SQL_ID; SQL_ID can be found in views like V$OB_SQL_AUDIT
       CREATE OUTLINE otl_idx_c2
       ON 'ED570339F2C856BA96008A29EDF04C74'
       USING HINT /*+ USE_PLAN_CACHE(NONE) */;
       

• Enable SQL Plan Management (SPM)

  In OceanBase Database V4.2.1 and later, it is recommended to enable SPM. This mechanism can alleviate the "large account" issue in certain scenarios, especially when there is a single acceptable plan among multiple execution plans. SPM's automatic evolution ensures that execution plans improve positively when the plan cache is cleared, enhancing query performance.

• Address performance degradation caused by "large account" SQL

  • Manual tuning methods: When there is no suitable single execution plan, you can manually tune the SQL statements as follows:

    • Add indexes: Build indexes to improve query performance.
    • cursor_sharing_exact hint: Add this hint to large account SQL statements to ensure they receive independent execution plans.

  • Limit resource consumption: To prevent large account queries from affecting system performance, you can use the following methods to limit resource consumption:

    1. Control maximum concurrency: Use the MAX_CONCURRENT hint, for example:

       CREATE OUTLINE otl_sql_throttle1 ON 'SQL_ID' USING HINT /*+ MAX_CONCURRENT(10) */;
       

    2. SQL-level resource isolation: Bind large account queries to a resource group to limit their resource consumption during execution, for example:

       CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
           ATTRIBUTE => 'column',
           VALUE => 'items.store = \'taobao\'',
           CONSUMER_GROUP => 'slow_group');
       

       You can also use hints to specify a resource group for specific SQL statements:

       select /*+ RESOURCE_GROUP('slow_group') */ * from items where store = 'taobao';
       

    3. Automatically isolate large queries: Configure the large_query_threshold parameter to identify large queries and limit their CPU usage, ensuring small queries are prioritized. If both large and small queries are present, large queries can use up to 30% of the tenant's worker threads. This percentage can be adjusted using the large_query_worker_percentage parameter.

FAQ about SQL tuning

Poor physical design of the database lowers query performance

The performance of a query is largely affected by the physical design of the database, including schema information of the accessed objects. For example, for a secondary index, if the projected columns are not included in the index columns, the system needs to access the primary table by using the table lookup mechanism, which greatly increases the query cost. In this case, you can add the projected columns to the index columns to form a "covering index" to avoid the table lookup.

System load affects the response time of a single SQL statement

The overall system load not only affects the system throughput but also causes the response time of a single SQL statement to change. The SQL engine of OceanBase Database adopts a queue model. If all available threads are occupied when a user request is processed, the new request is queued in the request queue until a thread completes the current request. The time spent in the queue can be found in the (G)V$OB_SQL_AUDIT view.

Incorrect execution plan selection due to defects in the cost model

The built-in cost model of OceanBase Database is an inherent logic of the server. The optimal execution plan depends on this cost model. Therefore, if an incorrect execution plan is selected due to defects in the cost model, you can only ensure the selection of the "correct" execution plan by using execution plan binding.

Routing feedback logic error between the client and the server

One of the main functions of obproxy is to route SQL queries to the appropriate server node. Specifically, if a user query does not specify the weak-consistency read attribute, the proxy needs to route it to the primary node of the involved table (or specific partition) to avoid secondary forwarding by the server node. Otherwise, the proxy forwards the query to the appropriate node based on the predefined rules. Due to the loosely coupled architecture between the proxy and the server, the cached physical distribution information on the proxy may not be refreshed in a timely manner, leading to incorrect routing. The following scenarios may cause changes in routing information:

• Re-election of the primary node due to load balancing
• Re-election of the primary node due to unstable network
• Re-election of the primary node due to server online/offline or major compactions

When a large number of remote executions are found in the SQL audit or execution plan cache, consider whether these scenarios apply. The client has a routing feedback mechanism. If an error occurs, the client actively refreshes the data physical distribution information, and the routing selection will return to normal.

Index monitoring FAQ

Does index monitoring affect performance?

Yes, but the impact is negligible in the default (SAMPLED) mode.

Is the data accurate in the sampled mode? For example, will a query result be recorded if it is executed once?

Not necessarily. Sampling is used to filter out some data, so the records of some queries may be discarded.

Does index monitoring on a standby database only collect data from itself or synchronize data from the primary database?

Index monitoring is tenant-level. A standby database can view data synchronized from the primary database but cannot write to it.

FAQ about partition exchange

Why does an error occur when I perform a partition exchange in Oracle mode of OceanBase Database if the partitioned and non-partitioned tables have the same global indexes?

In Oracle mode of OceanBase Database, if you do not specify the local keyword when you create an index, a global index is created by default. To perform a partition exchange, the local indexes of the partitioned table must match the global indexes of the non-partitioned table. Otherwise, the partition exchange will fail.

FAQ about replicated tables

What is a replicated table?

A replicated table is a special type of table supported by OceanBase Database. This table allows you to read the latest data modifications from any replica. It is recommended for scenarios with low write frequency and high requirements for read latency and load balancing.

What are the applicable scenarios for replicated tables?

Scenario 1: Low write frequency and high requirements for read latency and load balancing

When the data volume of a table is not large but the access frequency is very high, using a regular table may lead to hotspots, affecting performance. In such cases, you can choose to use a replicated table.

Common scenarios for replicated tables:

• Configuration tables used by businesses to read configuration information.
• Tables storing exchange rates in financial scenarios that are updated only once a day.
• Tables storing branch or outlet information in banks, where new records are rarely added.

Best practices for replicated tables:

1. When creating a replicated table, it is recommended to create them as needed rather than creating a large number of replicated tables in a tenant.
2. When writing data to a replicated table, it is not recommended to perform write and read operations within the same transaction.
3. When querying a replicated table, if there is a JOIN query, design the query (Query) SQL in the order of joining a regular table with a replicated table.

Scenario 2: Tables that cannot be set as partitioned tables and frequently join with partitioned tables

Due to business logic considerations, some tables cannot be split into partitioned tables or do not need to be split (for example, queries do not have obvious partitioning conditions), but they frequently join with partitioned tables. To reduce unnecessary cross-node distributed queries, you can set these tables as replicated tables. Each node supports strong reads, regardless of which node the primary replica of the partitioned table is located on, it can join with the replicas of the replicated table on the same node.

What are the misuses of replicated tables?

Misuse 1: Setting one partitioned table as a replicated table to avoid cross-node joins between two partitioned tables

To improve performance, blindly setting partitioned tables in queries to replicated tables is not scientific. These tables may not be suitable for replicated tables, and the expected results may not be achieved.

• If a table has frequent write operations, writing to a replicated table has performance costs. The data needs to be synchronized to all replicas. In theory, this synchronization is concurrent. However, if some nodes have slow synchronization due to network issues, it will lead to a decline in the overall write performance of the replicated table.
• If the data volume of a replicated table is very large, each node has a replica of the table, which will occupy a large amount of storage space.

Misuse 2: Transactions containing both writes and reads to a replicated table

Transactions that are purely write-only or purely read-only are recommended. You only need to consider whether the business can tolerate write latency.

If a transaction contains both writes and reads to a replicated table, because reads require the leader, all replicated tables in the transaction will degrade to regular tables, losing the advantage of being able to read from the local replica.

Misuse 3: Joining a replicated table with a regular table

The routing rule for multiple-table JOIN queries in ODP (obproxy) is to route based on the first table parsed. If the first table is a replicated table, a random replica is selected. If this node is not the leader of the regular table, remote routing will occur, affecting performance.

If your business involves joining a replicated table with a regular partitioned table, it is recommended to adjust the JOIN order to join the regular partitioned table with the replicated table. This way, the query will be routed based on the regular partitioned table, generating a local plan as expected and optimizing performance.

Misuse 4: Setting a replicated table as a partitioned table

Replicated tables are already replicated on all nodes, so there is no need to set them as partitioned tables.

FAQ about auto-increment columns

In what scenarios do you need to set the data type of an auto-increment column to BIGINT?

• The business data grows rapidly and needs to be retained for a long period.
• The business does not care whether the table is created with the BIGINT or INT data type.
• The leader is scattered, and the probability of switching the leader (for example, due to a failure or random load balancing) increases. In this case, the probability of auto-increment column value jumps increases.
• In the NOORDER mode, you must explicitly specify the auto-increment column value.

In what scenarios can you keep the data type of an auto-increment column as INT?

• The business data volume is much smaller than the upper limit of INT.
• The business is migrated from MySQL, and the INT data type must be used to avoid compatibility issues with the application.
• In a single-machine scenario, the leader switching scenario is rare.
• You have monitoring and O&M capabilities and can handle the situation when the number of auto-increment values approaches the upper limit. For example, you can rebuild the table to migrate the data or change the data type from INT to BIGINT (from V4.2.2, changing the column type from INT to BIGINT is an online DDL operation).

In what scenarios can you change the auto-increment column to NOORDER?

• If you do not have a requirement for the ordered auto-increment column and want to optimize the performance of high-concurrency operations, you can change the ORDER mode to the NOORDER mode.
• If you have a requirement for the ordered auto-increment column, but all leaders are on the same OBServer node, and you want to optimize the performance of high-concurrency operations, you can change the ORDER mode to the NOORDER mode.

In what scenarios can you reduce the value of the auto_increment_cache_size parameter?

• The auto-increment column value jump problem is prominent.
• The business traffic is low.
• The performance is not sensitive.
• You have performance requirements, but the system is in the single-node mode and all leaders are concentrated on one node.