在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等。
不过，国产数据库中的生态工具并不完善，本文将简单介绍OceanBase数据库中会话级SQL跟踪（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: ·ALL: Print trace for all sampled queries. ·ONLY_SLOW_QUERY: Print trace only for slow queries. ·SAMPLE_AND_SLOW_QUERY: Print trace for slow queries, while other requests are printed based on the probability of _print_sample_percentage. |
level：打印日志的粒度。目前支持三个粒度等级，其中Level1为模块级别的粗粒度，Level3的粒度最精细。
开启跟踪
下面通过两个会话演示实操：会话1执行SQL，会话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.shWelcome to the OceanBase. Commands end with ; or \g.Your OceanBase connection id is 275746Server 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 allowedat oceanbase.DBMS_MONITOR.OB_SESSION_TRACE_ENABLE , line : 6, col : 1obclient(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/loggrep [trace_id] observer.log关闭跟踪
DBMS_MONITOR包的OB_SESSION_TRACE_DISABLE过程可根据Session ID关闭指定会话的跟踪，若session_id为NULL则关闭当前会话的跟踪，语法如下：
DBMS_MONITOR.OB_SESSION_TRACE_DISABLE(session_id IN BINARY_INTEGER);除了指定Session ID，该功能还支持通过Client Identifier、Module/Action 等方式定位会话。期待后续能推出专门解读trace file的配套工具。
张维照
云和恩墨资深数据库技术顾问
前Oracle ACE，2006年起从事数据库管理工作，2009年起从事Oracle DBA维护工作，十余年来专注于数据库技术和架构的研究，热衷于Oracle数据库故障诊断、性能优化、内部原理、新特性的学习与分享，在个人博客中分享了大量的学习和案例经验。现投身国产数据库推广与落地。
ENMOTECH
国产化替代浪潮下，数据库生态正发生深刻变革。面对层出不穷的国产数据库产品与技术，学习方向该如何抉择？是紧跟趋势布局国产数据库，还是专注传统技术深耕？来听听张维照老师的专业判断与实用指引。
数据驱动，成就未来，云和恩墨，不负所托！
云和恩墨创立于2011年，是业界领先的“智能的数据技术提供商”。公司以“数据驱动，成就未来”为使命，致力于将创新的数据技术产品和解决方案带给全球的企业和组织，帮助客户构建安全、高效、敏捷且经济的数据环境，持续增强客户在数据洞察和决策上的竞争优势，实现数据驱动的业务创新和升级发展。
自成立以来，云和恩墨专注于数据技术领域，根据不断变化的市场需求，创新研发了系列软件产品，涵盖数据库、数据库存储、数据库管理和数据智能等领域。这些产品已经在集团型、大中型、高成长型客户以及行业云场景中得到广泛应用，证明了我们的技术和商业竞争力，展现了公司在数据技术端到端解决方案方面的优势。
