DB2 11.5/AIX存储过程：IN参数用于FETCH FIRST时游标性能异常缓慢求助

问题分析 #

当存储过程使用IN参数nINC配合FETCH FIRST nINC ROWS ONLY时，DB2优化器无法提前确定参数具体值，可能生成未利用SEQ列索引（若存在）的执行计划，比如全表扫描后排序，导致性能下降；而使用固定值5时，优化器能直接生成最优计划，快速定位前N行数据。

解决方案（均保留FOR循环语法） #

方案1：添加OPTIMIZE FOR提示引导优化器 #

在SELECT语句中加入OPTIMIZE FOR nINC ROWS，让优化器针对参数指定的行数生成执行计划：

CREATE OR REPLACE PROCEDURE TEST_INC (IN nINC INTEGER)
BEGIN
   FOR EACH_RECORD AS C1 CURSOR FOR 
      SELECT * 
      FROM MYTABLE 
      WHERE SEQ > 0 
      ORDER BY SEQ 
      OPTIMIZE FOR nINC ROWS
      FETCH FIRST nINC ROWS ONLY
   DO
   -- 原有业务逻辑
   ...
   END FOR;
END;

方案2：使用动态SQL构造游标 #

通过动态SQL将参数值嵌入查询，让优化器每次执行时根据实际参数值生成最优计划：

CREATE OR REPLACE PROCEDURE TEST_INC (IN nINC INTEGER)
BEGIN
   DECLARE v_sql VARCHAR(1000);
   DECLARE stmt STATEMENT;

   SET v_sql = 'SELECT * FROM MYTABLE WHERE SEQ > 0 ORDER BY SEQ FETCH FIRST ' || CHAR(nINC) || ' ROWS ONLY';
   PREPARE stmt FROM v_sql;

   FOR EACH_RECORD AS C1 CURSOR FOR stmt
   DO
   -- 原有业务逻辑
   ...
   END FOR;

   DEALLOCATE PREPARE stmt;
END;

方案3：确保SEQ列存在合适索引并刷新统计信息 #

首先检查MYTABLE是否在SEQ列上创建升序索引，没有则创建：

CREATE INDEX IDX_MYTABLE_SEQ ON MYTABLE(SEQ ASC);

若已有索引，刷新表统计信息并重新绑定存储过程，让优化器获取最新数据分布：

RUNSTATS ON TABLE MYTABLE WITH DISTRIBUTION AND DETAILED INDEXES ALL;
REBIND PROCEDURE TEST_INC;

方案4：使用低隔离级别优化（业务允许时） #

如果业务允许脏读，添加WITH UR（未提交读）隔离级别，减少锁开销并加快查询：

CREATE OR REPLACE PROCEDURE TEST_INC (IN nINC INTEGER)
BEGIN
   FOR EACH_RECORD AS C1 CURSOR FOR 
      SELECT * 
      FROM MYTABLE 
      WHERE SEQ > 0 
      ORDER BY SEQ 
      FETCH FIRST nINC ROWS ONLY
      WITH UR
   DO
   -- 原有业务逻辑
   ...
   END FOR;
END;

内容的提问来源于stack exchange，提问作者André Barbosa