Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT getting slower pga memory increasing (Oracle 10g 10.2.0.3.0)
BULK COLLECT getting slower pga memory increasing [message #285537] Wed, 05 December 2007 00:17 Go to next message
dbessell
Messages: 2
Registered: December 2007
Location: Sydney
Junior Member
Hi,

I ran the script as below against 2 tables which returns just over 25 million rows, it is a simple insert script. Initially when it started it processed over 1 million rows in 20 minutes but gradually got slower and slower. It finished eventually after 29 hours.

It seems the reason it got slower and slower was because the pga memory was not being released, this grew to over 3Gb. Is this a side effect of the code I have written which bulk collects a 100 rows at a time and commits every 100,000 records? My expectation would be that the memory would not increase, what is the cause and is there anything I can do at a programme or database level to speed this up?

I have removed most of the procedures for ease of reading and commented out the calls to them as they are simply duplicate inserts but to different tables. The tables I insert into are on the same database with nologging, however, the tables selecting from ie housing.applic_answers and housing.questions are accross a database link.

Any help gratefully received,

Many thanks.
DECLARE

-- Define general types to use

TYPE app_refno_typ     IS TABLE OF general_answers.gan_app_refno%TYPE
                          INDEX BY PLS_INTEGER;
TYPE que_refno_typ     IS TABLE OF general_answers.gan_que_refno%TYPE
                          INDEX BY PLS_INTEGER;
TYPE created_by_typ    IS TABLE OF general_answers.gan_created_by%TYPE
                          INDEX BY PLS_INTEGER;
TYPE created_date_typ  IS TABLE OF general_answers.gan_created_date%TYPE
                          INDEX BY PLS_INTEGER;
TYPE date_value_typ    IS TABLE OF general_answers.gan_date_value%TYPE
                          INDEX BY PLS_INTEGER;
TYPE number_value_typ  IS TABLE OF NUMBER
                          INDEX BY PLS_INTEGER;
TYPE char_value_typ    IS TABLE OF general_answers.gan_char_value%TYPE
                          INDEX BY PLS_INTEGER;
TYPE modified_by_typ   IS TABLE OF general_answers.gan_modified_by%TYPE
                          INDEX BY PLS_INTEGER;
TYPE modified_date_typ IS TABLE OF general_answers.gan_modified_date%TYPE
                          INDEX BY PLS_INTEGER;
TYPE qor_code_typ      IS TABLE OF general_answers.gan_qor_code%TYPE
                          INDEX BY PLS_INTEGER;
TYPE other_code_typ    IS TABLE OF general_answers.gan_other_code%TYPE
                          INDEX BY PLS_INTEGER;
TYPE other_date_typ    IS TABLE OF general_answers.gan_other_date%TYPE
                          INDEX BY PLS_INTEGER;
TYPE comments_typ      IS TABLE OF general_answers.gan_comments%TYPE
                          INDEX BY PLS_INTEGER;
TYPE qcat_typ          IS TABLE OF VARCHAR2(3)
                          INDEX BY PLS_INTEGER;
TYPE ipa_refno_typ     IS TABLE OF involved_party_answers.ipn_ipa_refno%TYPE
                          INDEX BY PLS_INTEGER;
TYPE lar_code_typ      IS TABLE OF lettings_area_answers.laa_lar_code%TYPE
                          INDEX BY PLS_INTEGER;

-- Define general tabs to use

app_refno_tab          app_refno_typ;
que_refno_tab          que_refno_typ;
created_by_tab         created_by_typ;
created_date_tab       created_date_typ;
date_value_tab         date_value_typ;
number_value_tab       number_value_typ;
char_value_tab         char_value_typ;
modified_by_tab        modified_by_typ;
modified_date_tab      modified_date_typ;
qor_code_tab           qor_code_typ;
other_code_tab         other_code_typ;
other_date_tab         other_date_typ;
comments_tab           comments_typ;
qcat_tab               qcat_typ;
ipa_refno_tab          ipa_refno_typ;
lar_code_tab           lar_code_typ;

-- Define type and tab to store any questions needing update
TYPE upd_que           IS TABLE OF PLS_INTEGER
                          INDEX BY PLS_INTEGER;

upd_que_refno_tab      upd_que;
l_que_refno            general_answers.gan_que_refno%TYPE := -12345678;

-- Define type and tab to store the relevant insert
TYPE ins_tab           IS TABLE OF PLS_INTEGER
                          INDEX BY PLS_INTEGER;

ins_laa_tab            ins_tab; -- lettings_area_answers
ins_gan_tab            ins_tab; -- general_answers
ins_ipn_tab            ins_tab; -- involved_party_answers
ins_mga_tab            ins_tab; -- model_general_answers
ins_mip_tab            ins_tab; -- model_involved_party_answers
ins_mla_tab            ins_tab; -- model_lettings_area_answers

-- Exception handling
l_error_count          NUMBER;

-- Commit handling
v_commit               NUMBER := 1;

CURSOR main IS
SELECT
       a.aan_app_refno
,      a.aan_que_refno
,      USER
,      a.aan_date_updated    cre_date
,      DECODE(b.que_datatype
         ,'D',a.aan_value
         ,NULL)              date_value
,      DECODE(b.que_datatype
         ,'N',DECODE(a.aan_value -- from model_general_answers
                ,'N',-999
                ,'Y',-999
                ,a.aan_value
                )
         ,NULL)              number_value
,      DECODE(b.que_datatype
         ,'C',a.aan_value
         ,'YN',a.aan_value
         ,NULL)              char_value
,      a.aan_user_updated
,      a.aan_date_updated    mod_date
,      a.aan_qor_code            
,      a.aan_other_code         
,      a.aan_date              
,      a.aan_text
,      DECODE(b.que_hsr_qcat
         ,'CC','GAN'
         ,'HR','GAN'
         ,'OS','GAN'
         ,'GA','GAN'
         ,'MD','GAN'
         ,'SG','GAN'
         ,'DQ','GAN'
         ,'IP','IPN'
         ,'AP',DECODE(a.aan_lar_code
                 ,NULL,NULL
                 ,'LAA'
                 )
         )        qcat
,      a.aan_ipa_refno
,      a.aan_lar_code
FROM   housing.applic_answers a
,      housing.questions b
WHERE  a.aan_que_refno     = b.que_refno    
ORDER BY b.que_refno;

PROCEDURE insert_laa
IS
BEGIN

--   dbms_output.put_line('insert_laa');

   IF ins_laa_tab.COUNT = 0
   THEN
      NULL;
--      dbms_output.put_line('No records to insert.');
   ELSE
      FORALL indx IN VALUES OF ins_laa_tab
      SAVE EXCEPTIONS

         INSERT /*+ Append */ INTO lettings_area_answers
         (laa_lar_code
         ,laa_app_refno 
         ,laa_que_refno 
         ,laa_created_by 
         ,laa_created_date 
         ,laa_date_value 
         ,laa_number_value 
         ,laa_char_value 
         ,laa_modified_by 
         ,laa_modified_date 
         ,laa_qor_code 
         ,laa_other_code 
         ,laa_other_date 
         ,laa_comments)      
         VALUES
         (lar_code_tab(indx)
         ,app_refno_tab(indx)
         ,que_refno_tab(indx)
         ,created_by_tab(indx)
         ,created_date_tab(indx)
         ,date_value_tab(indx)
         ,number_value_tab(indx)
         ,char_value_tab(indx)
         ,modified_by_tab(indx)
         ,modified_date_tab(indx)
         ,qor_code_tab(indx)
         ,other_code_tab(indx)
         ,other_date_tab(indx)
         ,comments_tab(indx));

--         dbms_output.put_line ('inserted '||sql%rowcount||' records');
   END IF;

EXCEPTION
   WHEN OTHERS THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.PUT_LINE('Exceptions from insert_laa:');
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count
      LOOP
         DBMS_OUTPUT.put_line('Error: ' ||i||
                              ' Array Index: '||
                              SQL%BULK_EXCEPTIONS(i).error_index||
                              ' Message: '||
                              SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;

END insert_laa;

PROCEDURE update_questions
IS
BEGIN

--   dbms_output.put_line('update_questions');

   IF upd_que_refno_tab.COUNT = 0
   THEN
      NULL;
--      dbms_output.put_line('No records to update.');
   ELSE
      FORALL indx IN VALUES OF upd_que_refno_tab
      SAVE EXCEPTIONS

         UPDATE questions
         SET    que_datatype = 'T'
         WHERE  que_refno    = que_refno_tab(indx);

--         dbms_output.put_line ('updated '||sql%rowcount||' rows');
   END IF;

EXCEPTION
   WHEN OTHERS THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.PUT_LINE('Exceptions from update_questions:');
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count
      LOOP
         DBMS_OUTPUT.put_line('Error: ' ||i||
                              ' Array Index: '||
                              SQL%BULK_EXCEPTIONS(i).error_index||
                              ' Message: '||
                              SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;

END update_questions;

BEGIN

OPEN main;
LOOP
   FETCH main BULK COLLECT INTO app_refno_tab
                               ,que_refno_tab
                               ,created_by_tab
                               ,created_date_tab
                               ,date_value_tab
                               ,number_value_tab
                               ,char_value_tab
                               ,modified_by_tab
                               ,modified_date_tab
                               ,qor_code_tab
                               ,other_code_tab
                               ,other_date_tab
                               ,comments_tab
                               ,qcat_tab
                               ,ipa_refno_tab
                               ,lar_code_tab LIMIT 100;

   FOR indx IN 1..app_refno_tab.COUNT
   LOOP
      IF LENGTH(number_value_tab(indx)) > 9 -- Will be stored as a character
      THEN                                  -- field and datatype changed to T.
         char_value_tab(indx)    := TO_CHAR(number_value_tab(indx));
         number_value_tab(indx)  := NULL;

         IF que_refno_tab(indx) != l_que_refno -- Question needs updating to T
         THEN                                  -- have we previously updated it?
            upd_que_refno_tab(indx) := indx;
            l_que_refno             := que_refno_tab(indx);
         END IF;
      END IF;

      IF qcat_tab(indx) = 'GAN'     -- General Answers and Model General Answers
      THEN
         ins_gan_tab(indx) := indx;
         ins_mga_tab(indx) := indx;
      ELSIF qcat_tab(indx) = 'IPN'  -- Involved Party Answers and Model Involved Party Answers
      THEN
         ins_ipn_tab(indx) := indx;
         ins_mip_tab(indx) := indx;
      ELSIF qcat_tab(indx) = 'LAA'  -- Lettings Area Answers and Model Lettings Area Answers
      THEN
         ins_laa_tab(indx) := indx;
         ins_mla_tab(indx) := indx;
      END IF;

   END LOOP;

   insert_laa;
--   insert_gan; -- I have not included this procedure but its format is identical to insert_laa
--   insert_ipn; -- I have not included this procedure but its format is identical to insert_laa
--   insert_mga; -- I have not included this procedure but its format is identical to insert_laa
--   insert_mip; -- I have not included this procedure but its format is identical to insert_laa
--   insert_mla; -- I have not included this procedure but its format is identical to insert_laa
   update_questions;

   EXIT WHEN main%NOTFOUND;

   IF v_commit > 1000 -- 1000 loops of 100 records a time is 100,000 records.
   THEN
      COMMIT;
      v_commit := 1;
   ELSE
      v_commit := v_commit + 1;
   END IF;

END LOOP;

CLOSE main;

END;
/

[Updated on: Wed, 05 December 2007 00:19]

Report message to a moderator

Re: BULK COLLECT getting slower pga memory increasing [message #285557 is a reply to message #285537] Wed, 05 December 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, yes committing so often is a reason of performances decrease with higher number of rows.
25 millions of rows is not so much, you should try to do it in a whole without intermediate commit. Increase your undo tablespace is a better choice.
Also, avoid doing this during peak hours.

I don't know why space is growing but in my procedure I always explicitly delete all items in collection.

Finally, when you use exception clause, Oracle use a slower code to catch and save exceptions (and append hint is useless). I don't how many exceptions you could have but maybe it is worth to previously determinate which ones they are and not select them.

Regards
Michel
Re: BULK COLLECT getting slower pga memory increasing [message #285580 is a reply to message #285557] Wed, 05 December 2007 01:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sure sounds like a memory leak, but I would have thought your code would release all of the arrays.

Try narrowing down your search. Can you comment out all of the inserts/updates and just leave the BULK COLLECT loop? See if performance degrades there. Then reintroduce 1 of the inserts/updates and check again.

You should be able to determine whether the problem is occurring in just the BULK COLLECT, one or more of the insert/updates, or everywhere.

Perhaps the SAVE EXCEPTIONS structures are not released.
Or perhaps its the DBMS_OUTPUT?

Ross Leishman
Re: BULK COLLECT getting slower pga memory increasing [message #285588 is a reply to message #285580] Wed, 05 December 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think it is dbms_output whose buffer is limited to 1000000.

Regards
Michel
Re: BULK COLLECT getting slower pga memory increasing [message #285834 is a reply to message #285537] Wed, 05 December 2007 17:22 Go to previous messageGo to next message
dbessell
Messages: 2
Registered: December 2007
Location: Sydney
Junior Member
Many thanks Michel and Ross for your replies, very useful information.

The syntax for the BULK COLLECT and FORALL I have seen many times before on various websites including asktom as the correct way and these did not explicitly empty the arrays, however, it is certainly something for me to try. The same as the SAVE EXCEPTIONS. What syntax would you use to ensure these are emptied after each fetch? Thankyou.

One other thing I found whilst googling was a reference to arrays not being supported accross a database link. I understand this was true at 8i but is this also true at 10g, would this have any adverse effect on performance having to go back accross the link to get the next 100 rows?

The commits are every 100,000 rows so yes there would be about 250 commits during the run, I put these in as I thought it would release what ever was being stored in the memory. Without the commits the run fell over as it exceeded the limit.

Thanks once again for your help.

David.

Re: BULK COLLECT getting slower pga memory increasing [message #285840 is a reply to message #285588] Wed, 05 December 2007 20:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Michel Cadot wrote on Wed, 05 December 2007 18:59

I don't think it is dbms_output whose buffer is limited to 1000000.

Unlimited as of 10.2, however you need to DBMS_OUTPUT.ENABLE(NULL), which - granted - the OP did not display in his abbreviated code sample. Thought it was still worth a mention in case it had been done in a prior PL/SQL call.

Ross Leishman
Re: BULK COLLECT getting slower pga memory increasing [message #285900 is a reply to message #285840] Thu, 06 December 2007 00:51 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Ross, you're right default is unlimited from 10.2, so could be the reason.

Quote:

syntax would you use to ensure these are emptied after each fetch?

<collection>.delete;

Quote:

would this have any adverse effect on performance having to go back accross the link to get the next 100 rows?

Much less than going for each row.

Quote:

Without the commits the run fell over as it exceeded the limit.

Which one?

Quote:

I thought it would release what ever was being stored in the memory.

No, commit does not release memory, it releases locks and undo blocks you used and may be at the root of ORA-01555 error on your main query.

Regards
Michel
Previous Topic: Error in BLOB array
Next Topic: Triggers
Goto Forum:
  


Current Time: Mon Dec 05 20:57:24 CST 2016

Total time taken to generate the page: 0.08287 seconds