kingbase收集应用sql执行计划之auto_explain的使用

kingbase收集应用sql执行计划之auto_explain的使用

auto_explain插件参数介绍

auto_explain.log_min_duration
SQL语句执行时间的阈值，所有时间超过这个阈值的SQL语句的执行计划都会被记录到日志中，单位为毫秒，0表示记录所有语句，-1表示不记录

auto_explain.log_analyze
该参数控制是否加入analyze功能，即除了计划外把实际执行时间等信息也打出来，默认值为off

auto_explain.log_settings
该参数控制是否打印已修改的配置选项的信息，输出中仅包含影响执行计划的配置选项，默认值为off

auto_explain.log_verbose
该参数控制是否在执行计划中打印更为详细的输出信息，默认值为off

auto_explain.log_buffers
该参数控制是否在执行计划中打印缓冲区使用的信息，默认值为off

auto_explain.log_wal
该参数控制是否打印WAL日志的信息，默认值为off

auto_explain.log_triggers
该参数控制是否打印触发器的统计信息，注意只有当auto_explain.log_analyze参数开启时，该参数才有效，默认值为off

auto_explain.log_format
该参数用于控制执行计划的输出格式，可以是text、xml、json、yaml，默认值为text

auto_explain.log_level
该参数用于控制auto_explain插件的日志级别，其值同服务器的日志级别相同，默认值为LOG

auto_explain.log_nested_statements
该参数用于控制是否支持嵌套语句（在一个函数内执行的语句）的执行计划输出，当它关闭时，只记录顶层语句的计划，默认值为off

auto_explain.log_timing
该参数用于控制是否打印计划中每个节点的计时信息，注意只有当auto_explain.log_analyze参数开启时，该参数才有效，默认值为off

auto_explain.sample_rate
该参数用于控制打印会话中语句计划的比例，默认值为1，表示输出所有语句的计划，假如设置0.5,是打印所有sql里面一半的sql，不是某一个sql比如执行10次，只打印5次的意思

会话级使用auto_explain

示例用例：

begin;
--在当前会话加载插件
load 'auto_explain';

--在当前会话输出执行计划
set client_min_messages='log';
set auto_explain.log_min_duration = 0;
set auto_explain.log_analyze = true;
set auto_explain.log_verbose = true;
set auto_explain.log_buffers = true;
set auto_explain.log_nested_statements = true;

create table tb1 (id int,name text);
insert into tb1 values(generate_series(1,100),'aa');

--创建一个函数
create function f1(c1 int) returns int as $$
declare
rec int;
begin
select count(*) from tb1 where id < c1 into rec;
return rec;
end;
$$ language plpgsql strict;

--调用函数
select f1(100);

rollback;

示例结果显示：

test=# begin;
BEGIN
test=# load 'auto_explain';
LOAD
test=# set client_min_messages='log';
SET
test=# set auto_explain.log_min_duration = 0;
SET
test=# set auto_explain.log_analyze = true;
SET
test=# set auto_explain.log_verbose = true;
SET
test=# set auto_explain.log_buffers = true;
SET
test=# set auto_explain.log_nested_statements = true;
SET
test=#
test=# create function f1(c1 int) returns int as $$
test$# declare
test$# rec int;
test$# begin
test$# select count(*) from tb1 where id < c1 into rec;
test$# return rec;
test$# end;
test$# $$ language plpgsql strict;
CREATE FUNCTION
test=# select f1(100);
LOG:  duration: 0.063 ms  plan:
Query Text: select count(*) from tb1 where id < c1
Aggregate  (cost=2.50..2.51 rows=1 width=8) (actual time=0.059..0.060 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=1
  ->  Seq Scan on public.tb1  (cost=0.00..2.25 rows=99 width=0) (actual time=0.011..0.046 rows=99 loops=1)
        Output: id, name
        Filter: (tb1.id < 100)
        Rows Removed by Filter: 1
        Buffers: shared hit=1
LOG:  duration: 1.024 ms  plan:
Query Text: select f1(100);
Result  (cost=0.00..0.26 rows=1 width=4) (actual time=1.018..1.019 rows=1 loops=1)
  Output: f1(100)
  Buffers: shared hit=46 read=1
  I/O Timings: read=0.096
 f1
----
 99
(1 行记录)

test=# rollback;
ROLLBACK
test=#

全局使用auto_explain

在数据目录里的参数文件kingbase.conf末尾的这个参数shared_preload_libraries值的最后添加auto_explain，然后重启数据库生效

shared_preload_libraries = '<其他插件>,auto_explain'

以下常用配置参数，不需要重启，在线reload生效，记录所有运行10s以上的sql的执行计划到数据库日志sys_log里面

auto_explain.log_min_duration='10s'
auto_explain.log_analyze=on
auto_explain.log_nested_statements=on
auto_explain.log_timing=on
auto_explain.log_buffers=on

仅打印某个指定进程执行sql的计划

这个比较适合问题临时诊断配置
01、关闭全局打印
打开参数文件配置,reload生效

auto_explain.log_min_duration='-1’
auto_explain.log_analyze=off
auto_explain.log_nested_statements=off
auto_explain.log_timing=off
auto_explain.log_buffers=off

02、设置参数，但是不生效

-- auto_explain.log_min_duration=0为打印所有sql的执行计划，按需调整
alter system set auto_explain.log_min_duration=0       ;
alter system set auto_explain.log_analyze=on               ;
alter system set auto_explain.log_nested_statements=on     ;
alter system set auto_explain.log_timing=on   ;
alter system set auto_explain.log_buffers=on             ;

03、通过发信号，让某个会话生效

kill -1 <pid>

04、重置第二步的参数设置，避免其他会话因为其他变更生效

alter system reset auto_explain.log_min_duration;
alter system reset auto_explain.log_analyze;
alter system reset auto_explain.log_nested_statements;
alter system reset auto_explain.log_timing;

05、关闭该进程的计划打印
再次对该进程发送信号

kill -1 <pid>

打印未执行完成的sql计划

设置参数auto_explain.log_plan=on，不过不是所有版本都有这个参数
就会在sql进入executor打印计划，不过该调整仅对还未发生的sql有效，已经正在执行的sql没有用