优化通过DBLINK插入含LOB列远程表数据的性能问题求助

针对你遇到的跨DBLINK插入带LOB列的远程表性能差、游标/批量收集无法处理远程LOB，且需要保留JSON_EXISTS过滤条件的问题，给出以下优化方案：

方案1：用DBMS_HS_PASSTHROUGH将过滤逻辑推到远程执行 #

让所有过滤（包括JSON_EXISTS）在远程数据库完成，仅返回符合条件的数据，减少网络传输开销，同时专门处理远程LOB列：

DECLARE
  v_cursor INTEGER;
  v_rows   INTEGER;
BEGIN
  v_cursor := DBMS_HS_PASSTHROUGH.open_cursor@dblink;
  -- 远程执行过滤查询
  DBMS_HS_PASSTHROUGH.parse@dblink(v_cursor, q'[
    SELECT
      "URL",
      tipo,
      fecha_evento,
      fecha_registro,
      usuario,
      cliente,
      contrato,
      referer,
      correlation_id,
      session_id,
      tracking_cookie,
      "JSON",
      "APPLICATION"
    FROM schema.remote_table
    WHERE fecha_evento BETWEEN trunc(SYSDATE - INTERVAL '1' MONTH, 'MONTH') AND trunc(SYSDATE, 'MONTH') - INTERVAL '1' DAY
      AND url IN ('a','b','c','d')
      AND NOT JSON_EXISTS("JSON", '$.response.pasofin.codoferta')
      AND "URL" NOT IN ('e','f')
  ]');
  
  v_rows := DBMS_HS_PASSTHROUGH.fetch_rows@dblink(v_cursor);
  WHILE v_rows > 0 LOOP
    INSERT INTO raw_data VALUES (
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 1),
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 2),
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 3),
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 4),
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 5),
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 6),
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 7),
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 8),
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 9),
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 10),
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 11),
      DBMS_HS_PASSTHROUGH.get_lob@dblink(v_cursor, 12), -- 专门读取远程LOB
      DBMS_HS_PASSTHROUGH.get_value@dblink(v_cursor, 13)
    );
    COMMIT; -- 按需调整提交批次，避免事务过大
    v_rows := DBMS_HS_PASSTHROUGH.fetch_rows@dblink(v_cursor);
  END LOOP;
  
  DBMS_HS_PASSTHROUGH.close_cursor@dblink(v_cursor);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_HS_PASSTHROUGH.close_cursor@dblink(v_cursor);
    RAISE;
END;
/

方案2：在远程库创建过滤视图，本地直接查询插入 #

在远程数据库创建包含所有过滤条件的视图，让Oracle自动将过滤逻辑推送到远程执行：
-- 先在远程数据库执行：

CREATE OR REPLACE VIEW schema.remote_filtered_view AS
SELECT
  "URL",
  tipo,
  fecha_evento,
  fecha_registro,
  usuario,
  cliente,
  contrato,
  referer,
  correlation_id,
  session_id,
  tracking_cookie,
  "JSON",
  "APPLICATION"
FROM schema.remote_table
WHERE fecha_evento BETWEEN trunc(SYSDATE - INTERVAL '1' MONTH, 'MONTH') AND trunc(SYSDATE, 'MONTH') - INTERVAL '1' DAY
  AND url IN ('a','b','c','d')
  AND NOT JSON_EXISTS("JSON", '$.response.pasofin.codoferta')
  AND "URL" NOT IN ('e','f');

-- 然后本地执行插入：

INSERT INTO raw_data
SELECT * FROM schema.remote_filtered_view@dblink;

方案3：用Data Pump网络模式批量同步（适合定期数据同步） #

如果是定期同步数据，Oracle Data Pump的网络模式（NETWORK_LINK）是性能最优的选择，采用块级传输，专门优化了LOB处理：

impdp 本地用户名/本地密码@本地数据库实例 directory=DATA_PUMP_DIR network_link=dblink 
tables=schema.remote_table 
query=\"WHERE fecha_evento BETWEEN trunc(SYSDATE - INTERVAL '1' MONTH, 'MONTH') AND trunc(SYSDATE, 'MONTH') - INTERVAL '1' DAY 
AND url IN ('a','b','c','d') 
AND NOT JSON_EXISTS(\\\"JSON\\\", '$.response.pasofin.codoferta') 
AND \\\"URL\\\" NOT IN ('e','f')\" 
remap_table=remote_table:raw_data

注意：需提前在本地创建DATA_PUMP_DIR目录，且确保用户有读写权限。

方案4：优化DBLINK参数与远程表索引 #

1. 调整DBLINK的取行参数，减少网络往返次数：

ALTER DATABASE LINK dblink SET HS_FDS_FETCH_ROWS=1000;

2. 在远程表的fecha_evento和url列创建组合索引，加速远程过滤查询：

-- 远程数据库执行
CREATE INDEX idx_remote_table_fecha_url ON schema.remote_table(fecha_evento, url);

核心原则 #

• 绝对避免在本地对远程LOB列做函数/谓词操作（比如JSON_EXISTS），否则Oracle会拉取所有远程LOB数据到本地处理，性能暴跌。
• 尽量将所有过滤逻辑推送到远程执行，只传输符合条件的数据。
• 大数量插入时，分批次提交，避免事务过大导致的日志和内存压力。

内容的提问来源于stack exchange，提问作者Javi Torre