Oracle 19c触发器JSON_OBJECT性能优化及CLOB返回问题咨询

测试场景说明 #

在Oracle 19c环境下创建触发器以JSON格式记录数据变更时，发现JSON_OBJECT生成JSON的性能表现较差，测试细节如下：

• 创建测试表TRIGGER_TEST
• 编写AFTER INSERT触发器，采用两种JSON_OBJECT调用方式：
  1. SQL语句中使用RETURNING CLOB子句，通过SELECT INTO赋值给CLOB变量
  2. 直接在PL/SQL代码块中赋值
• 插入10万条数据的耗时对比：
  • 禁用触发器：4秒
  • 启用第一种触发器：14秒，其中JSON_OBJECT占用约8.5秒
  • 启用第二种触发器：JSON_OBJECT执行时间降至6.5秒，但总耗时仍为14秒；且PL/SQL中无法直接通过RETURNING CLOB获取结果，但业务场景中JSON内容可能超过32k，必须使用CLOB存储

问题解答 #

1. 第二种触发器为何执行更快？ #

第一种方式是在SQL引擎中执行JSON_OBJECT，再通过RETURNING CLOB+SELECT INTO将结果传递到PL/SQL变量，这个过程涉及SQL引擎与PL/SQL引擎的上下文切换，每次调用都会产生跨引擎的数据传递开销。
第二种方式直接调用PL/SQL版本的JSON_OBJECT构造函数，完全在PL/SQL引擎内完成计算，避免了跨引擎的上下文切换，因此JSON_OBJECT本身的执行时间更短。但总耗时仍为14秒，是因为触发器的固定开销（如事务日志写入、数据一致性校验等）占比极高，JSON生成环节的优化无法抵消这些固定成本。

2. 能否让第二种方式返回CLOB？ #

可以。Oracle 19c支持在PL/SQL中直接使用JSON_OBJECT的RETURNING CLOB语法，直接生成CLOB类型的JSON结果，满足超过32k的内容存储需求。示例代码如下：

DECLARE
  v_change_json CLOB;
BEGIN
  v_change_json := JSON_OBJECT(
    'id' VALUE :NEW.id,
    'create_time' VALUE :NEW.create_time,
    'content' VALUE :NEW.content
    RETURNING CLOB
  );
  -- 后续JSON存储或处理逻辑
END;

3. 该场景下有哪些加速JSON生成的方案？ #

• 精简JSON字段：仅记录业务需要的变更字段，而非全量字段，减少JSON_OBJECT的计算量
• 改用语句级触发器：如果是批量插入场景，将行级触发器（FOR EACH ROW）改为语句级触发器（FOR EACH STATEMENT），一次性处理多条数据生成JSON，大幅减少触发器触发次数
• 使用JSON_OBJECT_T构建JSON：PL/SQL中的JSON_OBJECT_T对象支持动态添加字段，对于复杂JSON的构建性能优于原生JSON_OBJECT函数，示例：

  DECLARE
    v_json_obj JSON_OBJECT_T;
    v_json_clob CLOB;
  BEGIN
    v_json_obj := JSON_OBJECT_T();
    v_json_obj.put('id', :NEW.id);
    v_json_obj.put('content', :NEW.content);
    v_json_clob := v_json_obj.to_clob();
  END;
  

• 异步处理JSON生成：触发器仅记录变更的原始数据，将JSON生成与存储操作放到异步队列（如Oracle AQ或DBMS_SCHEDULER）中后台执行，降低插入操作的实时耗时
• 预计算静态JSON片段：对于不常变化的字段，提前生成对应的JSON片段，在触发器中直接拼接，减少实时计算开销

内容的提问来源于stack exchange，提问作者marciel.deg