Oracle百万级数据批量查询性能优化求助：BULK COLLECT超时

问题描述 #

从Oracle数据库提取600万+条记录时，存储过程耗时过长导致会话过期，UI页面报错。尝试使用BULK COLLECT并添加ROWNUM <=100000条件，性能无明显改善。

原存储过程代码 #

PROCEDURE SP_GET_RAW_DATA_EXTRACT(
                                P_I_GPLTO IN VARCHAR2,
                                P_I_YEARS IN VARCHAR2,
                                P_I_PROJ_OWNERS IN VARCHAR2,
                                P_I_IMPLEMENTATIN_LEVELS IN VARCHAR2,
                                P_I_BU_SITES IN t_BU_SITE_IDS,
                                P_O_REPORT OUT REF_CURSOR
                                ) IS
V_COUNT_CNTR NUMBER;
V_SQL VARCHAR2(4000);
V_WHERE VARCHAR2(4000);

TYPE R_ID IS RECORD(
      ID VARCHAR2(4000));
--create a table where each subscript holds a record type
TYPE T_IDS IS TABLE OF R_ID;
V_ALLOC_COLS T_IDS;
V_LVL1           VARCHAR2(1000);
V_LVL2           VARCHAR2(1000);
V_LVL3           VARCHAR2(1000);
V_LVL4           VARCHAR2(1000);
V_LVL5           VARCHAR2(1000);
V_SITE           VARCHAR2(1000);
V_CNTR           VARCHAR2(1000);
BEGIN


 V_SQL:='SELECT 
 "PROGRAMMEID","PROGRAMMENAME","PROJECTID","PROJECTNAME","PROJECTDESCRIPTION","SYNERGYPROJECT","FUTUREREADYPROJECT",
 "PROJECTSTATUS","CANCELREASON","LASTUPDATEDBY","LASTUPDATEDBYNAME","LASTUPDATEDDATE","GPLT0","GPLT1","PROJECTOWNER",
 "PROJECTOWNERNAME","PROJECTDOCUMENTATION","BASELINEVALUECREATION","FINANCEAPPROVAL","VALUELINEID",
 "VALUELINENAME","BULEVEL1","BULEVEL2","BULEVEL3","BULEVEL4","BULEVEL5","COUNTRY","SITE","IMPLEMENTATIONLEVEL","EXPECTEDIL3DATE","GPLT2",
 "COMMODITY_CODE","COMMCODEDESC","VALUEOWNERID","VALUEOWNERNAME","SITELOCALALIGNED","SITELOCAPPROVEDBY","VALUECREATION",
 "BASELINECALCULATION","FINANCEVIEW","WORKINGCAPITALTYPE","INVENTORYLEVER","PRIMPROCLEVER","SECPROCLEVER",
 "PIRMSPENDPUTGBP","SECDSPENDPUTGBP","DIRECTPLACCOUNT","FINANCELEAD","FINANCELEADNAME",
 "CATEGORYFRANCHISE","FISHBONE","IPW","SYNERGYTYPE","RAGSTATUS","SPENDSAVINGSOWNER","TECHNICALSUPPORTNEEDED","SUPPLIERNAME",
 "PIPELINECOMMENTS","DELIVERYSTARTMONTHYEAR","YEAROFDELIVERY","PIPELINECURRENCY","DELIVERYMONTH",YEAR,"PIPELINEAMOUNTLOCAL",
 "PIPELINEAMOUNTGBP","SORTORDER","GPLT0ID","PROJECTOWNERMUDID","ILCODE","LEVEL1ID","LEVEL2ID","LEVEL3ID","LEVEL4ID",
 "LEVEL5ID","COUNTRYID","SITEID","CREATEDBY","CREATEDBYNAME","CREATEDON","TECHNICALCOMPLIANCE","PROJECTSIZE","ESTIMATEDSPENDLOCAL","ESTIMATEDSPENDGBP",
 CASE WHEN FUTUREORGANIZATION = 1 THEN ''New GSK''
WHEN FUTUREORGANIZATION = 2 THEN ''Cx''
ELSE ''NA''
END  "FUTUREORGANIZATION","ARCHIVE_CC" FROM VW_GLXY3_RAWDATA';
 V_WHERE:=' WHERE PROJECTSTATUS IN (''OPEN'',''ARCHIVED'')';

