内含 truncate 操作的存储过程执行时间不稳定的原因

问题描述

某存储过程的执行时间非常不稳定，有时仅需要 0.1s，有时却需要 20 多秒。

示例如下。

1. 创建测试表。

   obclient > create table t001 (id number(38),name varchar2(50));
   Query OK, 0 rows affected (0.067 sec)
   

2. 循环插入测试数据。

   obclient > DECLARE
   BEGIN
   FOR i IN 1 .. 10000000 LOOP
   insert into t001 values (i,'test');
   commit;
   END LOOP;
   END;
   /
   Query OK, 1 row affected (66.350 sec)
   

3. 创建 PL/SQL 包头 pkg1。

   obcleint > create or replace package pkg1 as
   procedure p1;
   end;
   /
   Query OK, 0 rows affected (0.014 sec)
   

4. 创建 PL/SQL 包体名 pkg1。

   obclient > create or replace package body pkg1 as 
   procedure p1 as
   v_sql varchar2(100);
   begin
   v_sql := 'truncate table t001;';
   execute immediate v_sql;
   insert into t001 values (1,'test');
   end;
   end;
   /
   Query OK, 0 rows affected (0.016 sec)
   

5. 调用存储过程。

   obclient > call pkg1.p1();
   Query OK, 0 rows affected (0.750 sec)
   

6. 再调用存储过程。

   obclient > call pkg1.p1();
   Query OK, 0 rows affected (0.625 sec)
   

问题原因

从 package 的定义中看到有几处 truncate table 操作，如果走到 truncate table 分支之后，会触发 package 重编译导致慢。

OceanBase 数据库 V4.0 之前的版本中，truncate table 的操作本质上是执行了一次 drop table 和 create table，其中涉及到了 schema 的删除与重建，因此会触发 package 的重编译。

适用版本

OceanBase 数据库 V2.x、V3.x 版本。

解决方法

如果是对数据量小的表、分区做 truncate 操作，可以用 delete 替换。

规避方式

PL 依赖的表尽量不要做 DDL 操作。