openGauss6.0数据库绑定变量的PBE性能抖动测试

看腻了就来听听视频讲解吧：https://www.bilibili.com/video/BV1aE42157Sg/
PostgreSQL数据库绑定变量的PBE性能抖动问题：
https://www.modb.pro/db/1772166024404766720

现象

应用JDBC接口调用SQL执行慢（几十分钟），客户端Dbeaver或gsql执行快（秒内）

概念

SQL语句结构完全相同，但入参不同，这种情况下考虑在生成执行计划时，将SQL中不同的数值以参数的形式作为替代（$）。此后的每次查询都可以根据语句中的数值将执行计划中的参数替换后再做执行，以此来提高此类SQL的执行效率。

实现方式是使用 java 的 preparedStatement 预编译接口，在数据库内核中对应的名称即为PBE(Parse Bind Execute)。

在执行一个SQL时，首先生成执行计划（进行语义分析、词法解析、逻辑优化、物理优化）、执行、结果传输等操作。如果一个SQL在应用中反复使用，可以将此SQL执行计划固化下来，只做一次prepare，后面执行时就不需要进行前面执行计划的生成操作，直接使用prepare好的执行计划。

PREPARE语句

特点：

1. 会话级别，session结束PREPARE语句也不存在了。下次再使用需重新创建
2. PREPARE语句不能在多个并发的client中共有
3. PREPARE语句可以通过DEALLOCATE命令清除。
4. 会话中所有可用的PREPARE语句获取可查询系统视图：pg_prepared_statements

openGauss同PostgreSQL一样，默认前5次执行时每次都会根据实际传入的实际绑定变量生成执行计划（custom plan），即每次都是硬解析；当第6次开始执行时，会生成一个通用的执行计划（generic plan），同时与前5次的执行计划进行比较，如果比较的结果是通用执行计划不比前5次的执行计划差，以后就会把这个通用的执行计划固定下来，这之后即使传入的值发生变化后，执行计划也不再变化。这就相当于Oracle打开了绑定变量窥视的功能。

PostgreSQL 的PBE相关参数介绍：

PG 12 中新增plan_cache_mode参数，用来改变执行计划的缓存模式。

plan_cache_mode参数介绍：

其中default值就是auto，即自动修正执行计划。
force_custom_plan：为每个指定的参数重新创建新的执行计划。
force_generic_plan：不依赖与参数的值，执行计划可以在各个语句间重复使用。

openGauss 的PBE相关参数介绍：

• enable_pbe_optimization

• plan_cache_mode

案例模拟

创建存在严重数据倾斜的数据场景，即为“openGauss”的数据有100万，而为“PostgreSQL”的数据是2条

-- 创建测试表和数据
drop table IF EXISTS t_pbe;
create table t_pbe(col1 serial, col2 text);
insert into t_pbe(col2) select 'openGauss' from generate_series(1, 1000000);
insert into t_pbe(col2) select 'PostgreSQL' from generate_series(1, 2);
CREATE INDEX idx_t_pbe_col2 ON t_pbe(col2);
vacuum analyze t_pbe;

常量模拟执行计划，可以看到col2字段的“openGauss”数据用的是全表扫描（这里用到并行提高了查询效率），“PostgreSQL”数据是走仅索引扫描

-- 常量模拟执行计划
explain (analyze,buffers) select count(*) from t_pbe where col2='openGauss';
explain (analyze,buffers) select count(*) from t_pbe where col2='PostgreSQL';

PBE模拟执行计划一：
enable_pbe_optimization=on
plan_cache_mode=auto
默认参数配置,绑定变量无法得知具体入参值，都走了较差的全表扫描执行计划

-- 模拟PBE，SQL如参用$1,$2...
prepare pbe_test as select count(*) from t_pbe where col2=$1;
-- 执行
explain (analyze,buffers) execute pbe_test('PostgreSQL');
explain (analyze,buffers) execute pbe_test('openGauss');
-- 取消
DEALLOCATE pbe_test;
-- pg_prepared_statements视图比较鸡肋，PG14 新增统计软/硬解析次数
select * from pg_prepared_statements\gx

PBE模拟执行计划二：
enable_pbe_optimization=off
plan_cache_mode=auto
关闭enable_pbe_optimization参数，可以观察到入参变量不在是$而是具体值，走回原先正常的执行计划（都是走cplan的执行计划），在实际应用场景上这样配置会出现第6次运行时走全表扫描（gplan的执行计划）

set enable_pbe_optimization=off;

PBE模拟执行计划三：
enable_pbe_optimization=on
plan_cache_mode=‘force_custom_plan’
调整plan_cache_mode参数，强制执行计划走cplan

set plan_cache_mode='force_custom_plan';

PBE模拟执行计划四：
enable_pbe_optimization=on
plan_cache_mode=auto
默认参数配置，用hint来指定执行计划

prepare pbe_test as select /*+ use_cplan */ count(*) from t_pbe where col2=$1;

总结

1. 对OLTP请求，并发高，数据倾斜较少，建议使用AUTO（自动修正）。 针对数据倾斜场景推荐使用hint方式或在会话级使用set指定force_custom_plan来做优化处理。
2. 对OLAP（复杂分析查询）由于并发低，每次请求的条件输入评估选择性可能差异较大，每条SQL（只是输入参数、WHERE条件不一样）也许使用不同的执行计划才能达到最佳的执行效率，因此OLAP系统，建议可以使用force_custom_plan。