Oracle 性能调整

嗨，团队/史蒂文。
我们有一个表 “A”，其中100000记录基于 “A” 表，我们有序列id，我们正在使用该序列id并获取另外两个表，例如
插入到gtt_table中选择 * 从b加入c，其中B.id = c.id和b.seq_id = rec.seq_id;
注意: 将数据提取并插入GTT表中需要花费大量时间。
请看下面我的程序。并尽快重播给我。

PROCEDURE sp_get_bulk_equal_conflicts(  
      out_equal_conflicts OUT sys_refcursor )
/***************************************************************************
    * Procedure:     sp_get_equal_conflicts
    *
    * Project:       DSG-OCE
    *
    * Purpose:       TO get the equal conflicts for the created overrides.
    *
    *
    * Developer:     TCSL
    *
    * Created Date:  23-JUN-2017
    *
    * Modification Details:
    * Date              USER STORY            Description
    * --------------------------------------------------------------------------
    *   
***************************************************************************/       
  IS
    IN_CONFLICT_DIRECTION  VARCHAR2(4000); 
    l_Current_Priority  VARCHAR2(4000);
    l_OVERRIDE_LEVEL    VARCHAR2(4000);
    l_Dept_Count        VARCHAR2(4000);
    l_SUB_DEPT_COUNT    VARCHAR2(4000);
    l_Class_Count       VARCHAR2(4000);
    l_Sub_Class_Count   VARCHAR2(4000);
    l_Style_Count       VARCHAR2(4000);
    l_SKU_Count         VARCHAR2(4000);
    l_Style_Count_Lower VARCHAR2(4000);
    l_SKU_Count_Lower   NUMBER;
    l_Chain_Count       VARCHAR2(4000);  
    L_STORE_COUNT       VARCHAR2(4000);
    l_Start_Date        VARCHAR2(4000);
    l_Program_id        VARCHAR2(4000);
    l_End_date          VARCHAR2(4000);
    l_prodWhere         clob;
    l_prodWhereSS       clob;
    l_prodWhereSS2      clob;
    l_prodWhereTemp     clob;
    l_orgWhere          clob;
    l_orgWhere2         clob;
    l_conflictDirection clob;
    l_includedStatus clob;
    l_ssWhere        clob;
    l_query_str      clob;    
    l_query clob;
  BEGIN
  IN_CONFLICT_DIRECTION:='EQUAL';
    l_Current_Priority  :=null;
    l_OVERRIDE_LEVEL    :=null;
    l_Dept_Count        :=null;
    l_SUB_DEPT_COUNT    :=null;
    l_Class_Count       :=null;
    l_Sub_Class_Count   :=null;
    l_Style_Count       :=null;
    l_SKU_Count         :=null;
    l_Style_Count_Lower :=null;
    l_SKU_Count_Lower   :=null;
    l_Chain_Count       :=null;
 
    L_STORE_COUNT       :=null;
    l_Start_Date        :=null;
    l_Program_id        :=null;
    l_End_date          :=null;
    l_prodWhere         :=null;
    l_prodWhereSS       :=null;
    l_prodWhereSS2      :=null;
    l_prodWhereTemp     :=null;
    l_orgWhere          :=null;
    l_orgWhere2         :=null;
    l_conflictDirection :=null;
    --tmpcur acur;
    l_includedStatus :=null;
    l_ssWhere        :=null;
    l_query_str      :=null;
    

    execute immediate 'truncate table tmp_bulk_duplicate_conflicts';  

        for rec in (select  /*+ parallel(A,4) */  OR_SESSION_ID ,STORE_NUMBER ,CHAIN_NUMBER  from OCE_BULK_PENDING_OVERRIDE A ) loop
        
      

  --  IF isProcessRunning        = 'X' THEN
      IF in_CONFLICT_DIRECTION = 'EQUAL' THEN
        l_conflictDirection   := ' B.PRIORITY = ' ;
        l_includedStatus      := ' AND A.OVERRIDE_STATUS_ID IN (1, 2, 3) ';
      END IF;
      --NEED CURRENT PRIORITY BASED ON OVERRIDE_LEVEL
  
      SELECT PRIORITY
      INTO l_Current_Priority
      FROM oce_app2.OCE_OVERRIDE_LEVEL
      WHERE OVERRIDE_LEVEL =
        (SELECT DISTINCT OVERRIDE_LEVEL
        FROM oce_app2.OCE_BULK_PENDING_OVERRIDE
        WHERE or_session_id = rec.OR_SESSION_ID
        );
      SELECT DISTINCT OVERRIDE_LEVEL
      INTO l_OVERRIDE_LEVEL
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE OR_SESSION_ID = rec.OR_SESSION_ID;
      
           
      SELECT COUNT(STYLE_NUMBER)
      INTO l_Style_Count
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE or_session_id = rec.OR_SESSION_ID;
      
      SELECT COUNT(SKU_NUMBER)
      INTO l_SKU_Count
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE OR_SESSION_ID = rec.OR_SESSION_ID;
      
      SELECT COUNT(CHAIN_NUMBER)
      INTO l_Chain_Count
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE or_session_id = rec.OR_SESSION_ID;
          
      SELECT COUNT(STORE_NUMBER)
      INTO l_Store_Count
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE or_session_id = rec.OR_SESSION_ID;
      
      SELECT DISTINCT OVERRIDE_START_DATE
      INTO l_Start_Date
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE or_session_id = rec.OR_SESSION_ID;
      
      SELECT DISTINCT OVERRIDE_END_DATE
      INTO l_End_Date
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE or_session_id = rec.OR_SESSION_ID;
      
      SELECT DISTINCT PROGRAM_ID
      INTO l_Program_Id
      FROM  OCE_BULK_PENDING_OVERRIDE 
      WHERE OR_SESSION_ID = rec.OR_SESSION_ID;
      
      IF l_Program_id     = 5 THEN
        l_Program_id     := 4;
      END IF;
   IF l_Style_Count > 0 THEN
        l_prodWhere   := ' AND UPPER(A.STYLE_NUMBER) IN (select distinct style_number from oce_app2.OCE_BULK_PENDING_OVERRIDE where style_number is not null  and  OR_SESSION_ID=' ||rec.OR_SESSION_ID || ') ';
        l_prodWhereSS := l_prodWhereSS || l_prodWhere;
        l_ssWhere     := ' AND UPPER(trim(A.STYLE_NUMBER)) IN (SELECT DISTINCT STYLE_NUMBER FROM oce_app2.OCE_BULK_PENDING_OVERRIDE WHERE style_number is not null and or_session_id = ' || rec.or_session_id || ') ';
      END IF;
      IF l_SKU_Count   > 0 THEN
        l_prodWhere   := ' AND A.SKU_NUMBER IN (select distinct sku_number from oce_app2.OCE_BULK_PENDING_OVERRIDE where sku_number is not null  and or_session_id=' ||rec.or_session_id || ') ';
        l_prodWhereSS := l_prodWhereSS || ' AND A.SKU_NUMBER IN (SELECT DISTINCT SKU_NUMBER FROM oce_app2.OCE_BULK_PENDING_OVERRIDE  where sku_number is not null ) ';
        l_ssWhere     := ' AND A.SKU_NUMBER IN (SELECT DISTINCT SKU_NUMBER FROM oce_app2.OCE_BULK_PENDING_OVERRIDE WHERE sku_number is not null and or_session_id = ' || rec.or_session_id || ') ';
      END IF;
      --CHAIN
      IF l_Chain_Count > 0 THEN
        IF SUBSTR(l_OVERRIDE_LEVEL, INSTR(l_OVERRIDE_LEVEL, '-')+1 ) = 'STORE' THEN
          l_orgWhere := l_orgWhere;
        ELSE
          l_orgWhere := ' AND A.CHAIN_NUMBER IN ( ' || rec.CHAIN_NUMBER || ') ';
        END IF;
      END IF;
      --  --REGION
      --  IF l_Region_Count > 0 THEN
      --    IF SUBSTR(l_OVERRIDE_LEVEL, INSTR(l_OVERRIDE_LEVEL, '-')+1 ) = 'STORE' THEN
      --      l_orgWhere := l_orgWhere;
      --    ELSE
      --      l_orgWhere := l_orgWhere || ' AND A.REGION_NUMBER IN ( ' || p_region || ') ';
      --    END IF;
      --  END IF;
      --  --DISTRICT
      --  IF i_District_Count                                            > 0 THEN
      --    IF SUBSTR(p_OVERRIDE_LEVEL, INSTR(p_OVERRIDE_LEVEL, '-')+1 ) = 'STORE' THEN
      --      orgWhere                                                  := orgWhere;
      --    ELSE
      --      orgWhere := orgWhere || ' AND A.DISTRICT_NUMBER IN ( ' || p_district || ') ';
      --    END IF;
      --  END IF;
      --STORE
      IF l_Store_Count > 0 THEN
        l_orgWhere    :=' AND A.STORE_NUMBER IN ( ' || rec.STORE_NUMBER || ') ';
      END IF;
      COMMIT;
     L_query:= ' select     override_id EXISTINGOVERRIDEID,
      or_session_id  OverrideID
  ,eligibility Eligibility
  ,override_level OverrideLevel
  ,PRIORITY
  , DESCRIPTION
  ,ORG_DESCRIPTION OrganizationalLevel
  ,override_reason OverrideReason
  ,override_start_date StartDate
  ,override_end_date EndDate
  , OVERRIDE_STATUS
  ,created_by CreatedBy
  ,created_date CreatedDate
  ,safety_stock SS
  ,min_oh MinOH
  ,sku_number
  ,style_number
  ,override_comments
  ,decode(' ||l_Program_Id||',1,unalloc,null) Unallocatable,
  '||l_Program_Id||' Program_Id,
  CHAIN_NUMBER
  from
  (
  select  /*+ parallel(a,4) */  ' ||rec.or_session_id || '  or_session_id,
  a.override_id
  ,A.DEPARTMENT_NUMBER
  ,A.SUB_DEPARTMENT_NUMBER
  ,A.CLASS_NUMBER
  ,A.SUB_CLASS_NUMBER
  ,A.STYLE_NUMBER
  ,A.SKU_NUMBER
  ,A.CHAIN_NUMBER
  ,A.REGION_NUMBER
  ,A.DISTRICT_NUMBER
  ,A.STORE_NUMBER
  ,CASE
  WHEN trim(a.eligibility) = ''Y'' then ''ON''
  WHEN trim(A.ELIGIBILITY) = ''N'' then ''OFF''
  end eligibility
  ,a.override_level
  ,B.PRIORITY
  ,case
  WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''DEPT'' THEN A.DEPARTMENT_NUMBER || '' ''
  WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''SUBDEPT'' THEN A.DEPARTMENT_NUMBER || ''.'' || A.SUB_DEPARTMENT_NUMBER || '' ''
  WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''CLASS'' THEN A.DEPARTMENT_NUMBER || ''.'' || A.SUB_DEPARTMENT_NUMBER || ''.'' || A.CLASS_NUMBER || '' ''
  WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''SUBCLASS'' THEN A.DEPARTMENT_NUMBER || ''.'' || A.SUB_DEPARTMENT_NUMBER || ''.'' || A.CLASS_NUMBER || ''.'' || A.SUB_CLASS_NUMBER || '' ''
  WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''STYLE'' THEN A.STYLE_NUMBER || '' ''
  WHEN substr(a.override_level, 0, INSTR(a.override_level, ''-'')-1) = ''SKU'' THEN A.SKU_NUMBER || '' ''
  END DESCRIPTION
  ,CASE
  WHEN SUBSTR(a.override_level, INSTR(a.override_level, ''-'')+1 ) = ''CHAIN'' THEN ''CHAIN - '' || A.CHAIN_NUMBER
  WHEN SUBSTR(a.override_level, INSTR(a.override_level, ''-'')+1 ) = ''STORE'' THEN ''STORE - '' || A.STORE_NUMBER
  END ORG_DESCRIPTION
  ,a.override_reason
  ,a.override_start_date
  ,a.override_end_date
  ,case 
  WHEN A.OVERRIDE_STATUS_ID = 1 THEN ''FUTURE'' 
  WHEN A.OVERRIDE_STATUS_ID = 2 THEN ''PENDING'' 
  WHEN A.OVERRIDE_STATUS_ID = 3 THEN ''PROCESSED''
  END OVERRIDE_STATUS
  ,a.created_by
  ,a.created_date
  ,a.safety_stock
  ,a.min_oh
  ,c.min_oh unalloc 
  ,a.override_comments 
  FROM oce_app2.OCE_OVERRIDE A, oce_app2.OCE_OVERRIDE_LEVEL B, oce_app2.OCE_OVERRIDE C WHERE 
  A.OVERRIDE_ID = C.OVERRIDE_REF_ID(+) AND A.OVERRIDE_LEVEL = B.OVERRIDE_LEVEL 
   and A.CREATED_DATE<(select distinct max(created_date) from OCE_BULK_PENDING_OVERRIDE)
  AND A.OVERRIDE_STATUS_ID IN (1, 2, 3)
  AND A.PROGRAM_ID = '|| l_Program_Id || ' 
  AND
  (
  (A.OVERRIDE_START_DATE <= ' || l_Start_Date || ' AND A.OVERRIDE_END_DATE BETWEEN ' || l_Start_Date || ' AND ' || l_End_Date || ')
  OR
  (A.OVERRIDE_START_DATE BETWEEN ' || l_Start_Date || ' AND ' || l_End_Date || ')
  OR
  (A.OVERRIDE_END_DATE BETWEEN ' || l_Start_Date || ' AND ' || l_End_Date || ')
  OR
  (A.OVERRIDE_END_DATE = 99999999)
  OR
  (' || l_Start_Date || ' BETWEEN A.OVERRIDE_START_DATE AND A.OVERRIDE_END_DATE)
  OR
  (' || l_End_Date || ' BETWEEN A.OVERRIDE_START_DATE AND A.OVERRIDE_END_DATE)
  )
  AND ' || l_conflictDirection || ' ' || l_Current_Priority || ' 
  ' || l_orgWhere || ' 
  ) a
  where 1 = 1
  ' || l_prodWhere || ' 
  ORDER BY PRIORITY desc, description
  ';  
  --EXECUTE IMMEDIATE ('insert into TMP_BULK_DUPLICATE_CONFLICTS values l_query');