IF P_I_GPLTO IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND GPLT0ID IN(' || P_I_GPLTO ||')';
END IF;
IF P_I_YEARS IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND YEAR IN(' || P_I_YEARS ||')';
END IF;
IF P_I_PROJ_OWNERS IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND UPPER(PROJECTOWNERMUDID) IN(' || UPPER(P_I_PROJ_OWNERS) ||')';
END IF;
IF P_I_IMPLEMENTATIN_LEVELS IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND ILCODE IN(' || P_I_IMPLEMENTATIN_LEVELS ||')';
END IF;

---IF P_I_BU_SITES IS NOT NULL THEN
--SPLITTING ALL BU SITE DATA INTO BU SITE LEVELS
FOR I IN 1..P_I_BU_SITES.COUNT LOOP
  SELECT COLUMN_VALUE BULK COLLECT
         INTO V_ALLOC_COLS 
    FROM TABLE (SPLIT(P_I_BU_SITES(I), ';')) WHERE rownum <= 50000 ;
   exit when V_ALLOC_COLS.count =0;
IF(NVL(V_ALLOC_COLS(1).ID,0)>0) THEN
  IF V_LVL1 IS NULL THEN
  V_LVL1:=V_ALLOC_COLS(1).ID;
  ELSE
  V_LVL1:= V_LVL1||','||V_ALLOC_COLS(1).ID;
  END IF;
END IF;
IF(NVL(V_ALLOC_COLS(2).ID,0)>0) THEN
  IF V_LVL2 IS NULL THEN
  V_LVL2:=V_ALLOC_COLS(2).ID;
  ELSE
  V_LVL2:= V_LVL2||','||V_ALLOC_COLS(2).ID;
  END IF;
END IF;
IF(NVL(V_ALLOC_COLS(3).ID,0)>0) THEN
  IF V_LVL3 IS NULL THEN
  V_LVL3:=V_ALLOC_COLS(3).ID;
  ELSE
  V_LVL3:= V_LVL3||','||V_ALLOC_COLS(3).ID;
  END IF;
END IF;
IF(NVL(V_ALLOC_COLS(4).ID,0)>0) THEN
  IF V_LVL4 IS NULL THEN
  V_LVL4:=V_ALLOC_COLS(4).ID;
  ELSE
  V_LVL4:= V_LVL4||','||V_ALLOC_COLS(4).ID;
  END IF;
END IF;
IF(NVL(V_ALLOC_COLS(5).ID,0)>0) THEN
  IF V_LVL5 IS NULL THEN
  V_LVL5:=V_ALLOC_COLS(5).ID;
  ELSE
  V_LVL5:= V_LVL5||','||V_ALLOC_COLS(5).ID;
  END IF;
END IF;
IF(NVL(V_ALLOC_COLS(6).ID,0)>0) THEN
  IF V_SITE IS NULL THEN
  V_SITE:=V_ALLOC_COLS(6).ID;
  ELSE
  V_SITE:= V_SITE||','||V_ALLOC_COLS(6).ID;
  END IF;
END IF;
IF NVL(V_ALLOC_COLS(7).ID,0)>0 THEN
 SELECT  COUNT(*) INTO V_COUNT_CNTR FROM TBL_BU_SITE_MASTER WHERE PARENT_ID =V_ALLOC_COLS(7).ID AND BU_TYPE_CODE='CNTR' AND ACTIVE='Y';
   IF V_COUNT_CNTR>0 THEN
   IF V_CNTR IS NULL THEN
    V_CNTR:= V_ALLOC_COLS(7).ID;
    ELSE
    V_CNTR:= V_CNTR||','||V_ALLOC_COLS(7).ID;
   END IF;
   END IF;
END IF;
END LOOP;
--END IF;

--Applying filter on BU site values
IF V_LVL1 IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND LEVEL1ID IN(' || V_LVL1 ||')';
END IF;

