我之前处理过好几起类似的ORDS调用存储过程性能暴跌的问题，你的情况核心差异在于ORDS和SQL Developer的执行上下文完全不同——尤其是PGA内存限制、会话参数和执行计划的选择逻辑上。下面给你分模块拆解问题和对应的优化方案：

你自定义的string_to_table_num/string_to_table_varchar2很大概率是元凶：

• 如果函数是一次性生成完整的集合（比如先把所有拆分后的值塞进数组再返回），ORDS会话的PGA配额通常比SQL Developer小很多，一旦传入的数字列表过长，直接会触发ORA-04036。
• 替换成Oracle原生的高效拆分方式，比如12c+支持的JSON_TABLE，完全不用自定义函数，内存控制更优：

  -- 数字列表拆分示例
  SELECT column_value AS cat_id
  FROM JSON_TABLE('[' || :p_category_ids || ']', '$[*]' COLUMNS cat_id NUMBER PATH '$')
  

• 如果必须保留自定义函数，改成管道化函数，逐行返回结果而不是一次性生成整个集合：

  CREATE OR REPLACE FUNCTION string_to_table_num(p_str IN VARCHAR2)
  RETURN SYS.ODCINUMBERLIST PIPELINED
  IS
    v_start NUMBER := 1;
    v_end   NUMBER;
  BEGIN
    WHILE v_start <= LENGTH(p_str) LOOP
      v_end := INSTR(p_str, ',', v_start);
      v_end := NVL(v_end, LENGTH(p_str) + 1);
      PIPE ROW(TO_NUMBER(SUBSTR(p_str, v_start, v_end - v_start)));
      v_start := v_end + 1;
    END LOOP;
    RETURN;
  END;
  /
  

ORDS的连接池默认会话参数往往比SQL Developer保守，直接在存储过程开头临时调整：

• 临时提升PGA配额（根据你的业务场景调整，不要设得过大影响其他会话）：

  EXEC DBMS_SESSION.SET_SESSION_OPTION('PGA_AGGREGATE_TARGET', '256M');
  

• 禁用不必要的并行执行（并行查询会占用大量PGA资源）：

  ALTER SESSION SET PARALLEL_QUERY_ENABLED = FALSE;
  

ORDS传递参数的方式可能让Oracle无法正确预估集合行数，导致生成低效执行计划：

• 给IN查询的集合添加CARDINALITY提示，告诉Oracle集合的大致行数：

  SELECT *
  FROM category_prods cp
  WHERE cp.category_id IN (SELECT column_value FROM TABLE(string_to_table_num(:p_cat_ids)))
  OPTION (CARDINALITY(TABLE(string_to_table_num(:p_cat_ids)) 20)) -- 假设最多20个ID
  

• 检查参数类型匹配：确保传入的字符串参数长度和存储过程定义一致，避免Oracle隐式转换导致计划失效。

apex_json比dbms_output的内存开销大得多，尤其是返回大量数据时：

• 不要一次性生成整个JSON对象，改用逐行写入的方式：

  apex_json.open_object;
  apex_json.open_array('category_products');
  FOR prod_rec IN (SELECT * FROM ...) LOOP
    apex_json.open_object;
    apex_json.write('prod_id', prod_rec.prod_id);
    apex_json.write('prod_name', prod_rec.prod_name);
    apex_json.close_object;
  END LOOP;
  apex_json.close_array;
  apex_json.close_object;
  

• 如果返回数据量极大，考虑分页返回结果，避免一次性加载所有数据到内存。

用DBMS_XPLAN分别获取ORDS和SQL Developer环境下的执行计划，看是否有差异：

DECLARE
  v_plan CLOB;
BEGIN
  -- 替换成你的查询语句
  EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR SELECT * FROM category_prods WHERE category_id IN (SELECT column_value FROM TABLE(string_to_table_num(:1)))' USING '1,2,3';
  SELECT DBMS_XPLAN.DISPLAY INTO v_plan FROM DUAL;
  DBMS_OUTPUT.PUT_LINE(v_plan);
END;
/

如果计划不同，大概率是ORDS会话的OPTIMIZER_MODE或其他参数和SQL Developer不一致，比如ORDS用了FIRST_ROWS而SQL Developer用了ALL_ROWS，临时调整会话的优化器模式即可：

ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;

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