OceanBase Oracle 存储过程 DDL 执行编译结果落盘和缓存

本文介绍 OceanBase Oracle 存储过程 DDL 执行编译结果落盘和缓存。

适用场景

场景 1 全量导入 - 所有对象是 VALID 状态

OceanBase Oracle PROCEDURE/PACKAGE 等 PL 较多且执行时编译慢，导致 SQL 执行慢或页面响应时间较长等问题，影响业务。

通过编译落盘，可以减少 PL 执行时不必要的编译，从而提高 SQL 执行速度。

推荐使用 utl_recomp.recomp_parallel 并合理设置并行度，进行全量编译落盘，详见步骤全量编译结果落盘和缓存。

场景 2 全量导入 - 预期全量编译后所有对象是 VALID 状态

OceanBase Oracle PROCEDURE/PACKAGE 等 PL 较多且依赖关系复杂，导致很多对象因此是 INVALID 状态，但不缺少对象且无兼容类问题。

且需要使用编译落盘功能来避免 PL 执行时不必要的编译。

如从 OceanBase Oracle 到 OceanBase Oracle 的导出并导入，OceanBase Oracle 大版本逻辑升级等。

推荐使用 utl_recomp.recomp_parallel 并合理设置并行度，进行全量编译落盘，详见步骤全量编译结果落盘和缓存。

场景 3 DDL 变更 - 通过 SQL 脚本等方式执行各种 DDL 后

进行 DDL 变更前，所有对象是 VALID 状态。

进行 DDL 变更后，需要对所有相关的 PL 对象（PACKAGE、PROCEDURE、FUNCTION、TYPE、TRIGGER）进行重新编译落盘，

推荐使用 utl_recomp.recomp_serial，进行全量编译落盘，效果同 Oracle DBMS_UTILITY.COMPILE_SCHEMA ("this subprogram is a wrapper for the RECOMP_SERIAL Procedure included with the UTL_RECOMP package.")

详见步骤全量编译结果落盘和缓存。

不适用场景

场景 4 全量导入 - 不能保证全量编译后所有对象是 VALID 状态

如首次从 Oracle 迁移到 OceanBase Oracle，建议参见：Oracle 离线迁移 OceanBase Oracle 参考手册，迁移后先解决所有的兼容问题及报错，直到 OceanBase Oracle 中所有对象是 VALID 状态，然后参考本文档操作。

相关参数或变量说明

变量 - ob_plan_cache_percentage

在 PL 对象（PACKAGE、PROCEDURE、FUNCTION、TYPE、TRIGGER）较多的情况下，编译落盘缓存（gv$ob_pl_cache_object）会占用较多 cache，可以适当增加如下参数避免因 ob_plan_cache_percentage 设置较小触发淘汰而导致调用时再重新 Load 到 PL cache 中，进而影响调用 PL 执行时间。

连接业务租户，通过如下 SQL 查看参数 ob_plan_cache_percentage (默认 5%)。

SHOW VARIABLES LIKE '%ob_plan_cache_percentage%';

连接业务租户，通过如下 SQL 设置参数 ob_plan_cache_percentage（生产环境请咨询负责此客户或项目的 OceanBase 技术支持团队）。

SET GLOBAL ob_plan_cache_percentage=25;

注 1：可根据持续观察 gv$ob_pl_cache_object 行数，不因新的编译落盘缓存触发其他对象的缓存淘汰或行数减少，即为合理。

变量 - plsql_optimize_level

编译优化等级，取值范围为 0-3，默认值为 2，即开启大部分优化。

编译优化等级在很大程度上可以影响编译的资源消耗和时间长短，一般而言，更高的优化等级会提供更好的运行时性能，但也需要更多 CPU、内存等资源和更长的编译时间。

根据业务可以灵活平衡编译时和运行时的开销，例如在开发测试环境中倾向于更短的编译时间（PLSQL_OPTIMIZE_LEVEL=0），而在生产环境中倾向于更短的运行时间（PLSQL_OPTIMIZE_LEVEL=2）。

