SQL statements

SQL execution

How do I locate errors in PL object creation?

You can execute the SHOW ERRORS statement to view the information about errors that occurred when stored procedures are created.

How do I analyze PL error logs?

You only need to pay attention to the error logs returned to the client. OceanBase Database logs the information about errors that occurred during resolve operations. If the EXPECTION OTHERS statement is contained in PL, error information is recorded in logs when this statement is executed. Therefore, the information recorded in 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 routing SQL for a PL object?

You can query the sys.all_virtual_routine_agent table for the routing SQL statement for a PL object. Parameters:

• ROUTINE_NAME: the name of the route.
• ROUTINE_TYPE: the type of the route.
• ROUTINE_BODY: the SQL statement being routed.

How do I query the source code of a created PL object?

You can query the DBA_SOURCE, ALL_SOURCE, or USER_SOURCE view for the source code of a created PL object. The value in the TEXT column is the source code of the PL object.

Is the syntax INSERT ALL INTO supported in the MySQL mode?

No, this syntax is not supported in MySQL mode. Currently, the INSERT ALL INTO syntax is supported only in Oracle mode.

How do the SQL engine and transaction engine allocate and isolate resources for tenants?

Both the SQL engine and transaction engine isolate resources among tenants. Notes:

• The plan cache of the SQL engine and the locks of the transaction engine are totally independent of each other.
• CPU: The CPU resources available for the SQL threads of a tenant are limited to control the number of active SQL threads.
• Memory: The SQL memory and transaction memory of different tenants are separately managed. If the memory of a tenant is used up, other tenants are not affected.
• Thread: The threads of the SQL engine and transaction engine of different tenants are totally independent of each other. If the threads of a tenant are suspended, other tenants are not affected.

What is batch execution in OceanBase Database?

When you use Java Database Connectivity (JDBC) in OceanBase Database, you can place multiple requests in the same group and transmit the requests at the same time. This is called batch execution or batch processing.

Why is batch execution used?

Sometimes, batch execution is used for data consistency. More often, batch execution is used to improve performance from the following aspects:

• Batch statements are rewritten to improve performance.
• Batch execution can reduce the number of interactions with the database.
• When OceanBase Database receives a batch execution request, it can perform optimization to further improve the performance.

Which classes/objects in JDBC are suitable for batch execution?

Both statements and prepared statements are suitable for batch execution.

Which JDBC attributes do I need to set to use batch execution?

• In terms of features, you must set rewriteBatchedStatements to TRUE to use batch execution.
• In terms of implementation and behavior, useServerPrepStmts determines the batch execution behavior.
• In terms of performance, cachePrepStmt, prepStmtCacheSize, prepStmtCacheSqlLimit, and maxBatchTotalParamsNum can all improve the performance.

The following table describes the attributes.

Which parameters in OceanBase Database are related to batch execution?

The following table describes the parameters related to batch execution.

What are the differences between statements and prepared statements in terms of behavior and use methods?

• Statement:

  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();
  

• Prepared statement:

  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 tables describe the batch execution behavior for prepared statements and statements when rewriteBatchedStatements is set to TRUE.

Prepared statement:

Statement:

What are the batch execution types in OceanBase Database? What are their optimization measures on requests?

In OceanBase Database, the processing of batch execution on INSERT, UPDATE, and DELETE statements is different.

INSERT

• Scenario 1 | useServerPrepStmts | INSERT | | --- | --- | | TRUE | The values of multiple INSERT statements are separated with commas (,) in one INSERT statement in the INSERT INTO TEST1 VALUES (?), (?), ..., (?) format. |

  In scenario 1, an OBServer node receives a COM_STMT_PREPARE (request_type=5) request and a COM_STMT_EXECUTE (request_type=6) request for the INSERT statement. The benefits are:

  • The batch execution of the INSERT statement is completed in two communications.
  • The natural attributes of a prepared statement help reduce the compilation time.
  • If more batch execution requests are initiated and cachePrepStmts and related parameters are properly set, the number of Prepare requests (request_type=5) can be reduced and only the Execute request (request_type=6) needs to be executed.

• Scenario 2 | useServerPrepStmts | INSERT | | --- | --- | | FALSE | The values of multiple INSERT statements separated with commas (,) in one INSERT statement in the INSERT INTO TEST1 VALUES (1), (2), ...,(10) format.

  In scenario 2, the OBServer node receives a COM_QUERY request (request_type=2) of the INSERT statement. The benefits are:

  • The batch execution of the INSERT statement is completed in only one communication.

• Scenario 3/4 | useServerPrepStmts | INSERT | Scenario | | --- | --- | --- | | TRUE | Multiple independent INSERT statements are separated with semicolons (;). | Scenario 3 | | FALSE | Multiple independent INSERT statements are separated with semicolons (;). | Scenario 4 | In scenarios 3 and 4, the OBServer node receives a request of multiple INSERT statements that are separated with semicolons (;) and executes them one by one. The benefits are:

  • The batch execution of the INSERT statement is completed in only one communication.

UPDATE The following table describes the scenarios that UPDATE statements involve. | useServerPrepStmts| UPDATE | Scenario | | --- | --- | --- | | TRUE | The variables in multiple independent UPDATE statements are replaced with question marks (?). | Scenario 1 | | FALSE | Multiple independent UPDATE statements are separated with semicolons (;). | Scenario 2 |

• If ob_enable_batched_multi_statement is not set to TRUE, for batch UPDATE execution in scenarios 1, 2, 3, and 4, the OBServer node executes the statements one by one without optimization.
• If ob_enable_batched_multi_statement is set to TRUE, for the batch UPDATE execution in scenarios 2, 3, and 4, the OBServer node parses multiple statements in the same format as one statement and generate a batch physical plan based on the corresponding parameters and distribution of data. This effectively improves the batch execution efficiency. To use this feature, you must enable explicit transaction commit.