IF V_LVL2 IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND LEVEL2ID IN(' || V_LVL2 ||')';
END IF;
IF V_LVL3 IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND LEVEL3ID IN(' || V_LVL3 ||')';
END IF;
IF V_LVL4 IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND LEVEL4ID IN(' || V_LVL4 ||')';
END IF;
IF V_LVL5 IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND LEVEL5ID IN(' || V_LVL5 ||')';
END IF;
IF V_SITE IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND SITEID IN(' || V_SITE ||')';
END IF;
IF V_CNTR IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND COUNTRYID IN(' || V_CNTR ||')';
END IF;
---dbms_output.put_line('WHERE:'||V_WHERE);
V_SQL:=V_SQL||V_WHERE ;
---DBMS_OUTPUT.PUT_LINE(V_SQL);
OPEN P_O_REPORT FOR V_SQL ;
 ---  FETCH P_O_REPORT  BULK COLLECT into V_ALLOC_COLS LIMIT 100;
  --- CLOSE P_O_REPORT;
END SP_GET_RAW_DATA_EXTRACT;

优化方案 #

1. 正确使用BULK COLLECT分批提取 #

当前代码中BULK COLLECT被注释，且直接返回大游标给UI，导致一次性加载大量数据占用内存并超时。改为分批提取：

-- 修改存储过程的返回逻辑，改用批量处理
DECLARE
  -- 定义与视图结构匹配的记录类型
  TYPE RAW_DATA_REC IS TABLE OF VW_GLXY3_RAWDATA%ROWTYPE;
  V_DATA_BATCH RAW_DATA_REC;
BEGIN
  OPEN P_O_REPORT FOR V_SQL;
  LOOP
    -- 每次提取1000条（可根据内存调整，建议1000-10000）
    FETCH P_O_REPORT BULK COLLECT INTO V_DATA_BATCH LIMIT 1000;
    EXIT WHEN V_DATA_BATCH.COUNT = 0;
    
    -- 这里添加数据处理逻辑，比如写入临时表、分批次推送给前端等
    -- 避免一次性将所有数据加载到内存
  END LOOP;
  CLOSE P_O_REPORT;
END;

2. 优化动态SQL拼接，避免性能损耗与注入风险 #

• 替换字符串拼接的IN条件为绑定变量集合：
  比如将P_I_GPLTO改为集合类型参数，或在存储过程中拆分后用TABLE()函数绑定：

  AND GPLT0ID IN (SELECT COLUMN_VALUE FROM TABLE(:P_I_GPLTO_LIST))
  

• 批量处理P_I_BU_SITES的COUNTRYID查询，减少循环内的SQL执行次数：

  -- 一次性收集所有符合条件的COUNTRYID
  SELECT PARENT_ID
  BULK COLLECT INTO V_CNTR_LIST
  FROM TBL_BU_SITE_MASTER
  WHERE PARENT_ID IN (
    SELECT T.COLUMN_VALUE 
    FROM TABLE(P_I_BU_SITES) S, 
         TABLE(SPLIT(S.COLUMN_VALUE, ';')) T 
    WHERE ROWNUM <= 50000
  )
  AND BU_TYPE_CODE='CNTR' AND ACTIVE='Y';
  
  -- 后续用绑定变量拼接条件
  V_WHERE := V_WHERE || ' AND COUNTRYID IN (SELECT COLUMN_VALUE FROM TABLE(:V_CNTR_LIST))';
  

3. 优化视图与索引 #

• 查看VW_GLXY3_RAWDATA的执行计划，检查是否存在全表扫描，给过滤字段（PROJECTSTATUS, GPLT0ID, YEAR, LEVEL1ID等）添加合适的索引
• 若视图包含复杂JOIN或子查询，考虑重构视图逻辑，或改用物化视图预计算数据

4. 前端与会话优化 #

• 不要让UI一次性加载600万条数据，改为分页加载，前端每次请求1000-10000条
• 临时调整数据库会话超时时间（不建议作为长期方案）：

  ALTER SESSION SET SQLNET.EXPIRE_TIME = 30; -- 设置30分钟超时
  

5. 避免字符串长度限制问题 #

当前V_LVL1等变量为VARCHAR2(1000)，筛选条件过多时会截断字符串，改用CLOB类型或集合类型存储筛选值。

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