通过如下 SQL 查看此参数。

SHOW VARIABLES LIKE 'plsql_optimize_level';

为使编译用时更短，可以设置 SESSION 变量如下：

-- disable PL optimization
ALTER SESSION SET plsql_optimize_level = 0;

注： 也可以在手动对编译慢的 PACKAGE 进行编译落盘时单独设置此参数。

测试环境可以根据情况设置全局变量来加速编译：

SET GLOBAL plsql_optimize_level = 0;

参数 - _ob_pl_compile_max_concurrency

编译并行度，默认为 4，集群级别参数。

utl_recomp.recomp_parallel 中的并行度上限为 _ob_pl_compile_max_concurrency 的值。

Oracle 租户下通过如下 SQL 查看此参数。

SELECT
  *
FROM
  sys.all_virtual_tenant_parameter_stat
WHERE
  name = '_ob_pl_compile_max_concurrency';

sys 租户下通过如下 SQL 查看此参数。

SELECT
  *
FROM
  __all_virtual_tenant_parameter_stat
WHERE
  name = '_ob_pl_compile_max_concurrency';

通过 root@sys 登录 sys 租户，可以根据情况调整此集群级别参数，如下。

-- allow at most 64 threads in an observer node to compile PL
ALTER SYSTEM SET _ob_pl_compile_max_concurrency = 8;

参数 - plsql_v2_compatibility（不需要改）

控制存储过程 DDL 阶段（CREATE OR REPLACE PACKAGE BODY/ALTER PACKAGE）编译落盘功能是否开启，默认不开启，配置项是租户级别，可以按租户来选择是否开启该功能。

默认值为 false，不开启存储过程 DDL 阶段编译落盘。除 OceanBase 技术支持团队特别说明需要开启外，不建议修改此参数默认值。

注 1: plsql_v2_compatibility 值为 false 时，如存在 PACKAGE 依赖关系，被依赖的 PACKAGE 也会编译落盘。

​ e.g. PACKAGE B 依赖 PACKAGE A，创建 PACKAGE B 时，被依赖的 PACKAGE A 会编译落盘，但 PACKAGE B 不会编译落盘。

注 2: plsql_v2_compatibility 值为 true 时，仅编译包头（ALTER PACKAGE xxx COMPILE SPECIFICATION;）不会触发编译落盘。

注 3: plsql_v2_compatibility 不影响 utl_recomp 系统包, 即调用 utl_recomp 总会进行编译落盘和缓存。

通过如下 SQL 查看此参数。

SHOW PARAMETERS LIKE 'plsql_v2_compatibility';

连接业务租户，通过如下 SQL 开启存储过程 DDL 阶段编译落盘，动态生效。

ALTER SYSTEM SET plsql_v2_compatibility = true;

连接业务租户，通过如下 SQL 关闭存储过程 DDL 阶段编译落盘，动态生效。

ALTER SYSTEM SET plsql_v2_compatibility = false;

检查各对象类型个数及状态

在 OceanBase 数据库中执行如下 SQL 查看各对象类型个数及状态，并在全量编译落盘过程中对比并观察进度。

如是从 Oracle 或 OceanBase Oracle 中迁移过来的，可以在原数据库中查一下并对比，确保对象个数一致。

不考虑对象状态，仅对比各对象类型个数:

ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
SELECT sysdate from dual;

SELECT
  t.owner,
  t.object_type,
  COUNT(1)
FROM
  dba_objects t
where
  t.owner IN (
    'ALVIN'
  )
GROUP BY
  t.owner,
  t.object_type
ORDER BY
  t.owner,
  t.object_type;

考虑对象状态，对比各对象类型个数及状态:

ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
SELECT sysdate from dual;

SELECT
  t.owner,
  t.object_type,
  t.status,
  COUNT(1)
FROM
  dba_objects t
WHERE
  t.owner IN (
    'ALVIN'
  )
GROUP BY
  t.owner,
  t.object_type,
  t.status
