Home » RDBMS Server » Performance Tuning » slow perf with Bulk collect and FORALL
icon5.gif  slow perf with Bulk collect and FORALL [message #250905] Wed, 11 July 2007 14:34 Go to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

I read about supercharge your DML with bulk collect and FORALL and implemented on one of the slow performing procedures, but the code seem to be performing even slower.
Any suggestions, please help, have deadline.
Below is the code:

CREATE OR REPLACE PROCEDURE RESET_CHANGE_PARAM(
   process_date   IN   DATE DEFAULT TRUNC (SYSDATE)
)
IS
CURSOR  products_data is
   SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill, mt.action_code,
          mt.transit_time, mt.effective_date
 	 FROM dp.t_src_matrix mt,
       					items vi,
       					t_ep_Catal tc,
       					location vl, 
	   					t_ep_shinr ts
				 WHERE  (mt.action_code = 'CHG-LIVE'   OR
                       mt.action_code = 'CHANGEPLUS'   OR
                       mt.action_code = 'CHANGE_MINUS' OR
                       mt.action_code = 'CHG-RST'      OR
                       mt.action_code = 'CHANGE')
  				  AND  mt.catalog = tc.catal
  				  AND  tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
  				  AND  mt.ship_to_number = ts.shinr
  				  AND  vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id;
				  
TYPE prod_tab IS TABLE OF products_data%ROWTYPE;
products_tab   prod_tab := prod_tab();


TYPE itemloc_typ IS TABLE OF sales_data.item_id%TYPE INDEX BY PLS_INTEGER;
TYPE stf_typ IS TABLE OF sales_data.store_max%TYPE INDEX BY PLS_INTEGER;
TYPE fixfill_typ IS TABLE OF sales_data.fixture_fill%TYPE INDEX BY PLS_INTEGER;
TYPE action_typ IS TABLE OF dp.t_src_matrix.action_code%TYPE INDEX BY PLS_INTEGER;
TYPE date_typ IS TABLE OF dp.t_src_matrix.effective_date%TYPE INDEX BY PLS_INTEGER;


action action_typ;
item itemloc_typ;
loc  itemloc_typ;


stmin stf_typ;
stmax stf_typ;
facing stf_typ;
fixfill fixfill_typ;

itemN itemloc_typ;
locN itemloc_typ;
stminN stf_typ;
stmaxN stf_typ;
facingN stf_typ;
fixfillN fixfill_typ;


--Date and time variables
trTime stf_typ;
efDate date_typ;
nb_err  Pls_integer ; 
total  Pls_integer := 0 ;  



v_start_date  DATE := SYSDATE;
v_end_date   DATE ;
cnt_rec    NUMBER := 0;
v_time  NUMBER := 0;
v_seq   NUMBER := 0;

BEGIN
    --Check the content
	BEGIN
		SELECT COUNT(1) INTO cnt_rec FROM dp.t_src_matrix
		WHERE ROWNUM < 2;
		EXCEPTION
  	WHEN NO_DATA_FOUND THEN NULL;
	END;

	BEGIN -- create table that will collect the statistics
		check_and_drop('alice_reset_param');

   		dynamic_ddl('CREATE TABLE alice_reset_param
            		(run_id number null,
             		categ_desc varchar2(100) NULL,
             		no_rec number null,
             		start_date date NULL,
             		end_date date NULL,
			 		time_elapsed number)');
	END;


IF cnt_rec > 0 THEN 
		OPEN products_data; 
LOOP
 FETCH products_data BULK COLLECT INTO products_tab LIMIT 999;

   FOR j IN products_tab.FIRST .. products_tab.LAST
   LOOP
      item (j) :=  products_tab (j).item_id;
       loc (j) :=  products_tab (j).location_id;
    action (j) :=  products_tab (j).action_code;
     stmin (j) :=  products_tab (j).store_min;
     stmax (j) :=  products_tab (j).store_max;
   fixfill (j) :=  products_tab (j).fixture_fill;
    facing (j) :=  products_tab (j).facings;
    trTime (j) :=  products_tab (j).transit_time;
    efDate (j) :=  products_tab (j).effective_date;
    
  IF action (j) = 'CHG-LIVE' THEN 
      FORALL i IN item.FIRST .. item.LAST
            UPDATE sales_data
               SET store_max = stmax(i),
                   store_min = stmin(i),
                     facings = facing(i),
                fixture_fill = fixfill(i)
             WHERE item_id = item(i)
               AND location_id = loc(i)
               AND store_max IS NOT NULL
               AND store_min IS NOT NULL
               AND facings IS NOT NULL;
      ELSE
   --This part will be for the CHG-RST
   -------------------------------------------------- 
   SELECT          m.item_id,
   				   m.location_id,
   				   NVL (s.store_max, m.store_max),
                   NVL (s.store_min, m.store_min),
                   NVL (s.facings, m.facings),
                   NVL (s.fixture_fill, m.fixture_fill)
 BULK COLLECT INTO itemN,
                   locN,
 				   stmaxN,
                   stminN,
                   facingN,
                   fixfillN
              FROM mdp_matrix m, sales_data s
             WHERE s.sales_date = trunc(process_date)
			   AND m.item_id = products_tab (j).item_id
               AND m.location_id = products_tab (j).location_id
               AND m.item_id = s.item_id
               AND m.location_id = s.location_id;
       
        FOR x IN itemN.FIRST .. itemN.LAST
          LOOP 
           IF     products_tab (j).store_max <> stmaxN (x)
               OR products_tab (j).store_Min <> stminN (x)
               OR products_tab (j).facings <> facingN (x)
               OR products_tab (j).fixture_fill <> fixfillN (x)
           THEN 
            FORALL x IN itemN.FIRST .. itemN.LAST
             UPDATE dr.sales_data
                  SET store_max = NVL (store_max, stmaxN (x)),
                      store_min = NVL (store_min, stminN (x)),
                      facings = NVL (facings, facingN (x)),
                      fixture_fill = NVL (fixture_fill, fixfillN (x))
                WHERE item_id = itemN (x)
                  AND location_id = locN (x)
                  AND sales_date <
                           TO_DATE (efDate (j), 'DD-MON-YYYY')
                         - (trTime (j));

               UPDATE dr.sales_data
                  SET store_max = NULL,
                      store_min = NULL,
                      facings = NULL,
                      fixture_fill = NULL
                WHERE item_id = itemN (x)
                  AND location_id = locN (x)
                  AND sales_date >=
                           TO_DATE (efDate (j), 'DD-MON-YYYY')
                         - (trTime (j));
      END IF;
        END LOOP; 
   END IF;
   END LOOP;
 
    v_seq := v_seq + 1;
    cnt_rec := products_tab.COUNT;
    v_end_date := SYSDATE;
    
    v_time := ROUND((v_end_date-v_start_date)*1440,5);
	INSERT INTO alice_reset_param
    VALUES(v_seq,'Bulk partial',cnt_rec, v_START_DATE,
	                                     v_end_date,
										 v_time);
	v_start_date := v_end_date;
	v_end_date := SYSDATE;
	COMMIT;
EXIT WHEN products_data%NOTFOUND;
END LOOP;
CLOSE products_data;
INSERT INTO alice_reset_param
SELECT max(run_id)+1, 'Bulk Total', SUM(no_rec), MIN(START_DATE), MAX(end_date), 
ROUND((MAX(end_date) - MIN(START_DATE))* 1440,5) FROM alice_reset_param;

COMMIT;
END IF;
END;


[mod-edit] illiterate IM speak words removed.

[Updated on: Mon, 24 September 2007 15:56] by Moderator

Report message to a moderator

Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #250906 is a reply to message #250905] Wed, 11 July 2007 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #250907 is a reply to message #250906] Wed, 11 July 2007 14:58 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Michel Cadot wrote on Wed, 11 July 2007 13:37
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel




Make it your signature Wink Laughing
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #250910 is a reply to message #250905] Wed, 11 July 2007 15:16 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

My Oracle version is 9.2.0.8.0
Do I need to create the message again to format the code properly, or I can update it somehow?
Thanks,--Alice
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #250915 is a reply to message #250910] Wed, 11 July 2007 15:41 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Edit your post..
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #250939 is a reply to message #250915] Wed, 11 July 2007 22:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You select rows from product_data in arrays of 999.

Then you loop through the 999 rows, and for each row (j), you update SALES_DATA for products 1..j.

So on the fist product, you perform 1 update.
On the second product, you perform 2 updates.
On the third product, you perform 3 updates.

You need to take the FORALL UPDATE statement out of the FOR loop.

Take a look at the example in this article

Ross Leishman
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #250952 is a reply to message #250939] Thu, 12 July 2007 00:23 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Thanks so very much for the tip and the interesting article.
With Best Regards, --Alice
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #250989 is a reply to message #250907] Thu, 12 July 2007 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DreamzZ wrote on Wed, 11 July 2007 21:58
Michel Cadot wrote on Wed, 11 July 2007 13:37
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel




Make it your signature Wink Laughing


Good idea! Laughing

Regards
Michel
icon14.gif  Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #251326 is a reply to message #250989] Thu, 12 July 2007 23:34 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Just wanted to say Thanks again to rleishman for the valuables tips and precious article.

I learned from this a few things:
1. It's best to make scalar collections (of table_name.col_Name type and bulk collect the needed cursor in the variables of the above types, which works best when a multiple column table update is needed
2. FORALL functions like a simple DML statement. If multiple updates are needed, then for each one we put a FORALL statement
Below is the final version of the code in a anonymous block. It's tested and it works. (The only thing I still need to check is the speed of execution) -- Will come back with more details

[ALIGN=left]
DECLARE

    CURSOR  chg_live_cur is
   SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill,
          mt.transit_time, mt.effective_date
 	 FROM dp.t_src_matrix mt,
       					items vi,
       					t_ep_Catal tc,
       					location vl, 
	   					t_ep_shinr ts
				 WHERE  mt.action_code = 'CHG-LIVE'
  				  AND  mt.catalog = tc.catal
  				  AND  tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
  				  AND  mt.ship_to_number = ts.shinr
  				  AND  vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id
			      AND  ROWNUM < 50; 
			      
	CURSOR chg_rst_cur IS 
				SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill,
          			   mt.transit_time, mt.effective_date
 	 			  FROM dp.t_src_matrix mt,
       					items vi,
       					t_ep_Catal tc,
       					location vl, 
	   					t_ep_shinr ts
				 WHERE (mt.action_code = 'CHANGEPLUS'   OR
                       mt.action_code = 'CHANGE_MINUS' OR
                       mt.action_code = 'CHG-RST'      OR
                       mt.action_code = 'CHANGE')
				   AND mt.catalog = tc.catal
  				   AND tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
  				   AND mt.ship_to_number = ts.shinr
  				   AND vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id
				   AND ROWNUM < 2;

	-- collection of scalars
	TYPE itemloc_typ IS TABLE OF sales_data.item_id%TYPE INDEX BY PLS_INTEGER;
    TYPE stf_typ IS TABLE OF sales_data.store_max%TYPE INDEX BY PLS_INTEGER;
    TYPE fixfill_typ IS TABLE OF sales_data.fixture_fill%TYPE INDEX BY PLS_INTEGER;
    TYPE action_typ IS TABLE OF dp.t_src_matrix.action_code%TYPE INDEX BY PLS_INTEGER;
    TYPE date_typ IS TABLE OF dp.t_src_matrix.effective_date%TYPE INDEX BY PLS_INTEGER;

action action_typ;
item itemloc_typ;
loc  itemloc_typ;


stmin stf_typ;
stmax stf_typ;
facing stf_typ;
fixfill fixfill_typ;

itemN itemloc_typ;
locN itemloc_typ;
stminN stf_typ;
stmaxN stf_typ;
facingN stf_typ;
fixfillN fixfill_typ;


--Date and time variables
trTime stf_typ;
efDate date_typ;
nb_err  Pls_integer ; 
total  Pls_integer := 0 ;  



v_start_date  DATE := SYSDATE;
v_end_date   DATE ;
cnt_rec    NUMBER := 0;
cnt_rec_ch NUMBER := 0;
v_time  NUMBER := 0;
v_seq   NUMBER := 0;


    -- create an exception handler for ORA-24381
    dml_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN 
 --Check the content of products_data
 
	BEGIN
		SELECT COUNT(1) INTO cnt_rec FROM dp.t_src_matrix
		WHERE action_code = 'CHG-LIVE';
		EXCEPTION
  	WHEN NO_DATA_FOUND THEN NULL;
	END;

 --Check the content of sales_cur
	BEGIN
		SELECT COUNT(1) INTO cnt_rec_ch FROM dp.t_src_matrix
		WHERE action_code = 'CHANGEPLUS'   OR
              action_code = 'CHANGE_MINUS' OR
              action_code = 'CHG-RST'      OR
              action_code = 'CHANGE';
		EXCEPTION
  	WHEN NO_DATA_FOUND THEN NULL;
	END;

 IF cnt_rec > 0 THEN 
	OPEN chg_live_cur; 
 LOOP
     FETCH chg_live_cur BULK COLLECT INTO 
	 item, loc, stmin, stmax, facing, fixfill, trTime,
	 efDate LIMIT 20;
	 
	 BEGIN 
	 FORALL i IN item.FIRST .. item.LAST SAVE EXCEPTIONS
            UPDATE sales_data
               SET store_max = stmax(i),
                   store_min = stmin(i),
                     facings = facing(i),
                fixture_fill = fixfill(i)
             WHERE item_id = item(i)
               AND location_id = loc(i)
               AND store_max IS NOT NULL
               AND store_min IS NOT NULL
               AND facings IS NOT NULL; 
     EXCEPTION
     WHEN dml_errors THEN NULL; 
        --FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
         --   DBMS_OUTPUT.put_line('Error in CHG-LIVE');
        --END LOOP;
    END;
EXIT WHEN chg_live_cur%NOTFOUND;
END LOOP;
CLOSE chg_live_cur;
ELSE NULL;
END IF;


--For Chg-Rst
 IF cnt_rec_ch > 0 THEN 
	OPEN chg_rst_cur; 
 LOOP
     FETCH chg_rst_cur BULK COLLECT INTO 
	 item, loc, stmin, stmax, facing, fixfill, trTime, efDate LIMIT 20;
     --Update with values from the cursor if sales_date < ef_date - trtime
	 BEGIN
	 FORALL i IN item.FIRST .. item.LAST save EXCEPTIONS 
	
	  UPDATE dr.sales_data
       SET store_max = NVL(store_max, stmax(i)),
           store_min = NVL(store_min, stmin(i)),
             facings = NVL (facings, facing(i)),
        fixture_fill = NVL (fixture_fill, fixfill(i))
     WHERE sales_date < TO_DATE (efDate(i), 'DD-MON-YYYY') - trTime(i) AND 
          (item_id, location_id) IN 
          (SELECT s.item_id, s.location_id
             FROM mdp_matrix m, sales_data s
            WHERE s.item_id = item(i)
              AND s.location_id = loc(i)
              AND s.sales_date = TRUNC(sysdate)
              AND s.item_id = m.item_id
              AND s.location_id = m.location_id
              AND (NVL (s.store_max, m.store_max) <> stmax(i)
               OR  NVL (s.store_min, m.store_min) <> stmin(i)
               OR  NVL (s.facings, m.facings) <> facing(i)
               OR  NVL (s.fixture_fill, m.fixture_fill) <> fixfill(i))
			);
    EXCEPTION
         WHEN dml_errors THEN NULL; 
        --FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
         --   DBMS_OUTPUT.put_line('Error in CHG-LIVE');
        --END LOOP;
    END;
    
	BEGIN
	 FORALL i IN item.FIRST .. item.LAST save EXCEPTIONS     
       --Set to NULL if sales_date > ef_date - transit time

               UPDATE dr.sales_data
                  SET store_max = NULL,
                      store_min = NULL,
                      facings = NULL,
                      fixture_fill = NULL
                WHERE item_id = item(i)
                  AND location_id = loc(i)
                  AND sales_date >=
                           TO_DATE (efDate(i), 'DD-MON-YYYY')
                         - (trTime(i));
     EXCEPTION
     WHEN dml_errors THEN NULL; 
        --FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
         --   DBMS_OUTPUT.put_line('Error in CHG-LIVE');
        --END LOOP;
     END;
  EXIT WHEN chg_rst_cur%NOTFOUND;
END LOOP;
CLOSE chg_rst_cur;
ELSE NULL;
END IF;


EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;[/ALIGN]
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #257593 is a reply to message #250905] Wed, 08 August 2007 13:12 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Hello there.
I promissed to come back with a performance review of the code which I implemented. After testing in our parallel environment, and we got a 30% improvement in performance, I deployed to Production.
Over the past 2 days we had over 300,000 records in each of the CHG-LIVE and CHG-RST, and the processing time was of 4 hours for either, about the same with the old row by row process. I am using the bulk collect with a limit of 500 recs at a time.
Any ideas on what else should I be looking at?
Thanks so much for your help, --Alice
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #257596 is a reply to message #250905] Wed, 08 August 2007 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Without really knowing which part is slow you can only guess at a solution.
ALTER SESSION SET SQL_TRACE=TRUE
run the results thru TKPROF EXPLAIN=USERNAME/PASSWORD & post the formatted results back here.
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #257609 is a reply to message #250905] Wed, 08 August 2007 14:44 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Thank you so much. Will run the code through Trace and TKPROF and come back with the results
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #257635 is a reply to message #250905] Wed, 08 August 2007 18:57 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Hi:
I got the trace file and executed my code, but when using tkprof and even when I got into the udump directory of the session and database used, I got permission denied on opening the file. Is it possible that the machine user not to have access to the files created in Oracle database directory? What do I need to do to fix it?
Thanks again so very much.
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #257639 is a reply to message #250905] Wed, 08 August 2007 19:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>What do I need to do to fix it?
Logon as user Oracle or get the SA/DBA give you the file or relax permissions on file & folder.

[Updated on: Wed, 08 August 2007 19:24] by Moderator

Report message to a moderator

Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #257983 is a reply to message #250905] Thu, 09 August 2007 13:30 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Thanks so much. I was about to ask the DBA's to give me the file, but I realized that the run I did was on 10000 records that were already updated, and it did not take too long to execute. We are running new tests today, and will be back with more on that.
As always I appreciate the prompt assistance I receive from users on this site.
Re: PLZ HELP - slow perf with Bulk collect and FORALL - resolved [message #261818 is a reply to message #250905] Thu, 23 August 2007 12:19 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Thanks for your help. It comes out there were some problems with the table being updated. A re-org of the database is scheduled to take place. The bulk collect is certainly a better technique when it comes to large amount of data being inserted/deleted/updated
Re: PLZ HELP - slow perf with Bulk collect and FORALL slow and memory intensive [message #270125 is a reply to message #261818] Tue, 25 September 2007 16:55 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

I ran a trace file and tkprof it for the trouble update in the code enclosed in my prior e-mail for 500 records bulk collect versus 1000 records solution - file enclosed.
If anyone has an idea on how can I speed up the execution of this code, it would be greately appreciated.
Re: slow perf with Bulk collect and FORALL [message #270128 is a reply to message #250905] Tue, 25 September 2007 17:18 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Hmmm......
If I were a betting man, I'd bet the tkprof_bulk_collect.txt file was the result of some serious CUT & paste activity.

What I was hoping to see was the processed trace file from the whole session; including the SELECT statements.

One question which remains unanswered is:
Is the majority of the time being spent doing SELECTs or doing UPDATEs?

Another anomaly that caught my attention is comparing reported results between 500 rows & 1000 rows.

0500>Execute 56 72.87 1675.80 250355 521880 31720 31243
1000>Execute 150 776.77 11287.71 1978190 7811035 42 38

I was hoping for/expecting an apples to apples comparison.
I don't understand how the 500 row run returned 31243 rows.
while the 1000 row run returned only 38 rows.

Therefore you've raise more questions than were answered?
Re: slow perf with Bulk collect and FORALL [message #270151 is a reply to message #270128] Tue, 25 September 2007 23:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This is very odd.

Even if you were to say that QUERY buffer reads, were instantaneous, your DISK reads are somewhere between 100 and 200 per second. Either your disk is implemented as grumpy little elfs stamping bits in plasticine, or there is some recursive SQL happening.

To you have any:
- triggers
- foreign keys amongst the updated columns

Post the FULL tkprof output - start to finish with plans explained as well.

Are the updates using an index?

Ross Leishman
Re: slow perf with Bulk collect and FORALL [message #270343 is a reply to message #270151] Wed, 26 September 2007 10:31 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Here is the complete report for 500 records.
The version in Production is currently set to 500 records.
Thanks so very much. -- Alice
Re: slow perf with Bulk collect and FORALL [message #270344 is a reply to message #270343] Wed, 26 September 2007 10:32 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

And here is the tkprof report for bulk collect limit of 1000 records.
Re: slow perf with Bulk collect and FORALL [message #270432 is a reply to message #270344] Wed, 26 September 2007 21:37 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Bulk Collect is no longer an issue here, as you are not executing that many statements. I believe the problem may be due to long range scans - I will follow up in your other thread instead.

Ross Leishman
Previous Topic: Help :Getting Inner query executions from v$sql_plan_statistics_all
Next Topic: multiple size of db_block
Goto Forum:
  


Current Time: Sun Dec 11 00:15:33 CST 2016

Total time taken to generate the page: 0.04566 seconds