--  
   EXECUTE IMMEDIATE 'INSERT INTO  /*+ parallel(TMP_BULK_DUPLICATE_CONFLICTS,4) */  OCE_APP2.TMP_BULK_DUPLICATE_CONFLICTS (EXISTINGOVERRIDEID,
   OverrideID
  , Eligibility
  , OverrideLevel
  ,PRIORITY
  , DESCRIPTION
  , OrganizationalLevel
  , OverrideReason
  , StartDate
  ,EndDate
  , OVERRIDE_STATUS
  ,CreatedBy
  , CreatedDate
  ,SS
  , MinOH
  ,sku_number
  ,style_number
  ,override_comments
  ,Unallocatable,
  program_id,CHAIN_NUMBER)  ' || l_query;
  
  commit; 

    --END IF;
        end loop;     
        
         update   oce_override set OVERRIDE_STATUS_ID=5 where sku_number||style_number in(
select oo.sku_number||oo.style_number from TMP_BULK_DUPLICATE_CONFLICTS tbdc,oce_override oo
where (TBDC.SKU_NUMBER=OO.SKU_NUMBER
or TBDC.STYLE_NUMBER=OO.STYLE_NUMBER))
and CREATED_DATE<(select distinct max(created_date) from OCE_BULK_PENDING_OVERRIDE);
commit; 
        
      delete from      TMP_BULK_DUPLICATE_CONFLICTS   where  OVERRIDEID in (select EXISTINGOVERRIDEID    from      TMP_BULK_DUPLICATE_CONFLICTS);
       commit;                 
                    
                                  Delete From TMP_BULK_DUPLICATE_CONFLICTS T1
            Where T1.Rowid >
                     Any (Select T2.Rowid
                            From TMP_BULK_DUPLICATE_CONFLICTS T2
                           Where     T1.Description = T2.Description
                                 And T1.Organizationallevel = T2.Organizationallevel
                                 and t1.PROGRAM_ID=t2.PROGRAM_ID
                                 and t1.chain_number=t2.chain_number);
                                 
                                 Commit;   
        
        open out_equal_conflicts for select   OverrideID
        ,EXISTINGOVERRIDEID
         ,case when program_id=1 then 'SFS'
        when  program_id=3 then 'ISA'
        when program_id=4 then 'BOPIS' end programname
        ,case when CHAIN_NUMBER=1 then 'DSG'
    when CHAIN_NUMBER=3 then 'GG'
    when CHAIN_NUMBER=7 then 'FS' end  Banner
  , Eligibility
  , OverrideLevel
  --,PRIORITY
  , DESCRIPTION
  , OrganizationalLevel
  , OverrideReason
  , StartDate
  , EndDate
  , OVERRIDE_STATUS STATUS
  , SS
  , MinOH
  --,sku_number
  --,style_number
  ,override_comments
  ,Unallocatable
    , CreatedBy
  , CreatedDate  from TMP_BULK_DUPLICATE_CONFLICTS;
  
