Re: CURSOR LOOPS in PL/SQL

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Thu, 20 Apr 2000 01:11:36 GMT
Message-ID: <8dllft$um8$1_at_nnrp1.deja.com>

In article <0139fce0.10ee388a_at_usw-ex0102-084.remarq.com>,   SyonOCPAD <syonNOsySPAM_at_perigee.net.invalid> wrote:
> I have a hypothesis I want validated or invalidated.
> Which is faster performance wise?
> A) CURSOR FOR LOOP
> B) SIMPLE LOOP with CURSOR
>
> My arguement lies with (B) --> (A) uses an explicit cursor but
> forces Oracle to handle the cursor implicitly. We all know to
> avoid using implicit cursors because of the inherent double
> fetch and the overhead put on Oracle to allocate the cursor
> variable, open, fetch, and close the cursor.
>
> Does anyone have the definitive answer?
> My test runs on 87,000 rows loop is that (B) is faster by
> approximately 3 seconds.
>
> Anyone?
>

In my experience, they are pretty much the same performance wise (but increasingly harder to code). I pretty much use implicit cursor loops almost all of the time (but when the result sets are smallish -- I fine tune with BULK COLLECT to do a single array fetch of all rows)

Here is my example, showing the performance of a totally implict cursor for loop (it defines the cursor and the record and does all of the fetching and implicity closes everything). Then a explict/implicit cursor for loop. I defined the cursor -- however it still does the open and close for me as well as setup the record. Then an explicit cursor for loop totally -- i do everything. Lastly, i show a bulk collect (even though I think 100,000 elements in the array is probably too much but it shows it can be faster)

The results were (and I find similar results in other releases):

ops$tkyte_at_8i> create table test as select object_id, object_name, object_type, status from all_objects;

Table created.

Elapsed: 00:00:05.15
ops$tkyte_at_8i> insert into test select object_id, object_name, object_type, status from all_objects;

19777 rows created.

Elapsed: 00:00:04.79
ops$tkyte_at_8i> insert into test select object_id, object_name, object_type, status from all_objects;

19777 rows created.

Elapsed: 00:00:04.73
ops$tkyte_at_8i> insert into test select object_id, object_name, object_type, status from all_objects;

19777 rows created.

Elapsed: 00:00:04.80
ops$tkyte_at_8i> insert into test select object_id, object_name, object_type, status from all_objects;

19777 rows created.

Elapsed: 00:00:04.75
ops$tkyte_at_8i>
ops$tkyte_at_8i> select count(*) from test;

  COUNT(*)

---

     98885

Elapsed: 00:00:00.21

ops$tkyte_at_8i>
ops$tkyte_at_8i> set timing on
ops$tkyte_at_8i>
ops$tkyte_at_8i> begin
  2          for x in ( select * from test ) loop
  3                  null;
  4          end loop;

  5 end;
  6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.62
ops$tkyte_at_8i>
ops$tkyte_at_8i> declare
  2 cursor c1 is select * from test;   3 begin

  4          for x in c1 loop
  5                  null;
  6          end loop;

  7 end;
  8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.79
ops$tkyte_at_8i>
ops$tkyte_at_8i> declare

  2          cursor c1 is select * from test;
  3          l_rec  test%rowtype;
  4  begin
  5          open c1;
  6          loop
  7                  fetch c1 into l_rec;
  8                  exit when c1%notfound;
  9          end loop;
 10          close c1;

 11 end;
 12 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.73

ops$tkyte_at_8i>
ops$tkyte_at_8i> set serveroutput on
ops$tkyte_at_8i>
ops$tkyte_at_8i> declare
  2          type myArray is table of varchar2(255) index by
binary_integer;
  3          l_object_id   myArray;
  4          l_object_name myArray;
  5          l_object_type myArray;
  6          l_status      myArray;
  7  begin
  8          select object_id, object_name, object_type, status
  9            BULK COLLECT INTO l_object_id, l_object_name,
l_object_type, l_status
 10            from test;
 11
 12          dbms_output.put_line( l_object_id.count );

 13 end;
 14 /
98885

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.38

Also, don't avoid this:

> forces Oracle to handle the cursor implicitly. We all know to
> avoid using implicit cursors because of the inherent double
> fetch and the overhead put on Oracle to allocate the cursor
> variable, open, fetch, and close the cursor.

this applies to SELECT INTO's only and isn't really valid for the following reasons:

• the double FETCH is Needed and mandatory -- the logic of a select into needs this and if you don't do it -- you are missing a chance to find corruption in your data. more on that in a minute.
• the overhead put on Oracle to allocate the cursor variable -- well, you have that with EXPLICIT as well as IMPLICT cursors and its all done at runtime. The same amount of work is done in the end. Whether we generate the open/fetch/close code or you explicity code it -- won't matter, we still execute it.

As for the double fetch, i don't know how many times I'm glad I had a select ... into .. from T where ....; That means I'm expecting AT LEAST 1 row and at MOST 1 row. I've seen people code:

 /* using explicit cursors cause its "faster" */  open c1;
 fetch c1 into x, y, z;
 close c1;

but then they fail to

1. check if ANY data was fetched.
2. to see if there were more rows in the table that matched the criteria -- I have data corruption and I didn't detect it.

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Apr 20 2000 - 03:11:36 CEST