旺旺头条抽丝剥茧丨OB中的Session SQL Trace DBMS_MONITOR（类似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等工具。但在国产数据库中生态工具并不完善，这里简单记录 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. |
The sampling percentage, with a range of [0,1]. | |
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的粒度最精细。
调用DBMS_MONITOR.OB_SESSION_TRACE_ENABLE开启跟踪
session 1执行SQL过程中，session 2跟踪
确保目标会话处于活动状态
# session 1obclient(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的Trace。如果session_id为NULL，表示当前Session。语法如下：
DBMS_MONITOR.OB_SESSION_TRACE_DISABLE(session_id IN BINARY_INTEGER);如果不使用SESSION ID，也可以使用Client Identifier,Module/Action 等。希望后期能做解读trace file的工具。
数据驱动，成就未来，云和恩墨，不负所托！
云和恩墨创立于2011年，是业界领先的“智能的数据技术提供商”。公司以“数据驱动，成就未来”为使命，致力于将创新的数据技术产品和解决方案带给全球的企业和组织，帮助客户构建安全、高效、敏捷且经济的数据环境，持续增强客户在数据洞察和决策上的竞争优势，实现数据驱动的业务创新和升级发展。
自成立以来，云和恩墨专注于数据技术领域，根据不断变化的市场需求，创新研发了系列软件产品，涵盖数据库、数据库存储、数据库管理和数据智能等领域。这些产品已经在集团型、大中型、高成长型客户以及行业云场景中得到广泛应用，证明了我们的技术和商业竞争力，展现了公司在数据技术端到端解决方案方面的优势。
版权声明
本文仅作者转发或者创作，不代表旺旺头条立场。
如有侵权请联系站长删除
相关文章
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。