Home » SQL & PL/SQL » SQL & PL/SQL » Performance of cursor (10 g)
icon7.gif  Performance of cursor [message #388547] Wed, 25 February 2009 06:21 Go to next message
Jyoti5
Messages: 4
Registered: February 2009
Junior Member
Hi All,

I need one suggestion on performance of a cursor.

i have created one cursor

OPEN cur1 FOR
SELECT field1,
field2,field3,
package_abc.getvalue(field1),
package_abc.getvalue(field2)
from table1
where field4='A';

package structure:
package_abc.getvalue(field_val varchar2)

SELECT col1 from table2 where col2=field_val;

lets ssay we have millions of record
By doing so performance of proc has been degraded.. it is longer execution time for running the proc...
can anyone suggest me alternative measure that can be taken...
earlier i were using GTT(global temporary table) which had 5 columnns and after fetching cursor for 3 columns, i were updating GTT table for another 2 columns which i think was considerably good, but that had some other issue, so cannot use it...

please give your valuable comments




Re: Performance of cursor [message #388563 is a reply to message #388547] Wed, 25 February 2009 07:47 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Jyoti5 wrote on Wed, 25 February 2009 13:21
package structure:
package_abc.getvalue(field_val varchar2)

SELECT col1 from table2 where col2=field_val;

I have no idea what shall this SELECT statement demonstrate; however it is not valid inside PL/SQL.
Jyoti5 wrote on Wed, 25 February 2009 13:21
lets ssay we have millions of record
By doing so performance of proc has been degraded.. it is longer execution time for running the proc...

You also posted the code for opening the cursor. This operation is hardly causing any performance problem. But, the fetching of (all) records from cursor (you did not post) may be slow.
Using SQL statement instead of that row by row processing would be faster (as mentioned e.g. in this thread on AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330#30743748552628). But, without any details (especially stating, what shall that procedure do, showing the slow code block and posting the table structures within indexes used), it is impossible to say more.
Re: Performance of cursor [message #388565 is a reply to message #388547] Wed, 25 February 2009 07:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Every time you make a function call from within an SQL statement, you cause a context switch - the operation needs to switch to the Pl.Sql engine to get the value, and then switch back to the SQL engine.
These are quite costly, and will slow down your code.

In the example you posted, joins are what you want:
SELECT field1,
      field2,
      field3,
      t2_1.col1,
      t2_2.col2
 from  table1
      ,table2 t2_1
      ,table2 t2_2
 where field4='A'
 and   t2_1.col_2 = field1
 and   t2_2.col_2 = field2;
 

Re: Performance of cursor [message #388699 is a reply to message #388547] Wed, 25 February 2009 23:24 Go to previous messageGo to next message
Jyoti5
Messages: 4
Registered: February 2009
Junior Member
Thanks flyboy and JrowBottom...

fetching of millions of records are taking time, that may be because of swithching b/w sql and pl/sql engine as per JrowBottom.

i'll try solution provided by you Smile
Re: Performance of cursor [message #388769 is a reply to message #388699] Thu, 26 February 2009 03:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Fetching millions of records is always going to take some time. At 10,000 rows a second you're still looking at minutes of elapsed time.

Here's an example showing just how much wors a function can make things perform:
create table test_119 (col_1 number, col_2 number);

create table test_120 (col_3 number, col_4 number);

insert into test_119 select level, mod(level,10)+1 from dual connect by level <= 10000;

insert into test_120 select level,level*(-1) from dual connect by level <= 10;

create or replace function test_120_fn (p_in  number) return number as
  v_return number;
begin
  select col_4
  into   v_return
  from   test_120
  where  col_3 = p_in;
  
  return v_return;
end;;
/

declare
  v_tim   pls_integer;
  v_iter  pls_integer := 100;
  
begin

  v_tim := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    for rec in (select col_1
                      ,col_2
                      ,col_4
                from   test_119
                      ,test_120
                where  col_3 = col_2) loop
      null;
    end loop;
  end loop;
  
  dbms_output.put_line('Test 1 '||to_char(dbms_utility.get_time - v_tim));

  v_tim := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    for rec in (select col_1
                      ,col_2
                      ,test_120_fn(col_2)
                from   test_119) loop
      null;
    end loop;
  end loop;
  
  dbms_output.put_line('Test 2 '||to_char(dbms_utility.get_time - v_tim));
end;
/


Results:
Test 1 239
Test 2 10158

As we can see, the function runs about 40* slower than the join version.
Interestingly, if you add another copy of Test_120 into the first query, and another call to test_120_fn into the second query you get results like this:
Test 1 321
Test 2 19386
The first test takes about 50% longer to run, whereas the 2nd test takes nearly double the time - implying that each additional function adds the same amount of extra processing time to fetching a row.
Previous Topic: Trigger for enforcing referential integrity
Next Topic: Help with SMTP Mail Code
Goto Forum:
  


Current Time: Thu Dec 08 14:20:30 CST 2016

Total time taken to generate the page: 0.10082 seconds