PL/pgSQL 编译缓存

本文主要介绍 Vastbase 数据库中 PL/pgSQL 的编译缓存功能。

功能描述

PL/pgSQL 的编译是一个耗时操作，每次运行前都重新编译对性能来说影响非常大。为解决这个问题，对于使用 PL/pgSQL 定义的函数/存储过程，Vastbase 数据库会将编译结果缓存在会话的内存中。在会话运行期间，如果函数/存储过程的定义没有发生变化，则只需要编译一次，后续函数/存储过程的运行会从缓存获取编译结果。

编译缓存判断逻辑

Vastbase 数据库使用 PL/pgSQL 编译缓存的判断逻辑如下：

1、对于一条 SQL 中复数次使用的函数，Vastbase 数据库会通过存储函数编译信息的方式直接使用，这种情况下不会访问 PL/pgSQL 编译缓存。

2、计算调用函数的哈希键，并根据结果查询是否存在 PL/pgSQL 编译缓存。

Vastbase 数据库中，哈希键的计算规则受到参数类型与字符集排序规则影响，对于不同的字符集排序规则、存在 ANY 系列参数时，可能存在多个不同的哈希键。

3、若存在 PL/pgSQL 编译缓存，则进一步检查缓存的有效性，如果缓存已失效，则删除失效缓存。

4、若不存在 PL/pgSQL 编译缓存，或缓存已失效，则对函数进行编译。

注意事项

• PL/pgSQL 编译缓存仅在会话中生效。

• 对于不同的字符集排序规则、存在 ANY 系列参数时，可能存在多个不同的哈希键，即对应不同的 PL/pgSQL 编译缓存结果。

示例

1、创建测试表，并导入数据。

CREATE TABLE tb_vinegar(
breed VARCHAR2(25),
name VARCHAR2(25),
birthdate DATE);

编辑测试脚本，在 sql 脚本中创建 100 个包，每个包中有一个函数，每个函数中包含一个变量。

vi tb_vinegar.sh

#!/bin/bash
for ((i=1;i<=100;i++))
do
if [ $i -eq 100 ]
then
echo "CREATE OR REPLACE package pkg_vinegar_0 as" >> t_vinegar.sql
echo "FUNCTION f_0 () RETURN VARCHAR ;" >> t_vinegar.sql;
echo "v_0 tb_vinegar%rowtype;">>t_vinegar.sql
echo "TYPE rec_type_0 IS RECORD(" >> t_vinegar.sql
echo "col_int int," >> t_vinegar.sql
echo "col_smallint SMALLINT," >> t_vinegar.sql
echo "col_integer INTEGER," >> t_vinegar.sql
echo "col_bigint BIGINT," >> t_vinegar.sql
echo "col_int2 INT2," >> t_vinegar.sql
echo "col_int4 INT4," >> t_vinegar.sql
echo "col_int8 INT8" >> t_vinegar.sql
echo ");" >> t_vinegar.sql
echo "r_0 rec_type_0;">>t_vinegar.sql
echo "END;" >> t_vinegar.sql
echo "/" >> t_vinegar.sql
else
echo "CREATE OR REPLACE package pkg_vinegar_$i as" >> t_vinegar.sql
echo "FUNCTION f_$i () RETURN VARCHAR ;" >> t_vinegar.sql
echo "v_$i tb_vinegar%rowtype;">>t_vinegar.sql
echo "TYPE rec_type_$i IS RECORD(" >> t_vinegar.sql
echo "col_int int," >> t_vinegar.sql
echo "col_smallint SMALLINT," >> t_vinegar.sql
echo "col_integer INTEGER," >> t_vinegar.sql
echo "col_bigint BIGINT," >> t_vinegar.sql
echo "col_int2 INT2," >> t_vinegar.sql
echo "col_int4 INT4," >> t_vinegar.sql
echo "col_int8 INT8" >> t_vinegar.sql
echo ");" >> t_vinegar.sql
echo "r_$i rec_type_$i;">>t_vinegar.sql
echo "END;" >> t_vinegar.sql
echo "/" >> t_vinegar.sql
fi
done


for ((i=1;i<=100;i++))
do
if [ $i -eq 100 ]
then
echo "CREATE OR REPLACE PACKAGE BODY pkg_vinegar_0 as " >> t_vinegar.sql
echo "FUNCTION f_0 () RETURN VARCHAR " >> t_vinegar.sql;
echo "AS" >> t_vinegar.sql
echo "BEGIN">>t_vinegar.sql
echo "RETURN '123'; " >> t_vinegar.sql
echo "END;" >> t_vinegar.sql
echo "END;" >> t_vinegar.sql
echo "/" >> t_vinegar.sql
else
echo "CREATE OR REPLACE PACKAGE BODY pkg_vinegar_$i as " >> t_vinegar.sql
echo "FUNCTION f_$i () RETURN VARCHAR " >> t_vinegar.sql
echo "AS " >> t_vinegar.sql
echo "BEGIN" >> t_vinegar.sql
echo "v_$i.breed=pkg_vinegar_$((i-1)).f_$((i-1));" >> t_vinegar.sql
echo "r_$i.col_int=10;" >> t_vinegar.sql
echo "dbms_output.put_line(v_$i.breed);" >> t_vinegar.sql
echo "dbms_output.put_line(r_$i.col_int);" >> t_vinegar.sql
echo "RETURN r_$i.col_int;" >> t_vinegar.sql
echo "END;" >> t_vinegar.sql
echo "END;" >> t_vinegar.sql
echo "/" >> t_vinegar.sql
fi
done

2、运行脚本，将 SQL 写入 t_vinegar.sql 文件。

sh tb_vinegar.sh

3、连接 Vastbase 数据库，查看查看执行时间。

vsql -d vastbase -f t_vinegar.sql

执行以下语句，可以打开多个窗口，查看执行时间。

\timing
SET max_stack_depth='10MB';
SELECT pkg_vinegar_99.f_99();

返回结果为：

 f_99
------
 10
(1 row)

Time: 279.024 ms

4、重新执行 pkg_vinegar_99.f_99()，查看执行时间。

\timing
SET max_stack_depth='10MB';
SELECT pkg_vinegar_99.f_99();

返回结果为：

 f_99
------
 10
(1 row)

Time: 19.223 ms

发现执行时间大幅减少，这是由于函数第一次编译的耗时远大于执行耗时。

5、退出当前会话，重新连接 Vastbase。

\q
vsql -d vastbase

6、再次执行 pkg_vinegar_99.f_99()，查看执行时间。

\timing
SET max_stack_depth='10MB';
SELECT pkg_vinegar_99.f_99();

返回结果为：

 f_99
------
 10
(1 row)

Time: 262.737 ms

发现执行时间与第一次执行近似，这是由于编译缓存仅存储在会话中。

7、编写脚本删除测试包与包体。

vi clean_vinegar.sh

for ((i=1;i<=100;i++))
do
if [ $i -eq 100 ]
then
echo "DROP PACKAGE BODY pkg_vinegar_0;" >> clean_vinegar.sql
else
echo "DROP PACKAGE BODY pkg_vinegar_$i;" >> clean_vinegar.sql
fi
done

for ((i=1;i<=100;i++))
do
if [ $i -eq 100 ]
then
echo "DROP PACKAGE pkg_vinegar_0;" >> clean_vinegar.sql
else
echo "DROP PACKAGE pkg_vinegar_$i;" >> clean_vinegar.sql
fi
done

echo "DROP TABLE tb_vinegar;" >> clean_vinegar.sql

执行清理脚本。

sh clean_vinegar.sh
vsql -d vastbase -f clean_vinegar.sql