Home » RDBMS Server » Performance Tuning » bulk collect help in production (XP ,Oracle 10g)
bulk collect help in production [message #312837] Wed, 09 April 2008 21:04 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi All

I have a cursor

 CURSOR    c_best_contact(a_CUG IN VARCHAR2) IS
        SELECT    ac_contact_key,
                ac_title,
                ac_forename,
                ac_surname,
                ac_full_name,
                ac_kdm,
                assk_kdm_flg,
                DECODE(
                        assk_kdm_flg,
                        'Key Decision Maker', '01',
                        'Operational KDM', '02',
                        'Strategic KDM', '03',
                        'Decision Maker', '04',
                        'Influencer', '05',
                        'Non-Decision Maker', '06',
                        '09'
                        ) kdm_order
        FROM    ACE2_CONTACT,
                ACE2_SMERF_SVEN_KDM
        WHERE    ac_customer_group_key=a_CUG
        AND      assk_conk = ac_contact_key
        AND      NVL(ac_deleted_flg,'A') = 'A'
        AND         ac_kdm IS NOT NULL  /* added for avoiding additional records from ACE2_contact table dt: 17/09/07*/
        ORDER BY
                  kdm_order ;

Its corresponding for loop goes like this

 FOR k IN c_best_contact(CUGTable(i).cugkey)
                            LOOP
                                  lv_RecordExist := 'TRUE' ;
                               BEGIN
                                       IF lv_Best_Contact_Found = 'FALSE' THEN
                                        IF p_output IN ('CM', 'EM', 'OM') THEN
                                            BEGIN
                                                SELECT    actc_telephone_number,
                                                        actc_tps_consent_value,
                                                        actc_ctps_consent_value,
                                                        NVL(actc_dnc_consent_value, 'C')
                                                INTO    lv_telephone_number,
                                                        lv_tps_consent_value,
                                                        lv_ctps_consent_value,
                                                        lv_telephone_consent
                                                FROM    ACE2_CONTACT_TELEPHONE_CONSENT,
                                                        ACE2_CONTACT
                                                WHERE    actc_contact_key = k.ac_contact_key
                                                AND      ac_contact_key = actc_contact_key
                                                AND      NVL(actc_deleted_flg, 'N') = 'N'
                                                AND      NVL(actc_primary_flg, 'N') = 'Y'
                                                AND      NVL(actc_dnc_consent_value, 'C') = 'C'
                                                AND     ((NVL(ac_full_name, ' ') <> ' ') OR
                                                         (LENGTH(NVL(actc_telephone_number,' ')) > 8))
                                AND       ac_kdm IS NOT NULL /* added for avoiding additional records from ACE2_contact table dt: 17/09/07*/
                                                AND ROWNUM = 1 /*to be removed later added temporarily for resolving the issuewith ACE2*/ ;
                                                   IF INSTR(lv_supp,'1', 1) <> 0 THEN   -- Check for TPS
                                                    IF (NVL(lv_tps_consent_value, 'C') = 'C' AND
                                                        NVL(lv_ctps_consent_value, 'C') = 'C') THEN
                                                        lv_tps_consent := 'C' ;
                                                    ELSE
                                                        lv_tps_consent := 'D' ;
                                                    END IF ;
                                                ELSE
                                                    lv_tps_consent := 'N' ;
                                                END IF ;
                                            EXCEPTION
                                                     WHEN NO_DATA_FOUND THEN
                                                           lv_telephone_consent := 'D' ;
                                                          lv_tps_consent := 'N' ;  -- earlier it was D
                                                          lv_telephone_number := NULL ;
                                                          lv_tps_consent_value := NULL ;
                                                          lv_ctps_consent_value := NULL ;
                                            END ;
...contnd
end loop;
In production it processes 50 million records,hence i feel if we can make use of bulk collect here then it can seriously improve performance,
as the currect issue is few queries are running for 2-3 days instead of 6-7 hrs hence huge problem

I am not 100% sure how to proceed,Please help me here

Thanks

[formatted by moderator]

[Updated on: Wed, 09 April 2008 21:50] by Moderator

Report message to a moderator

Re: bulk collect help in production [message #312838 is a reply to message #312837] Wed, 09 April 2008 21:05 Go to previous messageGo to next message
rasa
Messages: 45
Registered: February 2006
Member
Please visit the FAQ portion on the forum and format your code please.
Re: bulk collect help in production [message #312841 is a reply to message #312838] Wed, 09 April 2008 21:51 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Bulk collect is not your solution. You need to get rid of the SQL-within-cursor-loop.

See this article: http://www.orafaq.com/node/1399

Ross Leishman
Re: bulk collect help in production [message #313494 is a reply to message #312841] Sun, 13 April 2008 03:12 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
The records are processed from the cursor loop,How can it be taken out of the cursor,further batch processing are done based on this cursor?

Is there a simple way out?Please let me know.

Thanks
Re: bulk collect help in production [message #313558 is a reply to message #312837] Sun, 13 April 2008 13:39 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
The SELECT INTO can be nested into the main cursor (as an inline view) with an outer join. It is a singleton select, and as such will fit perfectly in your main cursor.

This will speed up your process massively.
Re: bulk collect help in production [message #313591 is a reply to message #313558] Sun, 13 April 2008 20:19 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
FOR k IN c_best_contact(CUGTable(i).cugkey)
LOOP
lv_RecordExist := 'TRUE' ;
BEGIN
IF lv_Best_Contact_Found = 'FALSE' THEN
IF p_output IN ('CM', 'EM', 'OM') THEN
BEGIN
SELECT actc_telephone_number,
actc_tps_consent_value,
actc_ctps_consent_value,
NVL(actc_dnc_consent_value, 'C')
INTO lv_telephone_number,
lv_tps_consent_value,
lv_ctps_consent_value,
lv_telephone_consent
FROM ACE2_CONTACT_TELEPHONE_CONSENT,
ACE2_CONTACT
WHERE actc_contact_key = k.ac_contact_key
AND ac_contact_key = actc_contact_key
AND NVL(actc_deleted_flg, 'N') = 'N'
AND NVL(actc_primary_flg, 'N') = 'Y'
AND NVL(actc_dnc_consent_value, 'C') = 'C'
AND ((NVL(ac_full_name, ' ') <> ' ') OR
(LENGTH(NVL(actc_telephone_number,' ')) > Cool)
AND ac_kdm IS NOT NULL /* added for avoiding additional records from ACE2_contact table dt: 17/09/07*/
AND ROWNUM = 1 /*to be removed later added temporarily for resolving the issuewith ACE2*/ ;
IF INSTR(lv_supp,'1', 1) <> 0 THEN -- Check for TPS
IF (NVL(lv_tps_consent_value, 'C') = 'C' AND
NVL(lv_ctps_consent_value, 'C') = 'C') THEN
lv_tps_consent := 'C' ;
ELSE
lv_tps_consent := 'D' ;
END IF ;
ELSE
lv_tps_consent := 'N' ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_telephone_consent := 'D' ;
lv_tps_consent := 'N' ; -- earlier it was D
lv_telephone_number := NULL ;
lv_tps_consent_value := NULL ;
lv_ctps_consent_value := NULL ;
END ;
...contnd
end loop;

The SELECT INTO can be nested into the main cursor
--not getting this

Are you talking about this for loop statement,please note that the table type variable processes for all those values and this is passed from the parameterised cursor above,if i take this out
then how can i process??

Thanks
Re: bulk collect help in production [message #313592 is a reply to message #312837] Sun, 13 April 2008 20:28 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You choose to ignore posting guidelines, we choose to not answer your questions.
Re: bulk collect help in production [message #313795 is a reply to message #313592] Mon, 14 April 2008 10:34 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member

BEGIN
IF lv_Best_Contact_Found = 'FALSE' THEN
IF p_output IN ('CM', 'EM', 'OM') THEN
BEGIN
SELECT actc_telephone_number,
actc_tps_consent_value,
actc_ctps_consent_value,
NVL(actc_dnc_consent_value, 'C')
INTO lv_telephone_number,
lv_tps_consent_value,
lv_ctps_consent_value,
lv_telephone_consent
FROM ACE2_CONTACT_TELEPHONE_CONSENT,
ACE2_CONTACT
WHERE actc_contact_key = k.ac_contact_key
AND ac_contact_key = actc_contact_key
AND NVL(actc_deleted_flg, 'N') = 'N'
AND NVL(actc_primary_flg, 'N') = 'Y'
AND NVL(actc_dnc_consent_value, 'C') = 'C'
AND ((NVL(ac_full_name, ' ') <> ' ') OR
(LENGTH(NVL(actc_telephone_number,' ')) > Cool)
AND ac_kdm IS NOT NULL /* added for avoiding additional records from ACE2_contact table dt: 17/09/07*/
AND ROWNUM = 1 /*to be removed later added temporarily for resolving the issuewith ACE2*/ ;
IF INSTR(lv_supp,'1', 1) <> 0 THEN -- Check for TPS
IF (NVL(lv_tps_consent_value, 'C') = 'C' AND
NVL(lv_ctps_consent_value, 'C') = 'C') THEN
lv_tps_consent := 'C' ;
ELSE
lv_tps_consent := 'D' ;
END IF ;
ELSE
lv_tps_consent := 'N' ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_telephone_consent := 'D' ;
lv_tps_consent := 'N' ; -- earlier it was D
lv_telephone_number := NULL ;
lv_tps_consent_value := NULL ;
lv_ctps_consent_value := NULL ;
END ;
...contnd
end loop;

Hi Extremely Sorry,I tried to format but this is how i get the resultt.Please suggest.
Re: bulk collect help in production [message #313851 is a reply to message #313795] Mon, 14 April 2008 22:10 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Enclose you code in [code]..[/code] tags.
Previous Topic: Poor performance of query with VPD policy
Next Topic: INDEX ANALYZE
Goto Forum:
  


Current Time: Tue Dec 06 15:51:41 CST 2016

Total time taken to generate the page: 0.05729 seconds