包含大量 HTTP Requests 的 PL/SQL Procedure 性能分析

2026-10-24支付老李

明确结论

这个 procedure 的主要瓶颈通常不是 cursor、XML 解析或单行 INSERT，而是串行 HTTP 往返。

每处理一行，Oracle database session 都要等待 external API 返回。假设需要处理 (N) 行，单次 HTTP 请求的平均耗时为 (T_{http})，最低执行时间大致为：

[ T_{total} \approx N \times (T_{http} + T_{xml} + T_{db}) ]

例如，单次请求平均耗时 300 ms，处理 10000 行时，仅等待 HTTP 响应的理论时间就约为：

[ 10000 \times 0.3s = 3000s \approx 50\text{ 分钟} ]

如果平均响应时间增加到 2 秒，总时间会超过 5.5 小时。少量慢请求、timeout 和 retry 还会明显拉长尾部时间。

PL/SQL 中的同步 HTTP 调用会阻塞当前 database session。这里阻塞的不是普通应用程序所说的 “main thread”，而是执行该 procedure 的 Oracle foreground process/session。它会一直等待网络操作完成。其他 session 通常仍可工作，但当前 session 占用的连接、事务状态、PGA、锁和其他资源可能长时间无法释放。

可能出现的具体问题

1. 执行时间随行数近似线性增长

当前逻辑完全串行：

读取一行
  -> 发起请求
  -> 等待响应
  -> 解析 XML
  -> 写入结果
  -> 读取下一行

处理量翻倍时，总时间通常也会接近翻倍。相比平均延迟，更应该关注 API 的高分位延迟。少数需要几十秒才能失败的请求，就可能决定整个批次何时完成。

2. HTTP timeout 和连接故障

需要考虑以下故障：

DNS 解析失败或变慢；
TCP connection timeout；
TLS handshake 失败或耗时过长；
API 返回 4xx、5xx；
请求已经被 API 处理，但数据库端读取 response 时连接中断；
API 限流，例如返回 429；
response 不完整、XML 非法或内容超出预期；
防火墙、代理或负载均衡器关闭空闲连接；
单个请求无限期等待或等待时间过长。

如果没有明确设置 timeout，一个异常请求就可能让整个批次停滞很久。可用的 timeout 接口取决于 HTTP package 的实现。如果底层使用 UTL_HTTP，应检查并设置合适的 transfer timeout，例如：

UTL_HTTP.SET_TRANSFER_TIMEOUT(30);

这里的 30 只是示例，不能直接作为生产环境的配置值。实际值应根据 API 的正常延迟分布、服务端 SLA 和业务容忍度确定。

3. 长事务和锁

如果 10000 行全部处理完成后才提交，事务可能持续数小时，并产生以下问题：

undo 使用量持续增加；
已修改记录上的 row lock 长时间保留；
失败后的 rollback 成本较高；
其他查询面临更大的一致性读压力；
极端情况下，长时间查询可能遇到与 undo 保留有关的问题；
procedure 中途失败后，已经完成的工作难以恢复和重跑。

反过来，每行执行一次 COMMIT 也不合适。这会增加 commit 开销，并使事务边界过细。更常见的做法是按固定批次提交，或者使用任务表记录每行的处理状态。批次大小需要通过测试确定。

4. 重试造成重复处理

网络超时不代表服务端没有处理请求。例如：

Oracle 发出请求；
API 已成功处理；
response 在网络中丢失；
Oracle 判断请求失败并重试。

如果 API 操作有副作用，重试可能造成重复处理。应优先使用业务唯一键或 idempotency key，并在结果表上建立合适的唯一约束。

5. 连接建立成本

如果每行请求都重新建立 TCP/TLS connection，就会增加：

TCP handshake 时间；
TLS handshake 时间；
API、代理和防火墙的连接压力；
临时端口和 socket 使用量。

需要检查 MAKE_HTTP_REQUEST_TO_API 能否安全复用 persistent connection，以及 response body 是否始终被完整读取并正确关闭。连接是否能够复用，取决于 package 的具体实现，不能只看 procedure 外层代码。

6. XML 和逐行 SQL 开销

当 HTTP 响应很快或 response 较大时，XML 解析也可能占用相当一部分时间。

在部分 Oracle 版本中，已不建议在新代码里使用 EXTRACTVALUE。可以根据实际版本评估 XMLTABLE，一次提取多个字段：

SELECT x.payment_status,
       x.reference_no,
       x.amount
INTO   l_status,
       l_reference_no,
       l_amount
FROM XMLTABLE(
       '/response/payment'
       PASSING l_xmltype
       COLUMNS
         payment_status VARCHAR2(30)  PATH 'status',
         reference_no   VARCHAR2(100) PATH 'reference',
         amount         NUMBER        PATH 'amount'
     ) x;

是否更快，必须使用真实 XML 和实际数据量进行测试，不能只根据语法判断。

先做分段计时