EXCEPTION
      WHEN OTHERS
      THEN
         l_fail := 'Error Backtrace';
         L_ERROR_MSG := SUBSTR (SQLERRM, 1, 250);
         pkg_oce_common.sp_ui_error_log ('Get Equal Conflicts',
                          'sp_get_bulk_equal_conflicts',
                          l_err_start_date,
                          SYSDATE,
                          l_fail,
                          l_error_msg,
                          dbms_utility.format_error_backtrace());
      RAISE;
      END sp_get_bulk_equal_conflicts;

复制

好吧，我迷路了...您的循环查询是:

从OCE_BULK_PENDING_OVERRIDE选择/* 并行 (A，4) */或 _ 会话 _ id，存储 _ 编号，链 _ 编号

所以平行告诉我这将返回很多记录。但是你这样做:

SELECT PRIORITY
      INTO l_Current_Priority
      FROM oce_app2.OCE_OVERRIDE_LEVEL
      WHERE OVERRIDE_LEVEL =
        (SELECT DISTINCT OVERRIDE_LEVEL
        FROM oce_app2.OCE_BULK_PENDING_OVERRIDE
        WHERE or_session_id = rec.OR_SESSION_ID
        );
      SELECT DISTINCT OVERRIDE_LEVEL
      INTO l_OVERRIDE_LEVEL
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE OR_SESSION_ID = rec.OR_SESSION_ID;
      
           
      SELECT COUNT(STYLE_NUMBER)
      INTO l_Style_Count
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE or_session_id = rec.OR_SESSION_ID;
      
      SELECT COUNT(SKU_NUMBER)
      INTO l_SKU_Count
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE OR_SESSION_ID = rec.OR_SESSION_ID;
      
      SELECT COUNT(CHAIN_NUMBER)
      INTO l_Chain_Count
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE or_session_id = rec.OR_SESSION_ID;
          
      SELECT COUNT(STORE_NUMBER)
      INTO l_Store_Count
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE or_session_id = rec.OR_SESSION_ID;
      
      SELECT DISTINCT OVERRIDE_START_DATE
      INTO l_Start_Date
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE or_session_id = rec.OR_SESSION_ID;
      
      SELECT DISTINCT OVERRIDE_END_DATE
      INTO l_End_Date
      FROM OCE_BULK_PENDING_OVERRIDE
      WHERE or_session_id = rec.OR_SESSION_ID;
      
      SELECT DISTINCT PROGRAM_ID
      INTO l_Program_Id
      FROM  OCE_BULK_PENDING_OVERRIDE 
      WHERE OR_SESSION_ID = rec.OR_SESSION_ID;
复制

针对您已经循环浏览的表的一整套查询？

然后我们构建一个INSERT语句，看起来它也正在为驱动循环中的每一行运行？

因此，看起来很多工作都是在逐行的基础上完成的。您需要重新审视该方法，并寻求更多基于集合的处理方法。

例如:

select  /*+ parallel(A,4) */  OR_SESSION_ID ,STORE_NUMBER ,CHAIN_NUMBER  ,
      COUNT(STYLE_NUMBER) over ( partition by OR_SESSION_ID )
      COUNT(SKU_NUMBER) over ( partition by OR_SESSION_ID )
      COUNT(CHAIN_NUMBER) over ( partition by OR_SESSION_ID )
      COUNT(STORE_NUMBER) over ( partition by OR_SESSION_ID )
复制

获取一些逐行计数作为外部单次通过的一部分。