Oceanbase中的Session SQL Trace DBMS_MONITOR （Similar to Oracle 10046 event）
在oracle中诊断session级SQL执行跟踪是最常见的SQL Trace的方法有很多，如sql_trace、10046 event, DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION, DBMS_SUPPORT.START_TRACE, DBMS_MONITOR.SESSION_TRACE_ENABLE 还有Oracle 11g 后更加灵活的Events++ 语法， 甚至还有围绕trace file 解读的一堆工具，如trprof, TRCSESS , tvdxtat, 10046.pl, orasrp等工具。但在国产数据库中生态工具并不完善，之前记录过达梦的10053 event, 这里简单记录Oceanbase 数据库中配置 session SQL Trace使用DBMS_MONITOR的方法OB_SESSION_TRACE_ENABLE，功能基本雷同Oracle DBMS_MONITOR的 SESSION_TRACE_ENABLE.
— 仅用于OB 企业版
语法
DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
level IN INT,
sample_pct IN NUMBER,
record_policy IN VARCHAR2);| Parameter | Description |
|---|---|
| session_id | The session_id for enabling end-to-end diagnosis. If empty, it represents the session_id of the current session. |
| level | The trace collection level for end-to-end diagnosis, which is an integer. |
| sample_pct | The sampling percentage, with a range of [0,1]. |
| record_policy | The printing policy, including the following options:
|
level：打印日志的粒度。目前支持三个粒度等级，其中 Level1 为模块级别的粗粒度，Level3 的粒度最精细。
调用 DBMS_MONITOR.OB_SESSION_TRACE_ENABLE开启 跟踪
session 1执行SQL过程中，session 2跟踪
确保目标会话处于活动状态
# session 1 obclient(SYS@orcl)[SYS]> select count(*) from dba_tables; +----------+ | COUNT(*) | +----------+ | 196 | +----------+ 1 row in set (0.655 sec) obclient(SYS@orcl)[SYS]> select count(*) from dba_tables,dba_tables,dba_tables,dba_tables; --- 结果出来前跟踪 +------------+ | COUNT(*) | +------------+ | 1475789056 | +------------+ 1 row in set (6 min 22.082 sec) #session 2 [admin@observer1 ~]$ sh connorcl.sh Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 275746 Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient(SYS@orcl)[SYS]> select * from v$version; +---------------------------------------------------------------------------------------------------------------+--------+ | BANNER | CON_ID | +---------------------------------------------------------------------------------------------------------------+--------+ | OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) | 0 | +---------------------------------------------------------------------------------------------------------------+--------+ 1 row in set (0.034 sec) obclient(SYS@orcl)[SYS]> select * from dual; +-------+ | DUMMY | +-------+ | X | +-------+ 1 row in set (0.002 sec) obclient(SYS@orcl)[SYS]> show processlist; +------------+-----------+---------------------+------+---------+-------+--------+------------------------------------------------------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +------------+-----------+---------------------+------+---------+-------+--------+------------------------------------------------------------------+ | 3221534888 | SYS | 172.20.22.216:36646 | SYS | Sleep | 570 | SLEEP | NULL | | 3221496489 | SYS | 172.20.22.216:28608 | SYS | Sleep | 270 | SLEEP | NULL | | 3221491471 | STANDBYRO | 172.20.22.215:45160 | SYS | Sleep | 9 | SLEEP | NULL | | 275746 | SYS | 172.20.22.213:30850 | SYS | Query | 0 | ACTIVE | show processlist | | 3221551946 | SYS | 172.20.22.216:36644 | SYS | Sleep | 22470 | SLEEP | NULL | | 3221506606 | STANDBYRO | 172.20.22.214:58074 | SYS | Sleep | 393 | SLEEP | NULL | | 3221489078 | STANDBYRO | 172.20.22.213:8590 | SYS | Sleep | 0 | SLEEP | NULL | | 3221586354 | STANDBYRO | 172.20.22.213:35262 | SYS | Sleep | 663 | SLEEP | NULL | | 3221618043 | STANDBYRO | 172.20.22.213:22396 | SYS | Sleep | 2 | SLEEP | NULL | | 3221629216 | STANDBYRO | 172.20.22.214:59684 | SYS | Sleep | 1 | SLEEP | NULL | | 3222031496 | STANDBYRO | 172.20.22.213:45336 | SYS | Sleep | 0 | SLEEP | NULL | | 3222251396 | SYS | 172.20.22.216:53378 | SYS | Sleep | 2070 | SLEEP | NULL | | 3222251938 | SYS | 172.20.22.216:53380 | SYS | Sleep | 270 | SLEEP | NULL | | 3222240781 | SYS | 172.20.22.216:12076 | SYS | Sleep | 270 | SLEEP | NULL | | 3222175226 | STANDBYRO | 172.20.22.215:25498 | SYS | Sleep | 4 | SLEEP | NULL | | 3222231890 | STANDBYRO | 172.20.22.214:34154 | SYS | Sleep | 2 | SLEEP | NULL | | 3222012528 | STANDBYRO | 172.20.22.214:45740 | SYS | Sleep | 699 | SLEEP | NULL | | 3222074146 | STANDBYRO | 172.20.22.213:8458 | SYS | Sleep | 4 | SLEEP | NULL | | 3222172888 | STANDBYRO | 172.20.22.213:59154 | SYS | Sleep | 180 | SLEEP | NULL | | 3221896511 | STANDBYRO | 172.20.22.213:65266 | SYS | Sleep | 7717 | SLEEP | NULL | | 3221939020 | SYS | 172.20.22.216:37564 | SYS | Sleep | 270 | SLEEP | NULL | | 3221773566 | STANDBYRO | 172.20.22.214:51246 | SYS | Sleep | 3 | SLEEP | NULL | | 3221775524 | STANDBYRO | 172.20.22.213:51446 | SYS | Sleep | 0 | SLEEP | NULL | | 3221808181 | SYS | 172.20.22.216:23330 | SYS | Sleep | 270 | SLEEP | NULL | | 3221899722 | STANDBYRO | 172.20.22.214:10296 | SYS | Sleep | 338 | SLEEP | NULL | | 3221775365 | STANDBYRO | 172.20.22.215:40524 | SYS | Sleep | 9 | SLEEP | NULL | | 3221889661 | STANDBYRO | 172.20.22.213:65268 | SYS | Sleep | 0 | SLEEP | NULL | | 285297 | SYS | 172.20.22.213:12560 | SYS | Query | 10 | ACTIVE | select count(*) from dba_tables,dba_tables,dba_tables,dba_tables | +------------+-----------+---------------------+------+---------+-------+--------+------------------------------------------------------------------+ 28 rows in set (0.056 sec) obclient(SYS@orcl)[SYS]> show proxysession; +----------------------+--------+----------+--------+------+---------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+-----------------+ | proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl | server_protocol | +----------------------+--------+----------+--------+------+---------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+-----------------+ | 12399560778330603643 | 275746 | enmotest | orcl | sys | 172.20.22.213:51934 | SYS | 0 | 1 | MCS_ACTIVE_READER | 15564 | 15564 | 0 | OceanBase 2.0 | +----------------------+--------+----------+--------+------+---------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+-----------------+ 1 row in set (0.004 sec) obclient(SYS@orcl)[SYS]> CALL DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(285297, 3, 0.5, 'ONLY_SLOW_QUERY'); ORA-00600: internal error code, arguments: -4179, User session ID does not exist. not allowed at oceanbase.DBMS_MONITOR.OB_SESSION_TRACE_ENABLE , line : 6, col : 1 obclient(SYS@orcl)[SYS]> show proxysession attribute 285297; +----------------------------------+----------------------+----------------+ | attribute_name | value | info | +----------------------------------+----------------------+----------------+ | proxy_sessid | 12399560778330603645 | cs common | | cs_id | 285297 | cs common | | cluster | enmotest | cs common | | tenant | orcl | cs common | | user | sys | cs common | | host_ip | 172.20.22.213 | cs common | | host_port | 52816 | cs common | | db | SYS | cs common | | total_trans_cnt | 0 | cs common | | svr_session_cnt | 1 | cs common | | active | true | cs common | | read_state | MCS_ACTIVE_READER | cs common | | tid | 15569 | cs common | | pid | 15564 | cs common | | idc_name | | cs common | | modified_time | 0 | cs stat | | reported_time | 0 | cs stat | | hot_sys_var_version | 2 | cs var version | | sys_var_version | 11 | cs var version | | user_var_version | 1 | cs var version | | last_insert_id_version | 0 | cs var version | | db_name_version | 3 | cs var version | | server_ip | 172.20.22.214 | curr used ss | | server_port | 2881 | curr used ss | | server_sessid | 3221941901 | curr used ss | | ss_id | 265527 | curr used ss | | state | MSS_ACTIVE | curr used ss | | transact_count | 8 | curr used ss | | server_trans_stat | 1 | curr used ss | | hot_sys_var_version | 2 | curr used ss | | sys_var_version | 11 | curr used ss | | user_var_version | 1 | curr used ss | | last_insert_id_version | 0 | curr used ss | | db_name_version | 3 | curr used ss | | is_checksum_supported | 0 | curr used ss | | is_safe_read_weak_supported | 0 | curr used ss | | is_checksum_switch_supported | 1 | curr used ss | | checksum_switch | 1 | curr used ss | | enable_extra_ok_packet_for_stats | 1 | curr used ss | +----------------------------------+----------------------+----------------+ 39 rows in set (0.019 sec) obclient(SYS@orcl)[SYS]> CALL DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(3221941901, 3, 0.5, 'ONLY_SLOW_QUERY'); Query OK, 0 rows affected (0.003 sec)
跟踪完成后，可以在 OBServer 节点的日志目录下查看跟踪日志。
cd /home/admin/oceanbase/log grep [trace_id] observer.log
DBMS_MONITOR 包的 OB_SESSION_TRACE_DISABLE 过程根据 Session ID 关闭指定 Session 的 Trace。如果 session_id 为 NULL，表示当前 Session。语法如下：
DBMS_MONITOR.OB_SESSION_TRACE_DISABLE(session_id IN BINARY_INTEGER);
如果不使用SESSION ID，也可以使用Client Identifier,Module/Action 等。希望后期能做解读trace file的工具。
微信扫一扫，打赏作者吧～
目前这篇文章还没有评论(Rss)