不要只测整个 procedure 的总耗时，至少应分别记录：

cursor 查询和 fetch 时间；
HTTP 请求耗时；
XML 构造与解析时间；
INSERT 时间；
commit 时间；
response 大小；
HTTP status；
timeout、连接失败和 XML 解析失败次数；
retry 次数；
每批次成功、失败和跳过的行数。

可以使用 DBMS_UTILITY.GET_TIME 测量 elapsed time，成本较低。它的单位是百分之一秒，而且计数值可能回绕，所以更适合计算短时间差值，不宜直接保存为绝对时间：

DECLARE
  l_http_start PLS_INTEGER;
  l_xml_start  PLS_INTEGER;
  l_db_start   PLS_INTEGER;

  l_http_cs    PLS_INTEGER;
  l_xml_cs     PLS_INTEGER;
  l_db_cs      PLS_INTEGER;
BEGIN
  l_http_start := DBMS_UTILITY.GET_TIME;

  MY_SCHEMA.MY_PACKAGE.MAKE_HTTP_REQUEST_TO_API(
    URL,
    C1.ID,
    RESPONSE_XML
  );

  l_http_cs := DBMS_UTILITY.GET_TIME - l_http_start;

  l_xml_start := DBMS_UTILITY.GET_TIME;

  L_XMLTYPE := XMLTYPE(RESPONSE_XML);

  -- XMLTABLE or other XML processing

  l_xml_cs := DBMS_UTILITY.GET_TIME - l_xml_start;

  l_db_start := DBMS_UTILITY.GET_TIME;

  INSERT INTO target_table (...) VALUES (...);

  l_db_cs := DBMS_UTILITY.GET_TIME - l_db_start;

  -- 将明细或聚合统计写入日志
END;

生产环境不一定需要为每行写一条详细日志，否则日志本身也可能成为瓶颈。可以采用以下方式：

开发测试时逐行记录；
生产环境每 100 或 1000 行聚合一次；
只记录慢请求和失败请求；
记录 p50、p95、p99 和最大值，而不只记录平均值。

建议同时保存 payment_id、attempt number、开始时间和结束时间，便于关联数据库日志与 API 服务端日志。

标记 session 和当前处理进度

可以使用 DBMS_APPLICATION_INFO 标记 module、action 和处理进度，方便 DBA 定位正在执行的 session：

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'PAYMENT_API_IMPORT',
    action_name => 'START'
  );

  FOR c1 IN transactions LOOP
    DBMS_APPLICATION_INFO.SET_ACTION(
      action_name => 'PAYMENT_ID=' || TO_CHAR(c1.id)
    );

    -- HTTP、XML 和 INSERT
  END LOOP;

  DBMS_APPLICATION_INFO.SET_ACTION('DONE');
END;

如果 ID 是敏感信息，不应直接写入 ACTION，可以改用批次号或脱敏后的标识。

还可以使用 DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS 报告总行数和已处理行数。不过，实现时不要为了获取总数而重复执行成本很高的查询。

Oracle 侧如何诊断

Session 级 SQL trace

对于可控的测试 session，可以启用 trace：

BEGIN
  DBMS_MONITOR.SESSION_TRACE_ENABLE(
    session_id => :sid,
    serial_num => :serial_no,
    waits      => TRUE,
    binds      => FALSE
  );
END;
/

测试结束后关闭：

BEGIN
  DBMS_MONITOR.SESSION_TRACE_DISABLE(
    session_id => :sid,
    serial_num => :serial_no
  );
END;
/

随后由 DBA 使用 TKPROF 或合适的 trace 分析工具，检查 SQL 执行次数、数据库等待和 elapsed time。HTTP 调用可能封装在 PL/SQL package 中，SQL trace 未必能直接显示完整的应用层请求耗时，因此 procedure 内部计时仍需保留。

查看 session 等待

执行期间可以观察目标 session 的状态和 wait event。网络调用可能表现为 socket 或网络相关等待，但具体事件名称会随 Oracle 版本、调用方式和平台变化，不能事先认定为某个固定名称。

需要重点确认：

session 当前是在使用 CPU，还是处于等待状态；
等待持续了多长时间；
是否存在锁等待；
undo、PGA 和临时空间是否持续增长；
SQL 是否被大量重复执行；
session 是否在某个 payment 上停留了异常长的时间。

如果环境获准使用相应的 Diagnostic Pack，还可以通过 AWR、ASH 等功能观察任务长时间运行期间的活动。使用前应确认许可条件。没有相应许可时，可以使用 session 视图、trace 和自建日志。

Wireshark 能回答什么

Wireshark 适合排查传输层问题，但不是分析业务耗时的首选工具。

它可以帮助判断：

是否反复建立 TCP connection；
DNS 查询是否缓慢；
TCP handshake 或 TLS handshake 是否耗时；
是否出现 retransmission、duplicate ACK 或 reset；
连接是由客户端、服务端还是中间设备关闭；
response 是否被拆成大量小包；
网络往返时间是否异常。

