文档首页/ 云数据库 GaussDB/ 开发指南（集中式_V2.0-8.x）/ SQL调优指南/ PLAN TRACE使用介绍
更新时间：2024-11-12 GMT+08:00
查看PDF
分享

PLAN TRACE使用介绍

  1. 该特性是数据库内核开发人员对慢SQL深度分析使用的特性，不建议非内核开发人员使用。
  2. 该特性开启后，会在执行DML期间记录优化器相关信息到系统表中，这样会导致读事务变成写事务，从而导致要求必须在读事务里执行的函数无法执行，例如函数pg_create_logical_replication_slot等。

使用plan trace特性可以查看查询计划的优化过程。在plan trace中可以看到计划中路径的计算过程、路径的选择与淘汰过程等关键信息，以达到分析慢SQL根因的目的。下面分别介绍使用该特性的两种方式。

--准备表
CREATE TABLE tb_a(c1 int);
CREATE TABLE tb_b(c1 int);
CREATE INDEX tb_a_idx_c1 ON tb_a(c1);
CREATE INDEX idx_b ON tb_b(c1);

方式一：使用GUC参数enable_plan_trace启用plan trace特性，操作步骤如下：

  1. 打开plan trace guc开关： 

    SET enable_plan_trace = on;

  2. 执行业务SQL。例如业务SQL如下： 

    SELECT * FROM tb_a a, tb_b b WHERE a.c1 = b.c1 AND a.c1=1;

  3. 通过视图gs_my_plan_trace查看新生成的plan trace： 

    SELECT * FROM gs_my_plan_trace ORDER BY modifydate LIMIT 1;

    由于plan trace的记录一般比较大，如果使用gsql连接数据库，建议使用\x命令将gsql的查询结果展示方式改为Expanded方式。

    由于plan trace通常比较大，这里只给出本示例执行结果的关键trace的部分片段，如下所示：

    片段1：在trace中，可以看到当前执行的SQL和计划。

    query_id      | 4f078a966a1c4a434167b2f780bbfd92
query         | select * from tb_a a, tb_b b where a.c1 = b.c1 and a.c1=1;
unique_sql_id | 2108646922
plan          | Datanode Name: datanode
              | Nested Loop  (cost=0.00..81.88 rows=144 width=8)
              |   ->  Seq Scan on tb_a a  (cost=0.00..40.03 rows=12 width=4)
              |         Filter: (c1 = '***')
              |   ->  Materialize  (cost=0.00..40.09 rows=12 width=4)
              |         ->  Seq Scan on tb_b b  (cost=0.00..40.03 rows=12 width=4)
              |               Filter: (c1 = '***')

    片段2：在trace中，可以看到当前SQL使用的关键GUC参数。

    plan_trace    | [key_guc]
              | enable_pbe_optimization=1
              | plan_cache_mode=0
              | random_page_cost=4.000
              | enable_hashjoin=1
              | enable_mergejoin=1
              | enable_nestloop=1
              | enable_seqscan=1
              | effective_cache_size=16385
              | work_mem=65536
              | default_statistics_target=100
              | cost_param=0
              | =[key_guc]=

    片段3：在trace中，可以看到当前SQL查询计划的路径代价的计算过程。

                  | [optcost_initial_cost_nestloop]
              | method_initial_state: inner_pathid,2 outer_pathid,1 inner_start_cost,0.000000 inner_total_cost,40.025000 outer_start_cost,0.000000 outer_total_cost,40.025000 outer_path_rows,12.000000
              | cal: inner_rescan_start_cost,0.000000 inner_rescan_total_cost,40.025000
              | cal: inner_run_cost = inner_total_cost - inner_start_cost 40.025000, 40.025000, 0.000000
              | cal: inner_rescan_run_cost = inner_rescan_total_cost - inner_rescan_start_cost 40.025000
              | cal: startup_cost += outer_start_cost + inner_start_cost 0.000000
              | cal: run_cost += outer_total_cost - outer_start_cost 40.025000
              | cal: run_cost += (outer_path_rows - 1) * inner_rescan_start_cost 40.025000
              | cal: run_cost += inner_run_cost 80.050000
              | cal: run_cost += (outer_path_rows - 1) * inner_rescan_run_cost 520.325000
              | Initial nestloop cost: startup_cost: 0.000000, total_cost: 520.325000
              | =[optcost_initial_cost_nestloop]=

    片段4：在trace中，可以看到基表路径的淘汰过程：1.老路径被淘汰；2.老路径被淘汰的原因；3.新路径的相关信息。

                  | An old path is removed with cost = 0.000000 .. 521.765000;  rows = 144.000000
              | The old path and the comparison results are:
              | {
              |          old pathid=00000004    Cost = NewBetter        |       PathKeys = Equal        |          BMS = Equal          |         Rows = Equal
              | }
              | A new path is accepted with cost = 0.000000 .. 81.880000;  rows = 144.000000
              | The detail information of the new path:
              | {
              |         NestLoop(1:tb_a  2:tb_b ) pathid=00000005 hasparam=0 rows=144 multiple=1.000000 tuples=0.00 rpages=0.00 ipages=0.00 selec=0.00000000 ml=0 iscost=1 lossy=0 uidx=0)  dop=1 cost=0.00..81.88 hint 0 trace_id=#1##3##5#      clauses:  outerpathid=00000001 innerpathid=00000003
              | }