ORDER BY
  t.owner,
  t.object_type,
  t.status;

全量编译前检查 INVALID 对象

如是从 Oracle 或 OceanBase Oracle 中迁移过来的，在源数据库和目标数据库中分别执行如下 SQL 查看并对比 INVALID 的对象。

当迁移到 OceanBase Oracle 后，如果发现对象状态是 INVALID，先查看原数据库对象状态。以避免迁移后发现 OceanBase 中对象状态是 INVALID，排查半天后才发现在原库中就是 INVALID。

如原库中就是 INVALID，可以记录下来或考虑先删除，以避免影响编译或排查问题。

SELECT
  t.owner,
  t.object_type,
  t.status,
  t.object_name
FROM
  dba_objects t
WHERE
  t.owner IN (
    'ALVIN'
  )
  and t.status IN ('INVALID')
ORDER BY
  t.owner,
  t.object_type,
  t.status;

如因依赖关系导致有大量无效对象，如上述场景 2，可以考虑后面通过 utl_recomp.recomp_parallel 批量编译落盘。

如果想手工批量编译，可以参考如下方法。

批量编译当前用户下所有无效对象。

注： 为方便观察单个 PL 对象编译时间等，如下仅批量输出 SQL，复制后批量执行即可。

SELECT * FROM user_errors;

SET SERVEROUTPUT ON;

BEGIN
  FOR obj IN (SELECT object_name, object_type
              FROM user_objects
              WHERE object_type IN ('PACKAGE','PACKAGE BODY','TRIGGER','PROCEDURE','FUNCTION','TYPE') 
              AND status = 'INVALID' )
  LOOP
    BEGIN

      IF obj.object_type = 'PACKAGE' THEN
              dbms_output.put_line('ALTER PACKAGE ' || obj.object_name || ' COMPILE SPECIFICATION;');
      ELSIF obj.object_type = 'PACKAGE BODY' THEN
              dbms_output.put_line('ALTER PACKAGE ' || obj.object_name || ' COMPILE BODY;');
      ELSE
              dbms_output.put_line('ALTER ' || obj.object_type || ' ' || obj.object_name || ' COMPILE;');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Failed to compile ' || obj.object_type || ' "' || obj.object_name || '": ' || SQLERRM);
    END;
  END LOOP;
END;
/

SELECT * FROM user_errors;

编译完成后可以通过如下 SQL 查看各对象是否还有报错信息。如仍有报错信息，修复问题后再按如上步骤编译一下。

SELECT * FROM user_errors;

OR

SELECT * FROM all_errors WHERE owner = 'ALVIN';

全量编译结果落盘和缓存

如果 PROCEDURE/PACKAGE 等 PL 较多、较长时，执行时编译会比较慢，导致 SQL 执行慢或页面响应时间较长等问题，影响业务。通过在创建 PROCEDURE/PACKAGE 等 PL 的 DDL 阶段提前进行编译并将编译结果进行落盘并加载到 PL Cache 中，可以有效避免运行 PL 时不必要的编译，进而较大地提升 PL 相关 SQL 的执行时间。

为方便地对 PL 对象（PACKAGE、PROCEDURE、FUNCTION、TYPE、TRIGGER）进行编译，OceanBase 数据库 V4.2.3.0 版本开始兼容 Oracle UTL_RECOMP 系统包。

在 Oracle 租户 SYS 用户下执行如下 SQL，编译需要编译的对象 (不需要重新编译的会跳过)：

注 1: 全量编译前建议停掉相关业务应用，以避免不必要的影响。

注 2: PL 编译，尤其是通过 utl_recomp 全量编译，是 CPU 和 IO 密集型操作，CPU 或 磁盘性能会影响编译速度。

注 3: 因全量编译耗时较长，建议在 Linux 下开 screen 执行 (e.g. screen -ls; screen -S recomp; CTL + A, D 退出 screen, screen -r recomp 重新进入) 。

注 4: 运行此包时，数据库上不应该存在 DDL 执行，否则可能导致死锁。

