Hi Team/Steven.
We have one table"A" with 100000 records based on "A" Table we have sequence id we are using that sequence id and fetching two more tables like
insert into gtt_table select * from b join c where B.id=c.id and b.seq_id=rec.seq_id;
Note: Its taking to much time for fetching and inserting data into GTT Table.
Please see the below my Procedure.and replay to me as soon as possible.
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;
OK, I'm lost... Your looping query is:
select /*+ parallel(A,4) */ OR_SESSION_ID ,STORE_NUMBER ,CHAIN_NUMBER from OCE_BULK_PENDING_OVERRIDE A
So parallel tells me this is going to return a LOT of records. But then you do this:
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;
A whole stack of queries against the table you are already looping through ?
And then we build an INSERT statement, and that too looks like it is being run for every row coming back in the driving loop ?
So it looks like a LOT of work being done on a row-by-row basis. You need to revisit that approach and look to more of a set based processing approach.
For example:
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 )
gets you some of those row-by-row counts as part of the outer single pass.