方式二：使用系统函数gs_plan_trace_watch_sqlid启用plan trace特性，操作步骤如下：

  1. 通过系统表dbe_perf.statement获得感兴趣的SQL的 unique sql id，例如使用如下SQL来获取unique sql id： 

    SELECT * FROM dbe_perf.statement WHERE query LIKE '%tb_a%';

    获取到的结果如下中的unique_sql_id字段：

    node_name            | datanode1
node_id              | 0
user_name            | qiumc
user_id              | 10
unique_sql_id        | 1921680825
query                | select * from tb_a a, tb_b b where a.id=b.id and a.c1=?;
n_calls              | 3
min_elapse_time      | 8880
max_elapse_time      | 12371
total_elapse_time    | 32036

  2. 具有sysadmin权限的用户调用gs_plan_trace_watch_sqlid函数侦听感兴趣的unique sql id。示例如下： 

    SELECT gs_plan_trace_watch_sqlid(1921680825);

  3. 如果感兴趣的unique sql id没有开始生成plan trace，则该unique sql id会被保存在一个内存的列表中，且可以通过函数gs_plan_trace_show_sqlids()来查看当前待收集plan trace的unique sql id列表，例如示例SQL如下： 

    SELECT gs_plan_trace_show_sqlids();
    该SQL的执行结果结果如下： 
    -[ RECORD 1 ]-------------+------------
gs_plan_trace_show_sqlids | 1921680825,

  4. 如果此时执行一次SQL： 

    SELECT * FROM tb_a a, tb_b b WHERE a.c1=b.c1 AND a.c1=1;

    同样可以针对该SQL生成plan trace记录，后续步骤与方法一中步骤3相同。

    只有具有sysadmin/opradmin/monadmin权限的用户才可以调用gs_plan_trace_watch_sqlid、gs_plan_trace_show_sqlids这两个函数。如果普通用户执行了管理员侦听的unique sql id的SQL，则可以使用视图gs_my_plan_trace来查看自己生成的plan trace。

plan trace通常比较大，需要用户及时清理，否则会占用大量的磁盘空间，用户可以使用gs_plan_trace_delete函数来删除生成的plan trace。

例如使用SQL：

select gs_plan_trace_delete(TIMESTAMPTZ '2023-01-10 17:16:42.652543+08')

可以删除当前用户的小于等于2023-01-10 17:16:42.652543+08时间的所有plan trace，从而达到每个用户都可以清理自己plan trace数据的目的。

父主题： SQL调优指南

相关文档