注 5: 一次执行 utl_recomp.recomp_parallel 或 utl_recomp.recomp_serial 即可使全部对象变为 VALID 并将编译结果落盘和缓存。

全量导入 (场景 1 和 场景 2)

场景 1 全量导入 - 所有对象是 VALID 状态。

场景 2 全量导入 - 预期全量编译后所有对象是 VALID 状态。

在数据库升级或迁移后，可以在业务租户 SYS 用户下通过 utl_recomp.recomp_parallel 全量编译，并合理设置并行度以加快编译。

CALL utl_recomp.recomp_parallel(threads, schema, flags);

threads - 并行度，根据租户 CPU 配置合理并行度（上限为 _ob_pl_compile_max_concurrency）。

schema - 需要编译所有对象的 schema（用户名）。

flags - 写 1024 即可，"Flag values are intended for internal testing and diagnosability only."

e.g. 如下只需要将 ALVIN 改成需要编译所有对象的 schema（用户名）即可。

在业务租户 SYS 用户下执行。

-- 测试环境可以根据需要降低编译优化等级来加快编译速度，可以设置全局变量或 SESSION 变量
ALTER SESSION SET plsql_optimize_level = 0;

CALL utl_recomp.recomp_parallel(8, 'ALVIN', 1024);

DDL 变更 (场景 3)

场景 3 DDL 变更 - 通过 SQL 脚本等方式执行各种 DDL 后。

在数据库升级或迁移后，可以通过 utl_recomp.recomp_parallel 全量编译，效果同 Oracle DBMS_UTILITY.COMPILE_SCHEMA ("this subprogram is a wrapper for theRECOMP_SERIAL Procedure included with the UTL_RECOMP package.")

CALL utl_recomp.recomp_serial(schema, flags);

schema - 需要编译所有对象的 schema（用户名）。

flags - 写 1024 即可，"Flag values are intended for internal testing and diagnosability only."

e.g. 如下只需要将 ALVIN 改成需要编译所有对象的 schema（用户名）即可。

在业务租户 SYS 用户下执行。

-- 测试环境可以根据需要降低编译优化等级来加快编译速度，可以设置全局变量或 SESSION 变量
ALTER SESSION SET plsql_optimize_level = 0;

CALL utl_recomp.recomp_serial('ALVIN', 1024);

查看编译任务

然后可执行如下 SQL 观察编译任务 JOB 的情况。

ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
SELECT sysdate from dual;

SELECT
  *
FROM
  (
    SELECT
      tenant_id,
      job,
      job_name,
      job_type,
      last_date,
      this_date,
      next_date,
      start_date,
      end_date,
      state,
      what
    FROM
      sys.all_virtual_tenant_scheduler_job_real_agent
    WHERE
      job_name LIKE 'UTL_RECOMP_SLAVE_%'
      AND job > 0
    ORDER BY
      last_date DESC
  )
WHERE
  ROWNUM <= 10;

以上所有任务执行完成后会自动删除。

扩展阅读（按以上正常操作可跳过此部分）。

utl_recomp.recomp_parallel 会调用 dbms_scheduler.create_job 来创建任务。

Case 1：

utl_recomp.recomp_parallel 执行过程中手动取消，如果继续执行 utl_recomp.recomp_parallel 会自动删除之前创建的任务，此时无影响。

Case 2：

如 utl_recomp.recomp_parallel 执行过程中手动取消，不再继续执行 utl_recomp.recomp_parallel。

已通过 dbms_scheduler.create_job 创建的任务需要手动清理。

SYS 用户下查询 JOB:

SELECT job_name FROM SYS.dba_scheduler_jobs WHERE job_name LIKE 'UTL_RECOMP_SLAVE_%';

SYS 用户下手动清理对应 JOB：

EXEC dbms_scheduler.drop_job('UTL_RECOMP_SLAVE_X', true);

查看 DDL 编译缓存视图

