Re: Implicit vs Explicit cursors

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/05/14
Message-ID: <3198c481.14310277_at_dcsun4>#1/1

raj_at_ulysses.att.com (Rajeev Shankar) wrote:

>Hello all:
>
>I have a question regarding implicit vs explicit cursors.
>

There is some confusion as to what an implicit vs explicit cursor is.

The following is an 'explicit' cursor:

DECLARE
CURSOR dbase_cursor IS select * from emp; dbase_cursor_rec dbase_cursor%ROWTYPE;  

BEGIN
   OPEN dbase_cursor;

      LOOP
         FETCH dbase_cursor into dbase_cursor_rec;
         EXIT WHEN dbase_cursor%NOTFOUND;
         ....
      END LOOP;

   CLOSE dbase_cursor;
END;
/

The following is an implicit cursor:

begin

   for x in ( select * from emp ) loop

        ....
   end loop;
end;
/

I think the performance hit you are seeing is due to the "...." code. A select may run in 15 minutes in sql*plus but if you through it in a loop and process each and every row, doing an insert or something else with it, you will run slower.

Don't forget:

The select in sql*plus will use array fetches, the pl/sql will not. The code you add after the fetch will add processing time to the query.

>The following sql query retrieves data in about 15 minutes.
>
> select distinct o.col1, o.col2, o.col3, o.col4, o.col5,
> o.col6, o.col7, o.col8, s.col1, a.col1, a.col2
>from w_table1 o, w_table2 s, w_table3 a
>where o.col1 = s.col1 and
> o.col2 = s.col2 and
> o.col4 = a.col4 and
> o.col5 = a.col1 and
> o.col6 between sysdate -10 and sysdate
> and o.col7 like '%599QT%';
>
>The above script gives a rough idea of what I need. ( May not
>be right in syntax).
>
>However when I run a similar query to the one given above the output
>is very fast ( about 15 mins ). I had tuned the query using explain
>plan.
>
>
>Now when I run the same query using explicit cursor in PL/SQL
>the process takes hours to run.
>
>Here is the sample code
>
>DECLARE
>CURSOR dbase_cursor IS
> select distinct o.col1, o.col2, o.col3, o.col4, o.col5,
> o.col6, o.col7, o.col8, s.col1, a.col1, a.col2
>from w_table1 o, w_table2 s, w_table3 a
>where o.col1 = s.col1 and
> o.col2 = s.col2 and
> o.col4 = a.col4 and
> o.col5 = a.col1 and
> o.col6 between sysdate -10 and sysdate
> and o.col7 like '%599QT%';
>dbase_cursor_rec dbase_cursor%ROWTYPE;
>
>BEGIN
> OPEN dbase_cursor;
> LOOP
> FETCH dbase_cursor into dbase_cursor_rec;
> EXIT WHEN dbase_cursor%NOTFOUND;
> INSERT INTO temp values(......); /Not exact code/
> END LOOP;
> CLOSE dbase_cursor;
>END;
>/
>
>My question is why is there such a huge difference in performance.
>I had been told that there with regard to performance there is
>a marginal difference between implicit and explicit cursors.
>
>When I ran the above two processes, I was the only user, so there
>was not any load on the server.
>
>I work on Unix Platform ( SUNOS) with version 7.2 of oracle.
>
>I would appreciate a reply on the above matter.
>
>Thanks
>
>Rajeev

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

---

opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Tue May 14 1996 - 00:00:00 CEST