OraFAQ Forum: Performance Tuning » Cursor Optimization

Home » RDBMS Server » Performance Tuning » Cursor Optimization (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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?

Code: [Select all] [Show/ hide]

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: 8976
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.

Report message to a moderator

Re: Cursor Optimization [message #656753 is a reply to message #656751]

Tue, 18 October 2016 02:42

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why a loop and not just one INSERT SELECT?
And committing each row is sure a performances killer, not just for you but for everyone logged in the database.

[Updated on: Tue, 18 October 2016 02:42]

Report message to a moderator

Re: Cursor Optimization [message #656754 is a reply to message #656751]

Tue, 18 October 2016 02:45

cookiemonster
Messages: 13966
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.

Report message to a moderator

Re: Cursor Optimization [message #656756 is a reply to message #656753]

Tue, 18 October 2016 04:32

abhijeetkumar.patil@cogni
Messages: 3
Registered: October 2016

Junior Member

My developer removed distinct and kept only group by but still the query is taking time. How can we convert this query with single insert without looping?

Report message to a moderator

Re: Cursor Optimization [message #656758 is a reply to message #656756]

Tue, 18 October 2016 04:37

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

abhijeetkumar.patil@cogni wrote on Tue, 18 October 2016 10:32

My developer removed distinct and kept only group by but still the query is taking time. How can we convert this query with single insert without looping?

I did say that you need to revisit the entire query. Another example: why is your developer using ORDER BY in his cursor? He needs to understand his data and he needs to understand what he is trying to do. Would you like him to attend a SQL Tuning class I'm running next week?
http://www.skillbuilders.com/instructor-led-training/Course_outlines/new/course-description.cfm?c=new/oracle-sql-tuning-class&id= 627

Report message to a moderator

Re: Cursor Optimization [message #656759 is a reply to message #656758]

Tue, 18 October 2016 04:46

abhijeetkumar.patil@cogni
Messages: 3
Registered: October 2016

Junior Member

Can somebody please help me in optimizing this query? ORDER BY is not taking any time. I agree it should not appear. Also we have removed distinct clause as group by already exists.

Report message to a moderator

Re: Cursor Optimization [message #656760 is a reply to message #656759]

Tue, 18 October 2016 04:50

John Watson
Messages: 8976
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.

Report message to a moderator

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

Code: [Select all] [Show/ hide]

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 --...)

Code: [Select all] [Show/ hide]

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)

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Previous Topic:	Exceeding maxi,um number of cursors
Next Topic:	DATABASE TUNING PRINCIPLE AND DATABASE TROUBLESHOOTING.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 21:56:01 CDT 2025