注： 如下仅显示本机 observer 信息，可以通过 2881 直连到各 OBServer 查询确认。

业务租户下查询租户 ID。

SELECT tenant_id, tenant_name FROM dba_ob_tenants;

根据租户 ID 查询 DDL 编译缓存视图 (按 first_load_time 逆序)。

ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';

SELECT
  *
FROM
  (
    SELECT
      tenant_id,
      parameterize_text,
      first_load_time,
      last_active_time,
      object_type,
      object_id,
      compile_time,
      schema_version
    FROM
      gv$ob_pl_cache_object
    WHERE
      tenant_id = 1002 AND to_char(OBJECT_TYPE) NOT IN ('ANONYMOUS')
    ORDER BY
      first_load_time DESC
  )
WHERE
  rownum <= 10;

也可以根据租户 ID 按 compile_time 逆序查询 DDL 编译缓存视图，以找出编译落盘用时或 load 到 PL cache 用时较长的对象。

注 1： 如 PACKAGE A 依赖多个 PL 对象（PACKAGE、PROCEDURE、FUNCTION、TYPE、TRIGGER），在编译 PACKAGE A 过程会触发未编译落盘的 PL 对象的编译，所用时间都会累计到 PACKAGE A 上。

注 2: 如需要单独测试 PACKAGE A 编译落盘时间，可以在所有 PL 编译落盘后单独编译 PACKAGE A（ALTER PACKAGE A COMPILE）。

ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';

SELECT
  *
FROM
  (
    SELECT
      tenant_id,
      parameterize_text,
      first_load_time,
      last_active_time,
      object_type,
      object_id,
      compile_time,
      schema_version
    FROM
      gv$ob_pl_cache_object
    WHERE
      tenant_id = 1002 AND to_char(OBJECT_TYPE) NOT IN ('ANONYMOUS')
    ORDER BY
      compile_time DESC
  )
WHERE
  rownum <= 10;

查看 DDL 编译落盘视图

系统租户下操作。

首先，通过 root@sys 登录系统租户：

obclient -h10.10.20.20 -P2881 -uroot@sys -pxxx -A -D oceanbase

查询目标租户 tenant_id：

SELECT tenant_id, tenant_name FROM dba_ob_tenants;

查询 schema 对应的 database_id:

SELECT
  tenant_id,
  database_id,
  database_name
FROM
  __all_virtual_database
WHERE
  tenant_id = 1002
  AND database_name = 'ALVIN';

然后切换到业务租户下：

ALTER SYSTEM CHANGE TENANT oboracle; 

根据上述查到的 tenant_id 和 database_id，查询已编译落盘的对象，其中 __all_ncomp_dll.key_id 对应 user_objects.object_id。

SELECT
  gmt_create,
  gmt_modified,
  database_id,
  key_id,
  compile_db_id,
  merge_version
FROM
  __all_ncomp_dll
WHERE
  database_id = 500003
  -- AND key_id IN (500004, 500006, 500007)
ORDER BY
  gmt_modified DESC
LIMIT
  10;

如有需要可以在业务租户业务 schema 下查询相应的对象：

SELECT
  object_id,
  object_name,
  object_type
FROM
  user_objects
WHERE
  object_id IN (500004, 500006, 500007);

需要重新编译结果落盘的场景

当 PL（PACKAGE、PROCEDURE、FUNCTION、TYPE、TRIGGER）发生 DDL 变更或其依赖的表等对象发生 DDL 变更，会导致已有的 DDL 落盘失效。

当发生 DDL 变更（业务低峰时谨慎操作）后，建议通过 utl_recomp.recomp_parallel 对需要重新编译的对象进行全量编译落盘并缓存（不需要重新编译的会跳过），以避免业务执行 PL 再进行编译落盘缓存，从而避免影响业务。

扩展阅读

简单测试磁盘性能方法

DDL 存储过程 DDL 执行编译结果落盘和缓存，尤其是通过 utl_recomp 全量编译，是 CPU 和 IO 密集型操作，CPU 或 磁盘性能会影响编译速度。

