Diagnostics Transaction

OceanBase Cloud Platform (OCP) allows you to view basic information and associated SQL statements of large transactions. You can use the transaction diagnostics feature to identify and analyze large transactions.

Prerequisites

The OceanBase version is V3.2.x or later so that the Log Volume of Single Participant field is available.

Procedure

You can diagnostics transaction by using one of the following methods.

• Method 1:

  1. Log on to the OceanBase Cloud Platform (OCP) console.

  2. In the left-side navigation pane, click Autonomous Services to go to the Real-time Diagnostics page.

  3. Click Transaction Diagnostics tab.

• Method 2:

  1. Log on to the OceanBase Cloud Platform (OCP) console.

  2. To go to the O&M page of the tenant.

  3. In the left-side navigation pane, click Transaction Diagnostics to go to the Transaction Diagnostics page.

The following takes Method 1 as an example.

1. Log on to the OceanBase Cloud Platform (OCP) console.

2. In the left-side navigation pane, click OceanBase Autonomy Service.

3. In the Cluster Details section, click the name of the target cluster.

4. On the Transaction Diagnostics tab of the Real-time Diagnostics page, select a tenant from the drop-down list to view its transaction diagnostic information.

   • By default, the system displays the transactions whose execution time is greater than 500 ms or transactions whose log volume of a single participant exceeds 0.5 MB. You can specify other execution time or log volume ranges to query transactions.

   • Turn on Auto Refresh . The transaction list on the page will be automatically refreshed based on the refresh frequency. You can set the refresh frequency to 5s, 10s, or 30s. The default refresh frequency is 10s.

   

• View transactions.

  You can view transactions by transaction status. Transactions are divided into long-running transactions, suspended transactions, and other transactions by status. The transaction list displays information of the running large transactions. The list contains the following columns: Transaction Hash, Session ID, Transaction Type, Affected Rows, Execution Time Elapsed, and Maximum Log Volume of Single Participant. The following table describes the columns of the transaction list.

  Column	Description
  Transaction Hash	The unique ID of a transaction.
  Session ID	The session ID. For more information, see Manage tenant sessions.
  Transaction Type	The transaction type. Valid values: XA Transaction, Distributed Transaction, and Ordinary Transaction.
  Affected Rows	The number of rows affected in the execution of all SQL statements.
  Execution Time Elapsed (ms)	The time for which the transaction has been executed.

  

• View details of a transaction.

  Click the hash of a specific transaction. The Transaction Details page appears. You can view the basic information, execution information, and details of executed SQL statements of the transaction on the page.

  • Click Close Transaction in the upper-right corner to close the session of the transaction. Only ordinary transactions and distributed transactions in the Long-running Transactions list can be closed.

  • Click the SQL text in the SQL Statement with Longest Execution Time , SQL Statement with Most Affected Rows , or Details About Executed SQL Statements field to view the details of the SQL statement.

• Close transactions.

  On the Transactions page, select the transaction to be closed and click Close Transaction in the corresponding Actions column; or select multiple transactions and click Batch Close Transactions . In the dialog box that appears, click OK to close the session of the selected transaction or transactions. After the session of a transaction is closed, the transaction is rolled back and the session connection is also terminated.

  Notice

  Only ordinary transactions and distributed transactions in the Long Transactions list can be closed.