DELETE In the current version, no optimization is available for batch DELETE.

How do I choose different configurations in different scenarios?

The following tables describe the batch execution behavior for prepared statements and statements when rewriteBatchedStatements is set to TRUE.

Prepared statement:

Statement:

Batch INSERT Scenarios 1 and 2 can better showcase the benefits of batch execution and therefore are recommended.

• Scenario 1

  • JDBC object: prepared statement

  • Parameter on the server:

    _ob_enable_prepared_statement=TRUE
    

  • JDBC attribute:

    rewriteBatchedStatements=TRUE
    useServerPrepStmts=TRUE
    cachePrepStmts=TRUE
    prepStmtCacheSize=<Specify the value based on the actual situation.>
    prepStmtCacheSqlLimit=<Specify the value based on the actual situation.>
    maxBatchTotalParamsNum=<Specify the value based on the actual situation.>
    

• Scenario 2

  • JDBC object: prepared statement

  • JDBC attribute:

    rewriteBatchedStatements=TRUE
    useServerPrepStmts=FALSE
    

Batch UPDATE In scenarios 2, 3, and 4, the text protocol is used for communication and batch UPDATE execution can be used. Therefore, the configurations of these scenarios are recommended.

• Scenario 2

  • JDBC object: prepared statement

  • Parameter on the server:

    ob_enable_batched_multi_statement=TRUE
    _enable_static_typing_engine=TRUE
    

  • Variable on the server:

    _enable_dist_data_access_service=1
    

  • JDBC attribute:

    rewriteBatchedStatements=TRUE
    useServerPrepStmts=FALSE
    allowMultiQueries=TRUE
    --These settings aim to avoid behavioral differences among different JDBC driver versions.
    

• Scenario 3/4

  • JDBC object: statement

  • Parameter on the server:

    ob_enable_batched_multi_statement=TRUE
    _enable_static_typing_engine=TRUE
    

  • Variable on the server:

    _enable_dist_data_access_service=1
    

  • JDBC attribute:

    rewriteBatchedStatements=TRUE
    allowMultiQueries=TRUE
    --These settings aim to avoid behavioral differences among different JDBC driver versions.
    

How can I check whether batch execution takes effect in OceanBase Database?

You can query the gv$sql_audit view to observe whether batch execution takes effect.

• In scenario 1, if batch INSERT takes effect, you will see the following record 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
  

• In scenario 2, if batch INSERT takes effect, you will see the following record 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')
  

• In scenario 2, if batch UPDATE takes effect, you will see the following record 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
  

What is the value returned by the executeBatch() method during batch execution?

After the executeBatch() method is called, it returns an int [] array. For batch INSERT and batch UPDATE:

• If the statements are finally executed one by one on the OBClient, this array will return the number of rows updated by each operation in the batch.
• If the statements are executed as a whole on the OBClient, for example, the JDBC driver rewrites multiple INSERT statements as 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 this array returns -2, indicating that the execution is successful but the number of updated rows is unknown.

SQL tuning

Inaccurate data statistics

Query optimization relies on accurate data statistics. By default, the OceanBase optimizer collects some statistics during the major compaction of data, which may be inaccurate if you modify data a lot and causes latency in statistics updates. You can actively update the statistics through daily major compaction. In addition to collecting statistics, the optimizer also samples data from the storage layer based on query conditions for subsequent optimization and selection. Currently, OceanBase Database only supports sampling from local storage. If the data partitions are stored on remote nodes, the optimizer can only estimate the cost based on the collected statistics, which may lead to inaccurate cost estimation.

Decrease in query performance due to physical design of the database

The query performance depends largely on the physical design of the database, including the schema information of the object to be accessed. For example, in a query that involves a secondary index but the required projected column is not included in the indexed columns, the cost of the query is much higher because of the TABLE ACCESS BY INDEX PRIMARY KEY operations. In this case, you can add the projected column to the indexed columns to form a "covering index" to avoid table access by index primary key.

The response of an SQL query is affected by the system workload.

The overall load of the system not only affects the overall throughput of the system but also causes changes in the response time of an SQL query. In OceanBase Database, the SQL engine processes queries linearly, which means that new queries are put in a queue if all threads are occupied until a thread completes the current query. You can find the queue time of a query in the (G)V$OB_SQL_AUDIT view.

Incorrect selection of the execution plan due to cost model defects

OceanBase Database uses its built-in cost model. The selection of the optimal execution plan depends on this cost model. Therefore, if an incorrect plan is selected due to possible defects in this cost model, you can only bind the "correct" execution plan to avoid selecting the incorrect plan.

A routing feedback logic error occurred between the client and the server

One of the main functions of OceanBase Database Proxy (ODP) is to route SQL queries to the right server. Specifically, if you do not specify week-consistency read in the query, ODP routes the query to the server where the leader replica of a partition is located to avoid secondary forwarding between servers. Otherwise, the ODP forwards the query to the right server based on the preset rules. The loose coupling between an ODP and the server may cause latencies in refreshing the physical distribution of data cached on the ODP, resulting in routing errors. The routing information may be changed in the following circumstances:

• Re-election of the leader due to load balancing
• Re-election of the leader between servers due to network instability
• Re-election of the leader due to the addition or removal of an OBServer node or the rotating major compactions

You need to consider these circumstances when a large number of remote execution queries are found in the SQL audit or plan cache. The routing feedback logic exists between the client and the server, so the client refreshes the physical distribution of data when an error occurs. Then, the routing function is restored.