如需测试性能 ，建议 OceanBase 部署采用文档最后的性能测试最低配置。

简单测试磁盘性能方法如下。

步骤一，检查磁盘是否为 SSD, rota（rotational）为 0 是固态硬盘 SSD, 为 1 是机械硬盘 HDD。

lsblk -o name,rota,model

步骤二，检查 /data/1/ 磁盘性能，Linux 命令如下（SSD 盘预期耗时 1s 左右, HDD 盘预期耗时 6s-10s 甚至更长时间）。

DISK=/data/1/
DEVICE=`df -Th "${DISK}" | tail -n 1 | awk '{print $1}'` ;echo "${DEVICE}"
time for i in `seq 1 1000`; do dd bs=4k if="${DEVICE}" count=1 skip=$(( $RANDOM * 128 )) >/dev/null 2>&1 ;done

步骤三，检查 /data/log1/ 磁盘性能，Linux 命令如下（SSD 盘预期耗时 1s 左右, HDD 盘预期耗时 6s-10s 甚至更长时间）。

DISK=/data/log1/
DEVICE=`df -Th "${DISK}" | tail -n 1 | awk '{print $1}'` ;echo "${DEVICE}"
time for i in `seq 1 1000`; do dd bs=4k if="${DEVICE}" count=1 skip=$(( $RANDOM * 128 )) >/dev/null 2>&1 ;done

清理 PL Cache

注： 仅限测试环境操作，生产环境请在 OceanBase 技术支持团队的指导下操作。

通过 root@sys 登录系统租户查询。

按租户清理 PL cache：

ALTER SYSTEM FLUSH PL CACHE GLOBAL TENANT='oboracle' GLOBAL;

清理所有租户的 PL cache：

ALTER SYSTEM FLUSH PL CACHE GLOBAL;

查看对象编译级别

可以先在 Oracle 租户下查询对应 PACKAGE 的 procedure_name。

SELECT
  owner,
  object_name,
  procedure_name,
  object_id,
  subprogram_id,
  object_type
FROM
  dba_procedures
WHERE
  owner = 'ALVIN'
  AND object_name = 'PKG_TEST'
ORDER BY
  subprogram_id;

通过 root@sys 登录系统租户查询。

先切换到对应租户：

ALTER SYSTEM CHANGE TENANT oboracle;

根据 procedure_name 或 package_id（object_id）查询。

SELECT package_id,routine_id, routine_name, exec_env FROM oceanbase.__all_routine WHERE routine_name = 'XXX';

或

SELECT package_id,routine_id, routine_name, exec_env FROM oceanbase.__all_routine WHERE package_id = 12345;

最后一位的 2 即代表编译级别是 2。

+------------+------------+--------------------------+---------------------------+
| package_id | routine_id | routine_name             | exec_env                  |
+------------+------------+--------------------------+---------------------------+
|     539691 |     545680 | XXXXXXXXXXXXXXX          | 2151677954,45,46,87,0,,2, |
...

单个对象编译落盘

在未开始编译落盘时（plsql_v2_compatibility 值为 false），可以在 Oracle 租户下手动执行如下 SQL 使其编译结果落盘和缓存。

EXEC dbms_utility.validate(object_id);

e.g.

EXEC dbms_utility.validate(542996);

性能测试最低配置

由于 PL 编译落盘时间受磁盘 IO 等硬件影响，生产等环境对 PL 编译落盘速度有一定要求的，建议采用性能测试最低配置。

服务器数量： 4 台 (1-1-1 集群)。1 台 OAT/OCP 服务器，3 台 OceanBase 计算服务器。

配置: 32C，256 GB，2 TB SSD 存储，万兆网卡 (含交换机)。缺一不可，否则将存在瓶颈。

适用版本

OceanBase 数据库 V4.2.3.x 及之后版本、V4.2.4.x 及之后版本、V4.2.5.x 及之后版本、V4.3.1.x 及之后版本、V4.3.2.x 及之后版本。