SQL优化 | 精准区分 trace_id、sql_id、plan_id（二）

1、问题背景

在SQL优化过程中，经常遇到trace_id，sql_id，plan_id 这三个字段，笔者总是傻傻的分不清他们的区别；

下面我将着重介绍这三个ID值的区别，以及他们分别如何获取？

2、三种ID的区别

2.1 trace_id

trace_id是OceanBase数据库内部的SQL级别的ID;

默认值为Y0-0000000000000000-0-0。

它用于标识一条SQL的执行过程，是排查问题的重要环节。

当应用使用allowmultiqueries=true并将多条SQL拼接在一起发送到OceanBase数据库时，这些SQL会使用相同的trace_id。

注意：一个trace_id可能对应多条SQL语句。

绑定执行计划可参考OB 运维 | 如何有效使用 outline 功能？

2.2 sql_id（具体参数后的SQL语句）

sql_id用于唯一确定一条具体的SQL，是对特定SQL语句经过一系列转换得到的MD5值，返回128位的哈希值，类型为CHAR(32)。

它用于标识一条具体的SQL，同一条SQL的多次执行可能会有相同的SQL_ID和不同的REQUEST_ID。

注意：绑定执行计划可通过sql_id 来实现绑定，当SQL实现参数化，可通过sql_id 绑定这一类SQL的执行计划。

2.3 plan_id

用于在单个 OBServer 上唯一确定 plan_cache 中的一个 plan，是一个递增的值，由 plan_cache 模块进行管理。OBServer 重启后，plan_id 会重置。

plan_id 和 tenant_id 一起决定一个执行计划 。

2.4 三种ID值示例

-- # 4.x使用GV$OB_SQL_AUDIT
MySQL [(none)]> select trace_id,sql_id,plan_id from oceanbase.GV$SQL_AUDIT limit 1;
+-----------------------------------+----------------------------------+------+
| trace_id                          | sql_id                           | plan_id |
+-----------------------------------+----------------------------------+------+
| YB420ABA381C-00063097C87624F3-0-0 | 2351C88EB78C36BCCD2F397CF7C3901C |  9409 |
+-----------------------------------+----------------------------------+------+
1 row in set (0.01 sec)

3、分别如何获取

3.1 获取trace_id

-- 方式一：当前会话执行一条SQL
select last_trace_id();
-- 方式二：通过查询GV$SQL_AUDIT视图（3.x使用GV$SQL_AUDIT，4.x使用GV$OB_SQL_AUDIT）

3.2 获取sql_id

sql_id可通过以下几种方式获取：

1. 查询gv$plan_cache_plan_stat表获取。
2. 查询gv$sql_audit表获取。

注意：在不同场景下，需要根据实际情况选择合适的表来查询sql_id，同时要注意表结构和字段的差异。

3.3 获取plan_id

3.3.1 在observer.log日志中获取plan_Id

-- 3.X sys用户下查询表的相关信息
SELECT
    m.tenant_id,
    d.database_name,
    t.table_name,
    m.svr_ip,
    m.svr_port,
    m.role,
    d.database_id,
    t.table_id
FROM
    __all_virtual_meta_table m
JOIN
    __all_virtual_table t ON m.tenant_id = t.tenant_id
JOIN
    gv$database d ON t.database_id = d.database_id
WHERE
    m.tenant_id = '1001'
    AND d.database_name = 'wms'
    AND t.table_name = 'order_info'
    and m.role=1
GROUP BY
    m.tenant_id,
    m.svr_ip,
    m.svr_port,
    m.role,
    d.database_id,
    t.table_name,
    t.table_id;  
-- 4.X sys用户下查询表的相关信息
select  DATABASE_NAME,TABLE_NAME,PARTITION_NAME,SVR_IP,role from  oceanbase.DBA_OB_TABLE_LOCATIONS  where  DATABASE_NAME = 'plc'  and  TABLE_NAME='device_print_record'  and  role = 'LEADER';
-- 在主节点的ip上查询日志
grep "from whest where CUST_ACCT_NO"  observer.log |grep "plan_id"

3.3.2 在v$sql_audit中获取planId

# 4.x使用GV$OB_SQL_AUDIT
select PLAN_ID,query_sql from gv$sql_audit where query_sql like '%total_waits%' limit 1;

3.3.3 在ocp白屏中获取planId

1. SQL判断 --> TopSQL --> 打开F12 --> 调整筛选框

注意：只能选择TOP SQL，不能选择可疑SQL或者SlowSQL。

2. 选择All

3. 页面选择SQL（如出现太多接口数据，点如图所示清理无用接口）
   

4. 找到topPlanGroup，选择Response，搜索planId
   

想获取更多实用干货，关注微信公众号【雅俗数据库】。