常见显示过滤器包括：

ip.addr == <API_IP>
tcp.port == 443
tcp.analysis.retransmission
tcp.analysis.duplicate_ack
tcp.flags.reset == 1
dns
tls

如果使用 HTTPS，Wireshark 默认无法查看加密后的 HTTP URL、headers 和 XML 内容，但仍能观察连接建立过程、数据包时间、流量大小、重传和断开情况。

抓包时需要注意：

必须在能够看到 Oracle database server 出站流量的位置抓包；
database server 可能通过 proxy 或 load balancer 访问 API；
一个域名可能解析到多个 IP；
抓包内容可能包含凭据或敏感业务数据，必须遵守安全规定；
大规模、长时间抓包会产生很大的文件。

如果应用日志显示单次请求耗时 5 秒，而抓包结果表明网络往返稳定且没有重传，那么延迟更可能出现在 API 服务端、代理排队或 response 生成阶段，而不是纯粹的网络传输过程。

同步和异步在 PL/SQL 中的含义

当前 procedure 使用同步串行模型。调用 HTTP package 后，当前 Oracle session 必须等待调用返回，之后才能继续处理下一行。

普通 PL/SQL procedure 不会自动把请求变成异步任务，也没有可以直接套用的浏览器 JavaScript event loop。要提高吞吐量，通常需要把工作拆成多个独立任务，例如：

DBMS_SCHEDULER worker；
Oracle AQ 或其他消息队列；
数据库外的 worker service；
多个 session 从任务表领取待处理记录。

一种常见架构是：

PAYMENTS
   -> 生成任务记录
   -> 多个 worker 领取任务
   -> 每个 worker 同步调用 API
   -> 保存结果和处理状态

每个并行 worker 仍会在自己的 session 中同步等待，只是多个请求可以同时进行。

并行度不能盲目增加，还要考虑以下限制：

API rate limit；
API 允许的并发连接数；
Oracle connection pool 容量；
database CPU、PGA 和 process/session 上限；
网络出口容量；
下游表的写入争用；
能否保证同一个 payment 不会被多个 worker 重复领取。

使用任务表时，可以通过状态列和合适的锁定策略领取任务。例如，在符合业务语义和 Oracle 版本要求的情况下，可以使用 FOR UPDATE SKIP LOCKED，让多个 worker 跳过已被其他 worker 锁定的任务。完整实现还要处理 worker 崩溃后的任务回收，不能只增加这一条子句。

建议的容错结构

每行处理都应设置独立的异常边界，避免一条坏数据导致整个批次失败：

FOR c1 IN transactions LOOP
  SAVEPOINT before_payment;

  BEGIN
    MY_SCHEMA.MY_PACKAGE.MAKE_HTTP_REQUEST_TO_API(
      URL,
      c1.id,
      response_xml
    );

    l_xmltype := XMLTYPE(response_xml);

    -- Parse and validate XML

    INSERT INTO target_table (...) VALUES (...);

    UPDATE payment_work_queue
       SET status       = 'DONE',
           completed_at = SYSTIMESTAMP
     WHERE payment_id = c1.id;

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO before_payment;

      UPDATE payment_work_queue
         SET status        = 'FAILED',
             attempt_count = attempt_count + 1,
             error_code    = SQLCODE,
             error_message = SUBSTR(SQLERRM, 1, 1000),
             updated_at    = SYSTIMESTAMP
       WHERE payment_id = c1.id;
  END;

  -- 根据测试结果按批次提交
END LOOP;

实际实现不能无条件吞掉所有异常，需要区分：

可重试：临时网络错误、部分 5xx、符合 API 约定的 429；
不可重试：认证失败、参数错误、业务拒绝、永久性 XML 格式问题；
状态不确定：请求可能已经成功，但未收到 response。

重试应限制次数，并使用 exponential backoff 和 jitter。不要让 database session 无限重试或长时间执行 SLEEP。大量延迟重试更适合交给任务调度器或队列管理。

实施优先级

建议按以下顺序处理：

分别记录 HTTP 调用、XML 解析和数据库写入的耗时。
设置明确并经过验证的连接、读取或传输 timeout。
为每条任务记录可追踪的状态、attempt count 和错误信息。
设计幂等机制，避免 timeout 后重试产生重复结果。
确认 HTTP connection 是否得到复用。
控制事务大小，既不要让整个批次处于同一个长事务中，也不要每行提交。
使用 session trace 和等待信息确认数据库内部成本。
只有证据表明串行吞吐量不足时，才引入受控的并行 worker。
使用 Wireshark 验证连接、重传和网络路径问题，不要把它作为唯一的性能分析手段。

只要 API 必须按行调用，就无法消除 (N) 次请求本身的成本。可以优化的是单次请求延迟、连接复用、受控并发和失败恢复能力，同时避免让一个长时间运行的 database session 承担整个批次的全部风险。