gv$ob_sql_audit 视图如何确认完整的事务上下文

问题现象

在 OceanBase 数据库 V4.2.1.x 环境内，查询 gv$ob_sql_audit 视图里只能看到 tx_id 字段，表示的是事务 id，无法区分一个事务内多条 SQL 的顺序。

问题原因

gv$ob_sql_audit 视图不支持查看事务内的 sql 顺序。

解决方法

1. 升级 OceanBase 到 V4.2.5.0 及其之后的版本。

2. 确认 gv$ob_sql_audit 视图中存在 seq_num 字段，表示的是事务中的语句序号。

具体示例如下：

1. 创建测试表。

   obclient(root@test1)[test]> create table t1 (c1 int);
   

2. 开始事务。

   obclient(root@test1)[test]> begin;
   

3. 插入数据。

   obclient(root@test1)[test]> insert into t1 values(1);
   obclient(root@test1)[test]> insert into t1 values(2);
   obclient(root@test1)[test]> insert into t1 values(3);
   

4. 保存。

   obclient(root@test1)[test]> commit;
   

5. 查询表 gv$ob_sql_audit 情况。

   obclient(root@test1)[test]> select * from gv$ob_sql_audit where query_sql like '%insert into t1%' and query_sql not like '%audit%';
   

   返回结果如下：

   +----------------+----------+------------+-------------+-----------------------------------+------------+----------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+---------+----------------------------------+--------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+-------+--------+------+--------+------+--------+------+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+----------+------------------+--------------+-----------------------+---------------+---------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+--------------+-------------+-----------------+----------------------------------+-----------+-------------------------------+------------------------------+------------+------------------+-------------------+--------------------+
   | SVR_IP         | SVR_PORT | REQUEST_ID | SQL_EXEC_ID | TRACE_ID                          | SID        | CLIENT_IP      | CLIENT_PORT | TENANT_ID | TENANT_NAME | EFFECTIVE_TENANT_ID | USER_ID | USER_NAME | USER_GROUP | USER_CLIENT_IP | DB_ID  | DB_NAME | SQL_ID                           | QUERY_SQL                | PLAN_ID | AFFECTED_ROWS | RETURN_ROWS | PARTITION_CNT | RET_CODE | QC_ID | DFO_ID | SQC_ID | WORKER_ID | EVENT | P1TEXT | P1   | P2TEXT | P2   | P3TEXT | P3   | LEVEL | WAIT_CLASS_ID | WAIT_CLASS# | WAIT_CLASS | STATE              | WAIT_TIME_MICRO | TOTAL_WAIT_TIME_MICRO | TOTAL_WAITS | RPC_COUNT | PLAN_TYPE | IS_INNER_SQL | IS_EXECUTOR_RPC | IS_HIT_PLAN | REQUEST_TIME     | ELAPSED_TIME | NET_TIME | NET_WAIT_TIME | QUEUE_TIME | DECODE_TIME | GET_PLAN_TIME | EXECUTE_TIME | APPLICATION_WAIT_TIME | CONCURRENCY_WAIT_TIME | USER_IO_WAIT_TIME | SCHEDULE_TIME | ROW_CACHE_HIT | BLOOM_FILTER_CACHE_HIT | BLOCK_CACHE_HIT | DISK_READS | RETRY_CNT | TABLE_SCAN | CONSISTENCY_LEVEL | MEMSTORE_READ_ROW_COUNT | SSSTORE_READ_ROW_COUNT | DATA_BLOCK_READ_CNT | DATA_BLOCK_CACHE_HIT | INDEX_BLOCK_READ_CNT | INDEX_BLOCK_CACHE_HIT | BLOCKSCAN_BLOCK_CNT | BLOCKSCAN_ROW_CNT | PUSHDOWN_STORAGE_FILTER_ROW_CNT | REQUEST_MEMORY_USED | EXPECTED_WORKER_COUNT | USED_WORKER_COUNT | SCHED_INFO | FUSE_ROW_CACHE_HIT | PS_CLIENT_STMT_ID | PS_INNER_STMT_ID | TX_ID    | SNAPSHOT_VERSION | REQUEST_TYPE | IS_BATCHED_MULTI_STMT | OB_TRACE_INFO | PLAN_HASH           | LOCK_FOR_READ_TIME | PARAMS_VALUE | RULE_NAME | PARTITION_HIT | TX_INTERNAL_ROUTING | TX_STATE_VERSION | FLT_TRACE_ID | PL_TRACE_ID | PLSQL_EXEC_TIME | FORMAT_SQL_ID                    | STMT_TYPE | TOTAL_MEMSTORE_READ_ROW_COUNT | TOTAL_SSSTORE_READ_ROW_COUNT | PROXY_USER | SEQ_NUM          | NETWORK_WAIT_TIME | PLSQL_COMPILE_TIME |
   +----------------+----------+------------+-------------+-----------------------------------+------------+----------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+---------+----------------------------------+--------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+-------+--------+------+--------+------+--------+------+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+----------+------------------+--------------+-----------------------+---------------+---------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+--------------+-------------+-----------------+----------------------------------+-----------+-------------------------------+------------------------------+------------+------------------+-------------------+--------------------+
   | 11.xxx.xxx.191 |     2882 |   55130894 |   797988770 | YB420BA2D9BF-00063112367D6710-0-0 | 3221711517 | 11.xxx.xxx.191 |       24422 |      1004 | test1       |                1004 |  200001 | root      |          0 | 11.xxx.xxx.191 | 500001 | test    | 5AB7C2585394BAD0EE04A39099728804 | insert into t1 values(1) |   23672 |             1 |           0 |             1 |        0 |     0 |      0 |      0 |         0 |       |        |    0 |        |    0 |        |    0 |     0 |           100 |           0 | OTHER      | MAX_WAIT TIME ZERO |               0 |                     0 |           0 |         1 |         1 |            0 |               0 |           0 | 1745892001754099 |        17087 |        0 |             1 |         18 |           1 |          1844 |        15214 |                     0 |                     0 |                 0 |             0 |             0 |                      0 |               0 |          0 |         0 |          0 |                 3 |                       0 |                      0 |                   0 |                    0 |                    0 |                     0 |                   0 |                 0 |                               0 |             4007992 |                     0 |                 0 | NULL       |                  0 |                -1 |               -1 | 20324872 |                0 |            2 |                     0 | NULL          | 3290318591324336132 |                  0 |              |           |             1 |                   0 |                0 |              | NULL        |               0 | E0EE813BFC75152B268AC7B461AE9DA4 | INSERT    |                             0 |                            0 |            | 1745892001743307 |                 0 |                  0 |
   | 11.xxx.xxx.191 |     2882 |   55130896 |   797988774 | YB420BA2D9BF-00063112367D6711-0-0 | 3221711517 | 11.xxx.xxx.191 |       24422 |      1004 | test1       |                1004 |  200001 | root      |          0 | 11.xxx.xxx.191 | 500001 | test    | 5AB7C2585394BAD0EE04A39099728804 | insert into t1 values(2) |   23672 |             1 |           0 |             1 |        0 |     0 |      0 |      0 |         0 |       |        |    0 |        |    0 |        |    0 |     0 |           100 |           0 | OTHER      | MAX_WAIT TIME ZERO |               0 |                     0 |           0 |         0 |         1 |            0 |               0 |           1 | 1745892001775296 |          641 |        0 |             2 |         36 |           0 |           146 |          439 |                     0 |                     0 |                 0 |             0 |             0 |                      0 |               0 |          0 |         0 |          0 |                 3 |                       0 |                      0 |                   0 |                    0 |                    0 |                     0 |                   0 |                 0 |                               0 |              245760 |                     0 |                 0 | NULL       |                  0 |                -1 |               -1 | 20324872 |                0 |            2 |                     0 | NULL          | 3290318591324336132 |                  0 |              |           |             1 |                   0 |                0 |              | NULL        |               0 | E0EE813BFC75152B268AC7B461AE9DA4 | INSERT    |                             0 |                            0 |            | 1745892001769425 |                 0 |                  0 |
   | 11.xxx.xxx.191 |     2882 |   55130897 |   797988775 | YB420BA2D9BF-00063112367D6712-0-0 | 3221711517 | 11.xxx.xxx.191 |       24422 |      1004 | test1       |                1004 |  200001 | root      |          0 | 11.xxx.xxx.191 | 500001 | test    | 5AB7C2585394BAD0EE04A39099728804 | insert into t1 values(3) |   23672 |             1 |           0 |             1 |        0 |     0 |      0 |      0 |         0 |       |        |    0 |        |    0 |        |    0 |     0 |           100 |           0 | OTHER      | MAX_WAIT TIME ZERO |               0 |                     0 |           0 |         0 |         1 |            0 |               0 |           1 | 1745892001777868 |          433 |        0 |             2 |         76 |           1 |            68 |          273 |                     0 |                     0 |                 0 |             0 |             0 |                      0 |               0 |          0 |         0 |          0 |                 3 |                       0 |                      0 |                   0 |                    0 |                    0 |                     0 |                   0 |                 0 |                               0 |              245760 |                     0 |                 0 | NULL       |                  0 |                -1 |               -1 | 20324872 |                0 |            2 |                     0 | NULL          | 3290318591324336132 |                  0 |              |           |             1 |                   0 |                0 |              | NULL        |               0 | E0EE813BFC75152B268AC7B461AE9DA4 | INSERT    |                             0 |                            0 |            | 1745892001776611 |                 0 |                  0 |
   +----------------+----------+------------+-------------+-----------------------------------+------------+----------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+---------+----------------------------------+--------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+-------+--------+------+--------+------+--------+------+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+----------+------------------+--------------+-----------------------+---------------+---------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+--------------+-------------+-----------------+----------------------------------+-----------+-------------------------------+------------------------------+------------+------------------+-------------------+--------------------+
   3 rows in set
   

   说明

   即使在同一个事务内 seq_num 也不是连续的，但是会保证是递增的。

适用版本

OceanBase 数据库 V4.2.5 之前版本。