本文介绍以下内容。
- MySQL 模式或 Oracle 模式下如何设置及查看 SQL 审计相关集群参数、租户变量
- 如何获取获取查询物理执行计划所需的四元组 (ip、port、tenant_id、plan_id) 并查询 SQL 物理执行计划
- MySQL 模式或 Oracle 模式下如何最小化清理 PLAN CACHE
适用于包括但不限于如下场景:
- 需要记录或查询 SQL 执行历史
- 需要查询或对比 SQL 逻辑执行计划和物理执行计划
- SQL 优化或定位 SQL 瓶颈
适用版本
OceanBase 数据库 V2.2.x、V3.1.x、V3.2.x 版本
SQL Audit
开启集群 SQL Audit
enable_perf_event 用于设置是否开启性能事件的信息收集功能。
enable_sql_audit 用于设置是否开启 SQL 审计功能。
查询集群 SQL Audit 相关参数
使用 root@sys 登录 sys 租户。
mysql -h10.x.x.x -P2883 -uroot@sys#obcluster -p
通过如下 SQL 查询集群 SQL Audit 是否开启。
SHOW PARAMETERS LIKE 'enable_perf_event';
SHOW PARAMETERS LIKE 'enable_sql_audit';
设置集群 SQL Audit 相关参数
注意
- 生产环境如需修改请先与技术支持确认。
- 如是临时修改使用完后及时修改回原来的值。
使用 root@sys 登录 sys 租户。
mysql -h10.x.x.x -P2883 -uroot@sys#obcluster -p通过如下 SQL 查询并设置集群 SQL Audit 相关参数。
SHOW PARAMETERS LIKE 'enable_perf_event'; SHOW PARAMETERS LIKE 'enable_sql_audit'; ALTER SYSTEM SET enable_perf_event=true; ALTER SYSTEM SET enable_sql_audit=true; SHOW PARAMETERS LIKE 'enable_perf_event'; SHOW PARAMETERS LIKE 'enable_sql_audit';如是临时修改使用完后及时修改回原来的值。
SHOW PARAMETERS LIKE 'enable_perf_event'; SHOW PARAMETERS LIKE 'enable_sql_audit'; ALTER SYSTEM SET enable_sql_audit=false; ALTER SYSTEM SET enable_perf_event=false; SHOW PARAMETERS LIKE 'enable_perf_event'; SHOW PARAMETERS LIKE 'enable_sql_audit';
开启租户 SQL Audit
ob_enable_sql_audit 用于控制当前租户是否开启 SQL Audit 功能。
登录业务租户 (MySQL 模式或 Oracle 模式)。
obclient -h10.x.x.x -P2883 -uroot@mytenant#obcluster -p
查询租户 SQL Audit 相关变量
通过如下 SQL 查询租户 SQL Audit 是否开启。
SHOW VARIABLES LIKE '%ob_enable_sql_audit%';
设置租户 SQL Audit 相关变量
通过如下 SQL 查询并设置租户 SQL Audit 相关参数 (设置 Global 级别变量，重新连接或新连接生效)。
SHOW VARIABLES LIKE '%ob_enable_sql_audit%';
SET GLOBAL ob_enable_sql_audit=ON;
SHOW VARIABLES LIKE '%ob_enable_sql_audit%';
如是临时修改使用完后及时修改回原来的值。
SHOW VARIABLES LIKE '%ob_enable_sql_audit%';
SET GLOBAL ob_enable_sql_audit=OFF;
SHOW VARIABLES LIKE '%ob_enable_sql_audit%';
如何设置和查看 SQL 执行计划
注意
建议开启 plan cache 之前，先开启 SQL Audit。
设置变量 ob_enable_trace_log/ob_enable_plan_cache
ob_enable_trace_log用于设置是否使用 trace 日志。ob_enable_plan_cache用于设置是否打开 Plan Cache，打开表示 SQL 请求可以使用计划缓存，关闭时表示 SQL 请求不使用计划缓存。
首先登录业务租户（MySQL 模式或 Oracle 模式），以进行查询和设置变量。
obclient -h10.x.x.x -P2883 -uroot@mytenant#obcluster -p查询变量。
SHOW VARIABLES LIKE '%ob_enable_trace_log%'; SHOW VARIABLES LIKE '%ob_enable_plan_cache%';
设置变量。
设置 Session 级别变量（推荐）
设置 Session 级别变量，仅当前连接生效，重新连接需要重新设置。
SHOW VARIABLES LIKE '%ob_enable_trace_log%'; SHOW VARIABLES LIKE '%ob_enable_plan_cache%'; SET ob_enable_trace_log=ON; SET ob_enable_plan_cache=ON; SHOW VARIABLES LIKE '%ob_enable_trace_log%'; SHOW VARIABLES LIKE '%ob_enable_plan_cache%';测试中如需关闭变量，参考如下 SQL。
SHOW VARIABLES LIKE '%ob_enable_trace_log%'; SHOW VARIABLES LIKE '%ob_enable_plan_cache%'; SET ob_enable_trace_log=OFF; SET ob_enable_plan_cache=OFF; SHOW VARIABLES LIKE '%ob_enable_trace_log%'; SHOW VARIABLES LIKE '%ob_enable_plan_cache%';设置 Global 级别变量
设置 Global 级别变量，重新连接或新连接生效。
SET GLOBAL ob_enable_trace_log=ON; SET GLOBAL ob_enable_plan_cache=ON;测试中如需关闭 Global 级别变量（重新连接或新连接生效），参考如下 SQL。
SET GLOBAL ob_enable_trace_log=OFF; SET GLOBAL ob_enable_plan_cache=OFF;
EXPLAIN 查看逻辑执行计划
通过 EXPLAIN 查看执行计划时，对应 SQL (SELECT/INSERT/UPDATE/DELETE 等) 并不实际执行，所以可以放心执行 EXPLAIN 命令。
EXPLAIN INSERT INTO test(n1) VALUES(2);
EXPLAIN EXTENDED INSERT INTO test(n1) VALUES(2);
通过 root 用户登录 MySQL 租户。
obclient -h10.x.x.x -P2883 -uroot@obmysql#obcluster -p根据如下方式之一获取 (ip、port、tenant_id、plan_id)。
通过 trace_id
SELECT usec_to_time(request_time) request_time_s, t.svr_ip ip, t.svr_port port, t.tenant_id, t.plan_id, * FROM oceanbase.gv$sql_audit t WHERE trace_id = 'YB42AC1BCD4E-0005F8AB8D83F8E5-0-0' ORDER BY request_time DESC;通过 SQL
SELECT usec_to_time(request_time) request_time_s, t.svr_ip ip, t.svr_port port, t.tenant_id, t.plan_id, * FROM oceanbase.gv$sql_audit t WHERE query_sql LIKE '%INSERT INTO test%' ORDER BY request_time DESC;或
SELECT * FROM oceanbase.gv$plan_cache_plan_stat WHERE query_sql LIKE '%INSERT INTO test%';通过 plan_id
SELECT * FROM oceanbase.gv$plan_cache_plan_stat WHERE plan_id = 1093;
根据 (ip、port、tenant_id、plan_id) 获取实际执行计划。
注意：以上四个条件缺一不可。
SELECT * FROM oceanbase.gv$plan_cache_plan_explain WHERE ip='10.xx.xx.xx' AND port = 2882 and tenant_id=1001 and plan_id = 4780;
通过普通用户登录 Oracle 租户。
obclient -h10.x.x.x -P2883 -uroot@oboracle#obcluster -p根据如下方式之一获取 (ip、port、tenant_id、plan_id)。
通过 trace_id
SELECT to_char(to_date('1970-01-01', 'yyyy-mm-dd') + (request_time / 1000000 / 86400) + to_number(substr(tz_offset(sessiontimezone), 1, 3)) / 24, 'YYYYMMDD HH24:MI:SS') request_time_s, t.svr_ip, t.svr_port, t.tenant_id, t.plan_id, t.* FROM gv$sql_audit t WHERE trace_id = 'YB42AC1BCD4E-0005F8AB8D83FD79-0-0' ORDER BY request_time DESC;通过 SQL
SELECT to_char(to_date('1970-01-01', 'yyyy-mm-dd') + (request_time / 1000000 / 86400) + to_number(substr(tz_offset(sessiontimezone), 1, 3)) / 24, 'YYYYMMDD HH24:MI:SS') request_time_s, t.svr_ip, t.svr_port, t.tenant_id, t.plan_id, t.* FROM gv$sql_audit t WHERE query_sql LIKE '%INSERT INTO test%' ORDER BY request_time DESC;或
SELECT * FROM gv$plan_cache_plan_stat WHERE query_sql LIKE '%INSERT INTO test%';通过 plan_id
SELECT * FROM gv$plan_cache_plan_stat WHERE plan_id = 1376;
根据 (ip、port,、tenant_id、plan_id) 获取实际执行计划。
注意：以上四个条件缺一不可。
SELECT * FROM gv$plan_cache_plan_explain WHERE svr_ip='10.xx.xx.xx' AND svr_port = 2882 AND tenant_id=1165 AND plan_id = 769;
清理 PLAN CACHE
由于存在 PLAN CACHE，通过上述方式查看到的并不一定是第一次执行时的计划。
可以考虑清理 PLAN CACHE 后再查看执行计划。
为避免造成影响，此处建议仅根据 sql_id 清理 PLAN CACHE。
通过 root 用户登录 MySQL 租户。
obclient -h10.x.x.x -P2883 -uroot@obmysql#obcluster -p通过如下 SQL 根据 sql_id 清理 PLAN CACHE。
ALTER SYSTEM FLUSH PLAN CACHE sql_id='2DE6ADBEBA9523DC4D9D3C45D7B9A046' GLOBAL;
为避免造成影响，此处建议仅根据 sql_id 清理 PLAN CACHE。
通过普通用户登录 Oracle 租户。
obclient -h10.x.x.x -P2883 -uroot@oboracle#obcluster -p通过如下 SQL 根据 sql_id 清理 PLAN CACHE。
BEGIN
dbms_plan_cache.purge(sql_id => '5F0B1122EA84DC6CF43D6F39131D11AA',schema => 'ALVIN',global => true);
END;
/
