Home » RDBMS Server » Performance Tuning » Cursor Optimization (Oracle 11g)
| Cursor Optimization [message #656751] |
Tue, 18 October 2016 02:19  |
 |
abhijeetkumar.patil@cogni
Messages: 3
Registered: October 2016
|
Junior Member |
|
|
I have below procedure which is running for 8 hours to load 493274 records into lkp_cpo_site_link_ri_sn table.
Distinct ref_key_global values in comp_check_top_fp_mat_sn are 17420
and TGT_BOMS table hold 349507 records.
Can we optimize this procedure?
create or replace procedure test_cpo_sites_ri_sn as
cursor c1 is
select distinct po1.ref_key_global from comp_check_top_fp_mat_sn po1 order by po1.ref_key_global;
tmp1_text varchar2(2000);
begin
execute immediate 'truncate table lkp_cpo_site_link_ri_sn';
for v_cur2 in c1
loop
tmp1_text := 'insert into lkp_cpo_site_link_ri_sn(
select distinct
'''||v_cur2.ref_key_global||''', plant,
proc_type,
spl_proc_type,
SRC_MAT_TYPE,
vendor,
vendor_plant,
global_material,
VENDOR_SOURCE_SYSTEM,
vendor_name,
CURRENT_VENDOR
from TGT_BOMS e
where
-- ((nvl(e.prod_version,''XXXX'') not like ''V%'') OR (nvl(e.prod_version,''XXXX'') like ''V%''and plant = ''US25''))
start with ltrim(ref_key_global, ''0'')= '''||v_cur2.ref_key_global||'''connect by nocycle prior child_ref_key_global = ref_key_global
group by plant,
proc_type,
spl_proc_type,
SRC_MAT_TYPE,
vendor,
vendor_plant,
global_material,
VENDOR_SOURCE_SYSTEM,
vendor_name,
CURRENT_VENDOR
having SRC_MAT_TYPE in (''ZRI'')
)';
execute immediate (tmp1_text);
commit;
end loop;
end;
Thanks,
Abhijeet
[mod-edit: code tags added by bb; next time please add them yourself]
[Updated on: Tue, 18 October 2016 02:28] by Moderator Report message to a moderator |
|
|
|
| Re: Cursor Optimization [message #656752 is a reply to message #656751] |
Tue, 18 October 2016 02:41  |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
The query in your cursor loop begins with SELECT DISTINCT... and ends with GROUP BY... That is wrong: you are attempting to remove duplicates twice. I would hope that the optimizer will remove the unnecessary operation, but the fact that it is there shows that your developer doesn't understand what he is doing. If I am right, I think you need to revisit the whole query. I usually find that if there is one basic error, there are many more.
|
|
|
|
|
|
| Re: Cursor Optimization [message #656754 is a reply to message #656751] |
Tue, 18 October 2016 02:45 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'd start by getting rid of the completely unnecessary dynamic SQL - If you're not changing which tables/columns are referenced it's not dynamic.
Then I'd get rid of either the group by or the distinct - you never need both in the same select.
Make having part of the where clause instead - having is for applying restrictions to aggregates and src_mat_type isn't one.
Once that's all done run an explain plan for the resultant insert statement and post it here.
|
|
|
|
|
|
|
|
|
|
| Re: Cursor Optimization [message #656760 is a reply to message #656759] |
Tue, 18 October 2016 04:50 |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I would not attempt to optimize it. I would throw it away and start again. I have already pointed out two basic, and obvious, errors. CM and MC have pointed out others. How many more do you think there may be? Are you even sure that it actually does what what you want it to do?
Tell you programmer to do it again, and do it right.
|
|
|
|
| Re: Cursor Optimization [message #656973 is a reply to message #656760] |
Mon, 24 October 2016 14:56 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
some basic things you can do:
1. take the commit out of the loop and put it at the end
CREATE OR replace PROCEDURE Test_cpo_sites_ri_sn
AS
CURSOR c1 IS
SELECT DISTINCT po1.ref_key_global
FROM comp_check_top_fp_mat_sn po1
ORDER BY po1.ref_key_global;
tmp1_text VARCHAR2(2000);
BEGIN
EXECUTE IMMEDIATE 'truncate table lkp_cpo_site_link_ri_sn';
FOR v_cur2 IN c1 LOOP
tmp1_text := 'insert into lkp_cpo_site_link_ri_sn( select distinct '''
||v_cur2.ref_key_global
||''', plant, proc_type, spl_proc_type, SRC_MAT_TYPE, vendor, vendor_plant, global_material, VENDOR_SOURCE_SYSTEM, vendor_name, CURRENT_VENDOR from TGT_BOMS e where -- ((nvl(e.prod_version,''XXXX'') not like ''V%'') OR (nvl(e.prod_version,''XXXX'') like ''V%''and plant = ''US25'')) start with ltrim(ref_key_global, ''0'')= '''
||v_cur2.ref_key_global
||'''connect by nocycle prior child_ref_key_global = ref_key_global group by plant, proc_type, spl_proc_type, SRC_MAT_TYPE, vendor, vendor_plant, global_material, VENDOR_SOURCE_SYSTEM, vendor_name, CURRENT_VENDOR having SRC_MAT_TYPE in (''ZRI'') )';
EXECUTE IMMEDIATE (tmp1_text);
-- COMMIT;
END LOOP;
COMMIT;
END;
2. get rid of the dynamic sql (or use a parameterized dynamic sql if necessary (but not necessary in this case))
looks like you had an error in the code too (WHERE --...)
CREATE OR replace PROCEDURE Test_cpo_sites_ri_sn
AS
CURSOR c1 IS
SELECT DISTINCT po1.ref_key_global
FROM comp_check_top_fp_mat_sn po1
ORDER BY po1.ref_key_global;
tmp1_text VARCHAR2(2000);
BEGIN
EXECUTE IMMEDIATE 'truncate table lkp_cpo_site_link_ri_sn';
FOR v_cur2 IN c1 LOOP
insert into lkp_cpo_site_link_ri_sn
select distinct
v_cur2.ref_key_global
, plant
, proc_type
, spl_proc_type
, SRC_MAT_TYPE
, vendor
, vendor_plant
, global_material
, VENDOR_SOURCE_SYSTEM
, vendor_name
, CURRENT_VENDOR
from TGT_BOMS e
-- where ((nvl(e.prod_version,'XXXX') not like 'V%') OR (nvl(e.prod_version,'XXXX') like 'V%'and plant = 'US25'))
start with ltrim(ref_key_global, '0') = v_cur2.ref_key_global
connect by nocycle prior child_ref_key_global = ref_key_global
group by
plant
, proc_type
, spl_proc_type
, SRC_MAT_TYPE
, vendor
, vendor_plant
, global_material
, VENDOR_SOURCE_SYSTEM
, vendor_name
, CURRENT_VENDOR
having SRC_MAT_TYPE in ('ZRI')
;
-- COMMIT;
END LOOP;
COMMIT;
END;
3. move the having into the WHERE clause (assumes all chains you are producing are in the same SRC_MAT_TYPE)
CREATE OR replace PROCEDURE Test_cpo_sites_ri_sn
AS
CURSOR c1 IS
SELECT DISTINCT po1.ref_key_global
FROM comp_check_top_fp_mat_sn po1
ORDER BY po1.ref_key_global;
tmp1_text VARCHAR2(2000);
BEGIN
EXECUTE IMMEDIATE 'truncate table lkp_cpo_site_link_ri_sn';
FOR v_cur2 IN c1 LOOP
insert into lkp_cpo_site_link_ri_sn
with
my_tgt_boms as (
select *
from tgt_boms
where SRC_MAT_TYPE in ('ZRI')
)
select distinct
v_cur2.ref_key_global
, plant
, proc_type
, spl_proc_type
, SRC_MAT_TYPE
, vendor
, vendor_plant
, global_material
, VENDOR_SOURCE_SYSTEM
, vendor_name
, CURRENT_VENDOR
from my_TGT_BOMS e
-- where ((nvl(e.prod_version,'XXXX') not like 'V%') OR (nvl(e.prod_version,'XXXX') like 'V%'and plant = 'US25'))
start with ltrim(ref_key_global, '0') = v_cur2.ref_key_global
connect by nocycle prior child_ref_key_global = ref_key_global
group by
plant
, proc_type
, spl_proc_type
, SRC_MAT_TYPE
, vendor
, vendor_plant
, global_material
, VENDOR_SOURCE_SYSTEM
, vendor_name
, CURRENT_VENDOR
;
-- COMMIT;
END LOOP;
COMMIT;
END;
4. get rid of the LOOPING
I don't have time right now to look into this one. Maybe you can try it. It is comlicated a little bit by the connect by.
|
|
|
|
Goto Forum:
Current Time: Sun Mar 26 03:40:12 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
