Home » RDBMS Server » Performance Tuning » How to improve performance? (merged) (Oracle Database 11g Release 11.2.0.1.0 - Production, WINDOWS 7)
How to improve performance? (merged) [message #633356] Tue, 17 February 2015 00:56 Go to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Hi,

 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_recs;
   FOR i IN l_recs.FIRST..l_recs.LAST LOOP
      l_content_text := l_content_text||chr(10)||l_recs(i);           
   END LOOP;


In above code the FOR LOOP is taking huge time for 482,678 records. But the SQL in dynamic SQL is taking only 10 to 13 seconds.

The SQL concatenate some fields. I merge those concatenated fields in l_content_text variable using this FOR LOOP. l_content_text is a CLOB type variable.

Please suggest me if there is any other way to improve the performance of this LOOP.

Re: How to improve performance? [message #633357 is a reply to message #633356] Tue, 17 February 2015 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
In above code the FOR LOOP is taking huge time for 482,678 records. But the SQL in dynamic SQL is taking only 10 to 13 seconds.


How did you get these figures?

Quote:
The SQL concatenate some fields. I merge those concatenated fields in l_content_text variable using this FOR LOOP. l_content_text is a CLOB type variable.
Please suggest me if there is any other way to improve the performance of this LOOP.


What about just use LISTAGG or STRAGG function?

Re: How to improve performance? [message #633358 is a reply to message #633357] Tue, 17 February 2015 01:17 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Quote:

How did you get these figures?


Using DBMS_UTILITY.GET_TIME() function I get the elapsed time.

Quote:

What about just use LISTAGG or STRAGG function?


LISTAGG cannnot be used as it will exceeds the length. I don't know about STRAGG. I will explore.
Re: How to improve performance? [message #633361 is a reply to message #633358] Tue, 17 February 2015 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
LISTAGG cannnot be used as it will exceeds the length.


This is why I mentioned STRAGG which supports CLOB.
This has been discussed not so long ago in our SQL & PL/SQL forum.

[Updated on: Tue, 17 February 2015 01:49]

Report message to a moderator

Re: How to improve performance? [message #633363 is a reply to message #633361] Tue, 17 February 2015 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also, generally speaking, using a single BULK COLLECT for 482,678 rows is too big.
You have to loop with a LIMIT clause.

How to process faster? [message #633665 is a reply to message #633356] Tue, 24 February 2015 02:40 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member


EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_recs;

   FOR i IN l_recs.FIRST..l_recs.LAST LOOP
      l_content_text := l_content_text||chr(10)||l_recs(i);   
        
   END LOOP;



In above code the FOR LOOP is taking huge time for 482,678 records. But the SQL in dynamic SQL is taking only 10 to 13 seconds.

The SQL concatenate some fields. I merge those concatenated fields in l_content_text variable using this FOR LOOP. l_content_text is a CLOB type variable.

Please suggest me if there is any other way to improve the performance of this LOOP.
Re: How to process faster? [message #633666 is a reply to message #633665] Tue, 24 February 2015 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You got the answer in your previous same topic.
Why do you repost it instead of giving any feedback (and maybe thanks to those who spend time to help you)?

Re: How to process faster? [message #633667 is a reply to message #633666] Tue, 24 February 2015 02:51 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

I created the STRING_AGG function and improved the process. But still it is taking more than hour. That's why I am trying to get some alternatives to improve it.

Thanks all.
Re: How to process faster? [message #633670 is a reply to message #633667] Tue, 24 February 2015 03:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want help speeding up the process then you need to show us what the whole process is rather than one little bit of it.
Re: How to process faster? [message #633673 is a reply to message #633670] Tue, 24 February 2015 03:42 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

I am describing my detail requirements:

In a DB table(teststr) I have around 500,000 records. Number of fields in that table are around 10.Say in teststr.ename field I have concatenated all these fields' data. Now I need to concatenate all the records to store whole information in one record. I tried it several ways as you have seen in my previous posts. At last I am trying to do as below sample code:

declare
    c       CLOB;
    l_c     CLOB;
    cnt     PLS_INTEGER:=1;
    cnt1    PLS_INTEGER:=1000;
    l       PLS_INTEGER;
   
begin 
  
   l := dbms_utility.get_time();
   For i in 1..500 loop
       
       SELECT string_agg(ename) into c
       FROM (
       select rownum recno, ename
       from teststr   
       )
       WHERE recno between cnt and cnt1;
       cnt :=cnt1+1;
       cnt1:= cnt1+1000;
       l_c := l_c||chr(10)||c;          
     
   END LOOP;
  l := dbms_utility.get_time()-l;  
  dbms_output.put_line(l);
end;

Here I tried to concatenate all the records in l_c variable to insert into the desired table.
Please suggest me what would be the best way to achieve this purpose?
Re: How to process faster? [message #633676 is a reply to message #633673] Tue, 24 February 2015 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's not the full process.
And what you've posted is wrong - if you want to break the rows into chunks based on rownum then you need an order by to ensure the same rownum is applied to the same row each time the query is run.

Best way is probably a single insert/select or update as appropriate.
Re: How to process faster? [message #633678 is a reply to message #633673] Tue, 24 February 2015 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

We don't know your "string_agg" function.

Original STRAGG is an aggregate function, you have not to loop.

Re: How to process faster? [message #633682 is a reply to message #633678] Tue, 24 February 2015 04:00 Go to previous messageGo to next message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Please give me the syntax of original STRAGG function.
Re: How to process faster? [message #633684 is a reply to message #633682] Tue, 24 February 2015 04:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you tried typing STRAGG into google?
Re: How to process faster? [message #633686 is a reply to message #633684] Tue, 24 February 2015 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or here:

Michel Cadot wrote on Tue, 17 February 2015 08:49

Quote:
LISTAGG cannnot be used as it will exceeds the length.


This is why I mentioned STRAGG which supports CLOB.
This has been discussed not so long ago in our SQL & PL/SQL forum.


You will have the direct link from one of Barbara's posts.

Re: How to process faster? [message #633842 is a reply to message #633686] Thu, 26 February 2015 01:30 Go to previous message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member

Thanks all,

From OTN, I got below solution which improves the performance drastically.

Declare
 
l_clob        CLOB;
l_time        PLS_INTEGER;
l_cnt PLS_INTEGER:=0;

begin
 
   l_time := dbms_utility.get_time();
   dbms_output.put_line('Start Time: '||to_char(sysdate,'dd mm yyyy hh24:mi:ss')); 
   -- Initialize
   DBMS_LOB.CREATETEMPORARY(l_clob,true);
    -- Extract the data
   For rec in (select ename from teststr)
   loop
       -- Transform the data
       l_cnt:= l_cnt+1;
       dbms_lob.append(l_clob, to_clob(l_cnt)||rec.ename||chr(10));
   END LOOP;
 
   -- Load/Store the data
   INSERT INTO cl
   values (l_clob);
 
  dbms_lob.freetemporary(l_clob);
  COMMIT;
 
  l_time := dbms_utility.get_time()-l_time;
  dbms_output.put_line(l_time);
  dbms_output.put_line('End Time: '||to_char(sysdate,'dd mm yyyy hh24:mi:ss'));
end;
/

Previous Topic: Parallel direct path insert no quicker (merged 2 )
Next Topic: How to take OSWatcher baseline
Goto Forum:
  


Current Time: Thu Mar 28 18:53:48 CDT 2024