Skip to Main Content
  • Questions
  • What is the reason that implicit cursors are faster than explicit cursors

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, nag.

Asked: August 19, 2001 - 7:32 pm UTC

Last updated: September 16, 2011 - 4:49 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi
I tested and satisfied myself that implicit cursors are better than explicit cursors. I did tkprof report , and what you said is right.

Now can you tell me the reason as to why the implicit cursors are faster, what are the internal reasons. Is it because that we have to do a open curosor fetch etc( but when we do a for records in cursor loop , we dont have to do it).

What are the actual reason. How much of a perfomance difference it makes, it my case it was very neglible( but ofcourse I was retrieving only a few records).

Thanks a lot



and we said...

Ok, the answer is pretty clear.

PLSQL, like java, is interpreted. We have some C code that is a PLSQL VM (virtual machine) that runs PLSQL. The more PLSQL code you write, the more we have to intrepret. Intrepeted code runs slower then native code (object/binary code) does. Therefore the code:


declare
cursor c is select * from dual;
l_dummy dual.dummy%type;
begin
open c;
fetch c into l_dummy;
if ( c%notfound ) then
raise no_data_found;
end if;

fetch c into l_dummy;
if ( c%found ) then
raise too_many_rows;
end if;

close c;
end;

Is just a heck of alot more code than:

declare
l_dummy dual.dummy%type;
begin
select * into l_dummy from dual;
end;


the PLSQL VM has lots more to do, more CPU cycles, less is done in C, more is done in the VM -- hence less performance.


It just goes right along with my mantra:

o if you can do it in a single sql statement - do so.
o if you cannot, do it in as little PLSQL as you can.
o if you cannot, because plsql cannot do it, try some java (eg: sending an email with an attachment)
o if you cannot do it in java (too slow, or you have some existing 3gl code already) do it in C as an extproc.




Rating

  (83 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ashraf gamal, August 20, 2001 - 1:31 am UTC


Very much clear.

Nirmal Kumar, August 20, 2001 - 3:23 am UTC


Helena Markova, August 20, 2001 - 4:50 am UTC


great explanation as usual

umesh kasturi, August 20, 2001 - 4:28 pm UTC

great mantra tom!!

Good Matra Tom

C Vivek Anand, August 21, 2001 - 1:20 am UTC

This is really a nice example to make the point very
clear.Nice way of doing things tom.


zist

A reader, September 27, 2001 - 5:24 pm UTC

I guess what you are trying to say is ..

'the plsql virtual machine is written in c, all the plsql code which we write is interpreted and translated into c by this virtual machine.

So the less code you write , less is the work the plsql virtual machine has to do, hence more improvement in performance ,

Iam I right.


But

"the PLSQL VM has lots more to do, more CPU cycles, less is done in C, more is done in the VM -- hence less performance."


What you mean by "less is done in C, more is done in the VM"

Are you and me saying the same thing.


Tom Kyte
September 28, 2001 - 7:05 am UTC

yes, the less plsql code you write -- in general -- the faster it will go since the C code behind the plsql engine is doing the work

Not exactly a real world example

Graeme King, September 28, 2001 - 2:53 pm UTC

Obviously there is no doubt that your example is faster but in reality almost no-one codes explicit cursors like that.

For example if u are much more likely to test for %NOTFOUND and then do something else rather than raise a NO_DATA_FOUND exception. The same will go for the TOO_MANY_ROWS exception also. What if u don't care if there is more than one row that meets you criteria and all u want is the first one. You don't code a TOO_MANY_ROWS exception and just fetch once. Using an implicit cursor u will need to code that exception. You should not use exceptions to branch program control that you could otherwise handle elegantly.

I see explicit cursors having the following advantages over implicit cursors :

1. They give u more control over the flow of a program. This makes code easier to read, debug and maintain.
2. They can be encapsulated within a package used by many programs thus reducing code volume thus improving performance.
3. They seperate the SQL from the procedural side of your code allowing the program flow to be more apparent...cursors can be large but the actual logic of a program is not confused by a large SQL statement in the middle of your code as part of an implicit cursor. The explicit cursor would instead be declared in the declarations section or elsewhere in a standard cursor package for example.

I agree that performance should always be improved where possible but in this case (where it is neglible) not at the expense of good programming style (sequence, selection and iteration)

Thanks for excellent book on Oracle and your great work here.

Tom Kyte
September 29, 2001 - 2:14 pm UTC

If they do not code that way (that I did) -- then they have NO need for the select into. The code able is the real world code you would have to write in order to do the select into logic. What I was trying to demonstrate is why implicit cursors are BETTER then explicit ones. Look at all of the work they do for you.

In any case - the IMPLICIT select into will always be faster then even just the

open
fetch
close

I've shown that time and time and time again on this site. It is also more readable, legible, understandable and (most importantly) the easist to code and debug.

Also -- I don't care if you raise a no data found or not -- its the fact that you must detect it procedurally with an if statement. I can assure you that:

begin
select x into y from t where ...;
exception
when no_data_found then
....
end;

is faster than

open c;
fetch c into y;
if ( c%notfound ) then
...
end if;
close c;

regardless. People who do NOT do the TOO_MANY_ROWS test are the EXCEPTION (or they should be -- most of the time there should be ONE row). If you are just looking for the EXISTENCE of a row:

select count(*) into l_cnt from dual
where exists ( YOUR_QUERY_HERE );

is the efficient way to do that.

I see NO benefit from explicit cursors. They involve more code, require you to do more work, leave more chance for bugs (because you forget to close them whatever), are harder to code, and run less efficiently - slower then implicit cursors.

IMPLICIT CURSORS rule.

I myself find:

for x in ( select * from t )
loop
....
end loop


much much easier then


open c;
loop
fetch c into x;
exit when c%notfound
....
end loop;
close c;

In this case -- what the heck is C? Oh, I have to page back up in the code to see C's definition. I have to exit from the loop (true, I could code:

open c;
fetch c into x;
while ( c%found ) loop
.....
fetch c into x;
end loop;
close c;

but i find that awkward as well)....

2) not relevant. The overhead of EXPLICIT cursor processing outweighs by a large margin any possible performance speedup by reusing that cursor in a small number of places. How many places do you really use the same exact query over and over in your code? Not many in my experience. The performance gain of the implict cursor outweighs any gain here (which would be very very small)

3) The SQL statement IS the logic of your program. Even so, you can:

declare
c cursor for really large query;
begin
for x in c
loop
...
end loop;
end;
/

I do that from time to time for MONSTER queries (although I prefer just to make view for those and get the out of the code all together)


I just really disagree with you that implicit cursors take away from "good programming". I myself find the programs that use explicit cursors to be

- harder to read
- harder to code
- more error prone

Thanks for the feedback

I'm agree with you,but there is problem in Pro*c

A reader, September 29, 2001 - 4:33 am UTC

I'm agree with you,but there is problem in Pro*c.
I program in Pro*c (oracle 8.1.5 on solaris)implicit cursor:
SELECT PRODUCT_OBJ_ID0
INTO :PROID
FROM ACCOUNT_PRODUCTS_T
WHERE OBJ_ID0=:ACCID AND STATUS=10100 AND ROWNUM=1 G
When I execute this program repeat and repeat,I recieved "ORA-00100 error:maximum open cusors".(I have set HOLD_CURSOR=NO ,RELEASE_CURSOR=YES).
But,I use emplicit cursor:
EXEC SQL DECLARE pro_cur CURSOR FOR
SELECT PRODUCT_OBJ_ID0
FROM ACCOUNT_PRODUCTS_T
WHERE OBJ_ID0=:ACCID AND STATUS=10100;
EXEC SQL OPEN pro_cur;
EXEC SQL FETCH pro_cur INTO :PROID;
EXEC SQL CLOSE pro_cur;
It works well the first time run,the second time it stop at statment :EXEC SQL OPEN pro_cur;
Why ?


Tom Kyte
September 29, 2001 - 3:07 pm UTC

Sorry -- i cannot find any logged issues, nor can I reproduce.  With a proc compile:


proc ireclen=255 lines=yes <b>hold_cursor=no release_cursor=yes  </b>include=/export/home2/ora815/proc/lib include=/export/home2/ora815/rdbms/demo  include=/export/home2/ora815/rdbms/public  include=/export/home2/ora815/plsql/public   include=/export/home2/ora815/network/public iname=t

of this code:


static void process( int empno )
{
int x;
    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
    exec sql select count(*) into :x
               from emp
              where empno = :empno;
}


main( argc, argv )
int argc;
char *  argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR   oracleid[50];
int       value;
EXEC SQL END DECLARE SECTION;
int       i;

    strcpy( oracleid.arr, USERID );
    oracleid.len = strlen(oracleid.arr);

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
    EXEC SQL CONNECT :oracleid;
    printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);

    for( i = 0; i < 10000; i++ )
    {
        process(i);
        exec sql select value into :value
                   from v$mystat
                  where statistic# = ( select statistic#
                                         from v$statname
                                     where name = 'opened cursors current' );
        printf( "%d currently opened cursors\n", value );
        exec sql select value into :value
                   from v$mystat
                  where statistic# = ( select statistic#
                                         from v$statname
                                     where name = 'opened cursors cumulative' );
        printf( "%d cumulative opened cursors\n", value );
    }
    /* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

I see:

$ ./t

Connected to ORACLE as user: /

3 currently opened cursors
6 cumulative opened cursors
3 currently opened cursors
9 cumulative opened cursors
3 currently opened cursors
12 cumulative opened cursors
3 currently opened cursors
15 cumulative opened cursors
3 currently opened cursors
18 cumulative opened cursors
3 currently opened cursors
21 cumulative opened cursors
3 currently opened cursors
24 cumulative opened cursors
3 currently opened cursors
27 cumulative opened cursors
....


My current open cursor count never goes up (due to the use of hold_cursor=no, release_cursor=yes which are the WORST options to use if you care even a tiny bit about performance).  My CUMULATIVE count goes up -- all of the way to over 100,000 (which far exceeds my open_cursor init.ora setting of 

ops$tkyte@ORA815.US.ORACLE.COM> show parameter open

NAME                           TYPE    VALUE
------------------------------ ------- --------------------
open_cursors                   integer 50

Something else must be happening here -- try my sample and see what happens with you.

I ran this on 815 with solaris as well.


In short however, you should NOT use hold_cursor=no, release_cursor=yes.  thats the WORST for performance -- especially for a query you execute over and over. 

The NCOMP in 9i

Connor, October 01, 2001 - 5:45 pm UTC

It'll be interesting to see what difference occurs between explict and implicit under a natively compiled PL/SQL module (9i).

Tom Kyte
October 01, 2001 - 7:41 pm UTC

As there isn't much computationally expensive logic here (most of the code is already "plsql opcodes" -- simple instructions for plsql to execute) there isn't much of a speedup in this case (so its not a great example of NCOMP'ed plsql -- it is not significantly faster ncomped then not).  Here is the example I used (shows how easy ncomping code is though!)

ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'INTERPRETED';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace procedure explicit_interpreted( p_times in number )
  2  as
  3     cursor c is select * from dual;
  4     l_dummy dual.dummy%type;
  5     l_start number default dbms_utility.get_time;
  6  begin
  7      for i in 1 .. p_times
  8      loop
  9         open c;
 10         fetch c into l_dummy;
 11         if ( c%notfound ) then
 12              raise no_data_found;
 13         end if;
 14  
 15         fetch c into l_dummy;
 16         if ( c%found ) then
 17              raise too_many_rows;
 18         end if;
 19  
 20         close c;
 21      end loop;
 22      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs...' );
 23  end;
 24  /

Procedure created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace procedure implicit_interpreted( p_times in number )
  2  as
  3       l_dummy dual.dummy%type;
  4     l_start number default dbms_utility.get_time;
  5  begin
  6      for i in 1 .. p_times
  7      loop
  8         select * into l_dummy from dual;
  9      end loop;
 10      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs...' );
 11  end;
 12  /

Procedure created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'NATIVE';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace procedure explicit_native( p_times in number )
  2  as
  3     cursor c is select * from dual;
  4     l_dummy dual.dummy%type;
  5     l_start number default dbms_utility.get_time;
  6  begin
  7      for i in 1 .. p_times
  8      loop
  9         open c;
 10         fetch c into l_dummy;
 11         if ( c%notfound ) then
 12              raise no_data_found;
 13         end if;
 14  
 15         fetch c into l_dummy;
 16         if ( c%found ) then
 17              raise too_many_rows;
 18         end if;
 19  
 20         close c;
 21      end loop;
 22      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs...' );
 23  end;
 24  /

Procedure created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace procedure implicit_native( p_times in number )
  2  as
  3       l_dummy dual.dummy%type;
  4     l_start number default dbms_utility.get_time;
  5  begin
  6      for i in 1 .. p_times
  7      loop
  8         select * into l_dummy from dual;
  9      end loop;
 10      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs...' );
 11  end;
 12  /

Procedure created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'INTERPRETED';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> exec explicit_interpreted( 50000 );
1461 hsecs...

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> exec implicit_interpreted( 50000 );
1047 hsecs...

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> exec explicit_native( 50000 );
1413 hsecs...

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> exec implicit_native( 50000 );
964 hsecs...

PL/SQL procedure successfully completed. 

THE STORY OF NATIVE COMPILATION OF PL/SQL

A reader, October 01, 2001 - 9:53 pm UTC

Tell us briefly the story behind( briefly) -

'natively compiled PL/SQL module (9i).'


(The only thing we know is that , all the features available in sql will be available to pl/sql starting from 9i. (is it because the sql engine and the pl/sql engine have been combined in 9i ))




Tom Kyte
October 02, 2001 - 7:25 am UTC

Normally PLSQL is compiled into P-CODE (byte code) and then interpreted at runtime, much like Java is.

Native compilation makes it so that the PLSQL is compiled into C code instead, this C code is then compiled into native object code and linked into the database.  Now when the PLSQL executes, it is running as native object code -- not dynamically interpreted.  

You'll see a speed up for computationally expensive operations via this method, for example:

ops$tkyte@ORA9I.WORLD> @test
ops$tkyte@ORA9I.WORLD> set echo off
=================================================================


make sure the settings are setup right for NCOMP'ed plsql


ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> alter system set plsql_native_make_utility = 'make';

System altered.

ops$tkyte@ORA9I.WORLD> alter system set plsql_native_make_file_name = '/export/home/oracle9i/plsql/spnc_makefile.mk';

System altered.

ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'INTERPRETED';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> set echo off
=================================================================

we'll compile an interpreted one (default) first


ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'INTERPRETED';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace
  2  function factorial_interpreted( p_n in number ) return number
  3  is
  4  begin
  5      if ( p_n = 1 )
  6      then
  7          return 1;
  8      else
  9          return factorial_interpreted( p_n-1 ) * p_n;
 10      end if;
 11  end;
 12  /

Function created.

ops$tkyte@ORA9I.WORLD> create or replace procedure run_interpreted( p_times in number )
  2  as
  3          l_answer number;
  4          l_start  number default dbms_utility.get_time;
  5  begin
  6          for i in 1 .. p_times
  7          loop
  8                  l_answer := factorial_interpreted( 50 );
  9          end loop;
 10      dbms_output.put_line( (dbms_utility.get_time-l_start) ||
 11          ' hsecs...' || l_answer );
 12  end;
 13  /

Procedure created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> set echo off
=================================================================

and now NATIVE (too easy)


ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'NATIVE';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace
  2  function factorial_native( p_n in number ) return number
  3  is
  4  begin
  5      if ( p_n = 1 )
  6      then
  7          return 1;
  8      else
  9          return factorial_native( p_n-1 ) * p_n;
 10      end if;
 11  end;
 12  /

Function created.

ops$tkyte@ORA9I.WORLD> create or replace procedure run_native( p_times in number )
  2  as
  3          l_answer number;
  4          l_start  number default dbms_utility.get_time;
  5  begin
  6          for i in 1 .. p_times
  7          loop
  8                  l_answer := factorial_native( 50 );
  9          end loop;
 10      dbms_output.put_line( (dbms_utility.get_time-l_start) ||
 11          ' hsecs...' || l_answer );
 12  end;
 13  /

Procedure created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> set echo off
=================================================================

and the race is on...


ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'INTERPRETED';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> exec run_interpreted(1);
0 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> exec run_native(1);
0 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> exec run_interpreted(10000);
443 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> exec run_native(10000);
287 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> select 287/443 from dual;

   287/443
----------
 .64785553


Here, the native compiled procedure is about 40% faster then the interpreted one. 

Interpreted vs Native Compiled PL/SQL

Padders, October 03, 2001 - 5:26 am UTC

Tom you explain the difference in performance between implicit and explicit cursors as being simply due to the PL/SQL VM having to do more work. If so then why does natively compiled code still have the same overhead? Even if explicit cursor code generates more operations in C, wouldn't each of these operations should be cheaper than when interpreted this making the discrepancy between implicit and explicit cursors smaller, even if still present?

Tom Kyte
October 03, 2001 - 8:17 am UTC

Hey -- more code is more code.

You are still running in the PLSQL VM and most of the logic was very basic PLSQL operations however -- the explicit cursors had to interact with the PLSQL runtime more frequently -- executed more "op codes" if you will.

Even when you turn that into C in this case -- it was STILL doing the same amount of MORE work.

The more you can do in a single statement -- the faster it will be, regardless of whether we use C or PLSQL here. more code is -- more code. More logic takes longer to execute, regardless.

It is just much more efficient to

select x into y from t;

then it is to ask plsql to :

open c
fetch c into y;
if ....
fetch c into y
if ....
close c



EXISTS vs counting?

Kashif, October 18, 2002 - 12:49 pm UTC

Hi Tom,

Enlightening discussion on implicit vs explicit, I always thought explicits were a pain, and now I know they are also slower. Quick couple of questions, you mention earlier in this thread:

"
If you are just
looking for the EXISTENCE of a row:

select count(*) into l_cnt from dual
where exists ( YOUR_QUERY_HERE );

is the efficient way to do that.
"

Is it more efficient than simply doing:

select count (*)
into l_cnt
from
YOUR_FROM_AND_WHERE_CLAUSES_HERE;

--OR

select count (*)
into l_cnt
from
(YOUR_QUERY_HERE);

I'm wondering whether evaluating the existence of a row using an EXISTS and then counting is more efficient than simply doing a count on your query, or using an inline view and then couting the results.

Secondly, I'd like to clarify whether a count (1) is more efficient than a count (*). I've heard from our DBAs that the count (1) takes less time than a count (*). For example:

select count (1)
into l_cnt
from dual;

Any feedback would be useful. Thanks.

Kashif

Tom Kyte
October 18, 2002 - 1:09 pm UTC

yes it is.

the where exists -- STOPS your query as soon as a row is hit.
your count from your query -- finds ALL rows, finishes your query, doesn't stop.



Your DBA's are not telling you the truth. point them to
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1156151916789 <code>



Native compilaton, all or nothing ?

Rob, October 18, 2002 - 9:10 pm UTC

Hi Tom:

I read the following web presentation on native compilation. In there they say that you should natively compile all packages or none, that there is a performance hit from calling non-native compiled packages from natively compiled packages. Can you comment on this.

</code> http://www.dbspecialists.com/presentations/native_plsql.ppt <code>

Tom Kyte
October 19, 2002 - 10:29 am UTC

It all depends doesn't it (why I don't like blanket statements in general...)

anyway, suppose this is true:

a) you have a largish plsql environment (in place)
b) you have a very intense package that is called and does a lot of work
c) it doesn't call out to lots of other things, it gets called -- munches the data and returns
d) it could benefit greatly from native compilation


So, what do you do?  do you compile your entire existing system natively (possibly introducing some other issue -- it is after all a running production system right?).  Or, do you compile this one (or set of some) core routine?  Since it is called and then runs for a while and doesn't interact with other stuff -- it would still benefit greatly from native compilation wouldn't it (yes)

But it is interesting that they said that -- without proving that (especially since they said "don't believe anything someone says, see the proof" essentially in their presenatation.  

Maybe what they observed was a misplaced "cause effect" situation.  The effect was not caused by what they thought it was.  Here is a short test I did:

ops$tkyte@ORA920.US.ORACLE.COM> alter system set plsql_native_make_utility = 'make';
System altered.

ops$tkyte@ORA920.US.ORACLE.COM> alter system set plsql_native_make_file_name = '/usr/oracle/ora920/OraHome1/plsql/spnc_makefile.mk';
System altered.

ops$tkyte@ORA920.US.ORACLE.COM> alter system set plsql_native_library_dir = '/usr/oracle/ora920/OraHome1/native';
System altered.

ops$tkyte@ORA920.US.ORACLE.COM> alter session set plsql_compiler_flags = 'INTERPRETED';
Session altered.

ops$tkyte@ORA920.US.ORACLE.COM> alter session set plsql_compiler_flags = 'INTERPRETED';
Session altered.

ops$tkyte@ORA920.US.ORACLE.COM> create or replace procedure do_something_interpreted( l_data in varchar2 )
  2  as
  3          l_tmp varchar2(255);
  4  begin
  5          for i in 1 .. 100
  6          loop
  7                  l_tmp := substr( l_data, 1, i );
  8          end loop;
  9  end;
 10  /
Procedure created.


ops$tkyte@ORA920.US.ORACLE.COM> alter session set plsql_compiler_flags = 'NATIVE';
Session altered.

ops$tkyte@ORA920.US.ORACLE.COM> create or replace procedure do_something_native( l_data in varchar2 )
  2  as
  3          l_tmp varchar2(255);
  4  begin
  5          for i in 1 .. 100
  6          loop
  7                  l_tmp := substr( l_data, 1, i );
  8          end loop;
  9  end;
 10  /
Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM> alter session set plsql_compiler_flags = 'INTERPRETED';
Session altered.

ops$tkyte@ORA920.US.ORACLE.COM> create or replace procedure interpreted_calls_interpreted( p_times in number )
  2  as
  3  begin
  4          for i in 1 .. p_times
  5          loop
  6                  do_something_interpreted( 'Hello World!!!!' );
  7          end loop;
  8  end;
  9  /
Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM> create or replace procedure interpreted_calls_native( p_times in number )
  2  as
  3  begin
  4          for i in 1 .. p_times
  5          loop
  6                  do_something_native( 'Hello World!!!!' );
  7          end loop;
  8  end;
  9  /
Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM> alter session set plsql_compiler_flags = 'NATIVE';
Session altered.

ops$tkyte@ORA920.US.ORACLE.COM> create or replace procedure native_calls_interpreted( p_times in number )
  2  as
  3  begin
  4          for i in 1 .. p_times
  5          loop
  6                  do_something_interpreted( 'Hello World!!!!' );
  7          end loop;
  8  end;
  9  /
Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM> create or replace procedure native_calls_native( p_times in number )
  2  as
  3  begin
  4          for i in 1 .. p_times
  5          loop
  6                  do_something_native( 'Hello World!!!!' );
  7          end loop;
  8  end;
  9  /
Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM> set timing on

ops$tkyte@ORA920.US.ORACLE.COM> exec interpreted_calls_interpreted( 10000 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.23

ops$tkyte@ORA920.US.ORACLE.COM> exec interpreted_calls_interpreted( 10000 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.26

ops$tkyte@ORA920.US.ORACLE.COM> exec interpreted_calls_native( 10000 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.79

ops$tkyte@ORA920.US.ORACLE.COM> exec interpreted_calls_native( 10000 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.75

ops$tkyte@ORA920.US.ORACLE.COM> exec native_calls_interpreted( 10000 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.20

ops$tkyte@ORA920.US.ORACLE.COM> exec native_calls_interpreted( 10000 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.14

ops$tkyte@ORA920.US.ORACLE.COM> exec native_calls_native( 10000 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.76

ops$tkyte@ORA920.US.ORACLE.COM> exec native_calls_native( 10000 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.77

<b>which seems to bolster my point -- you have something that takes a bit of time (my do_something routines).  You natively compile it.  It runs faster -- regardless of how it is called.  

Argueably -- I did lots of context switching here (10,000 times each test).  The bottom line was "if the do something was ncomped, it ran faster"</b>
 

Going back to the original argument...

Kashif, October 30, 2002 - 1:27 pm UTC

Hey Tom,

This is an extension to point #2 made by Graeme King earlier in the thread, and then you're response to it. Now we've established that implicits are faster than explicits. However, if we have a situation inside a stored pl/sql routine where we have to do something like:

select col1 into var1 from tab1 where col2 = 'a';
..
select col1 into var2 from tab1 where col2 = 'b';
..
select col1 into var3 from tab1 where col2 = 'c';

we know that we're opening up a separate cursor for each of these selects, not good. But we also don't want to declare one parameterized cursor and then keep opening, fetching and closing either, per our discussion. Wouldn't it then be better to simply declare a function with the required arguments, and then call that function each time we need to select? E.g.:

function test (
param1 in tab1.col2%type)
return tab1.col1%type
is
l_var tab1.col1%type;
begin
select col1
into l_var
from tab1
where col2 := param1;
return (l_var);
end;
...
--and then...
...
var1 := test ('a');
...
var2 := test ('b');
...
var3 := test ('c');

I know I read this somewhere, but I was wondering what you're thoughts are on this. In this manner we also avoid the potential that the pl/sql engine parses two idential selects issued at different points in the routine separately, creating two cursors for the same exact sql. What say you?

Kashif

Tom Kyte
October 31, 2002 - 8:23 pm UTC

I say that is the foundation of modular coding and I dig it -- it is the way I program.


Yes, it would be sound to move this operation into a function like that.

A real performance gain?

Andy Barker, April 14, 2003 - 12:39 pm UTC

Having reviewed the threads on this site regarding the performance benefits of implicit vs. explicit cursors I was motivated to run my own benchmark. While I don't doubt the performance benefit demonstrated by your benchmarks, a more "real world" test I ran resulted in no statistically significant difference in the timing between implict and explicit cursors.

My test involved looping 40000 times and testing for the presence of a row. If it existed the row was updated, if not a new one inserted. Each run resulted in 20000 updates and 20000 inserts. Three coding methods were used:

1. Explicit cursor
open c_exists;
fetch c_exists into i_dummy;
b_exists := c_exists%FOUND;
close c_exists;
--
if b_exists then
-- UPDATE ...
else
-- INSERT ...
end if;

2. Implict cursor selecting count of rows to branch
SELECT
count(*)
INTO
i_count
FROM
cursor_test
WHERE
col1 = p_input_id;
--
if i_count = 0 then
-- INSERT ...
else
-- UPDATE ...
end if;

3. Implicit cursor using NO_DATA_FOUND exception to branch
begin
SELECT
1
INTO
i_dummy
FROM
cursor_test
WHERE
col1 = p_input_id;
--
-- UPDATE ...
exception
when NO_DATA_FOUND then
-- INSERT ...
end;

Each test was run, the time noted and then rolled back. Thus each test was hitting the same data blocks. Ignoring the first run of each type of test to remove any parsing and caching overhead my results were as follows:

EXPLICIT
0:42 0:41 0:39 0:40 0:43

IMPLICIT
0:43 0:40 0:39 0:39 0:40

EXCEPTION
0:42 0:40 0:42 0:40 0:43

I would welcome your thoughts as to why I didn't observe the performance differences you have demonstrated in your postings in this and other threads.


Tom Kyte
April 14, 2003 - 1:54 pm UTC

My major number one point is -- people have been trained (for whatever reason) to believe "explicit cursors good, explicit cursors faster, explicit cursors better"

I leveled the playing field. You need never fear the implicit select into.

Explicit is not better.
Explicit is not faster.


Also -- i cannot see your entire test from front to back -- but since the vast preponderance of runtime would be spent doing the insert/update -- one would have to say "what were you comparing". You were primarily comparing the runtimes of the modifications as the select into in this case isn't the slow thing. Also why do you compare a count(*) to something totally different??? make them the same. If you only care about the first row -- use

select x into y from z where ... AND ROWNUM=1;

don't do count(*)'s -- yuck.

You want to see if explicit is faster/slower/the same as implicit?
Then isolate your test to that functionality.

Is it going to make your code 2x as fast?
Nope.

Is it going to go slower?
Nope.

Is it going to go marginally faster?
Yup.

If that were my process, it would be:


update ...
if ( sql%rowcount = 0 )
then
insert ..
end if;

and we wouldn't be doing a select into at all.

Count from dual vs count from table.

Darren, April 14, 2003 - 5:54 pm UTC

Tom,

Whilst i understand your point on
" select count(*) into l_cnt from dual
where exists ( YOUR_QUERY_HERE );"
bieng faster than
select count(*)
into l_cnt
from YOUR_TABLE
where blah...

i usually code this as:
select count(*)
into v_cnt
from MY_TABLE
where blah
and rownum = 1;

whats the (dis)advantages of this approach? i have used tkprof on the dual method and the rownum stopkey approach and they are virtually identical (the exists approach has slightly extra IO from "dual").

p.s. i was testing on a RBO 8.1.7.2 database.



Tom Kyte
April 14, 2003 - 8:27 pm UTC

The where rownum = 1 works very well -- i use the interchangeably, depending on my mood.

The where exists seems more "semantically correct"
The where rownum = 1 is less typing and removes 4 LIOs (in 8i) from the query (to full scan DUAL)

Steven Feuerstein vs. Tom

Jens Kropp, June 24, 2003 - 9:37 am UTC

The PL/SQL Guru Steven Feuerstein wrote the exact opposite.

He would NEVER use implicit cursors. In his PL/SQL-Book he used the same arguments we found here in this topic from some "explicit cursor fans" and he also said that the performance gain with implicit cursors is minimal.

Hey, that would be a great discussion Guru Tom vs. Guru Feuerstein! What do you think?

Tom Kyte
June 25, 2003 - 9:05 am UTC

he has changed his mind in the recent past.



A reader, June 25, 2003 - 9:40 am UTC

Tom is Guru of gurus.
Perhaps only Jonathan lewis can match his brilliance to some extent.....

Tom Kyte
June 25, 2003 - 7:03 pm UTC

I wish it were true, I feel not so smart standing next to him actually.

You are being modest.

A reader, June 26, 2003 - 7:21 am UTC



Single row Cursor vs. SELECT statement

Ashutosh, December 31, 2003 - 8:36 am UTC

Tom,
I have come across an existing code where cursors are written to return single row data, which can be achieved by writing a single SQL statement. example:

Definition of Cursor:

cursor crd_lmt (cust_code varchar2) is
select credit_limit
from customer_credit_lmt
where customer_code = cust_code
and effective_date = (select max(effective_date)
from customer_credit_lmt
where customer_code = cust_code
and effective_date < sysdate);

Subsequent operations:

begin
open crd_lmt (o.customer_code);
fetch crd_lmt into credit_lmt;
if crd_lmt%NOTFOUND then
credit_lmt := null;
end if;
close crd_lmt;
end;

As I believe that by writing a simple SQL statement this result could have been achieved.

I wanted to know how much performance enhancement (if any) I would gain if it will be converted to single SQL statement call?

Thanks in advance,


Tom Kyte
December 31, 2003 - 10:08 am UTC

that should clearly be:


begin
select credit_limit into crd_lmt
from customer_credit_lmt
where customer_code = cust_code
and effective_date = (select max(effective_date)
from customer_credit_lmt
where customer_code = cust_code
and effective_date < sysdate);
exception
when no_data_found then null;
end;

and if concerned on the TOO_MANY_ROWS exception that could be thrown if two records exist for the same customer_code/effective_date -- you would just add "and rownum = 1" to the query (although I'd be concerned that there could be two records -- which the current code won't detect but the select into will).



another way to code that would be:

begin
select credit_limit into crd_lmt
from (select credit_limit
from customer_credit_lmt
where customer_code = cust_code
and effective_date < sysdate
order by effective_date DESC)
where rownum = 1;
exception
when no_data_found then null;
end;

which can be much more efficient -- consider:

/*
drop table t;

create table t
as
select mod(rownum,1000) customer_code, created effective_date, object_id credit_limit, object_name other_data
from all_objects;

create index t_idx on t(customer_code, effective_date, credit_limit);

analyze table t compute statistics for table for all indexes for all indexed columns;
*/

create or replace procedure proc1
as
l_credit_limit1 number;
l_credit_limit2 number;
begin
for i in 0..999
loop
begin
select credit_limit into l_credit_limit1
from (select credit_limit
from t
where customer_code = i
and effective_date < sysdate
order by effective_date DESC)
where rownum = 1;
exception
when no_data_found then l_credit_limit1 := null;
end;
begin
select credit_limit into l_credit_limit2
from t
where customer_code = i
and effective_date = (select max(effective_date)
from t
where customer_code = i
and effective_date < sysdate);
exception
when no_data_found then l_credit_limit2 := null;
end;
if ( l_credit_limit1 <> l_credit_limit2 or
(l_credit_limit1 is null and l_credit_limit2 is not null ) or
(l_credit_limit2 is null and l_credit_limit1 is not null ) )
then
raise program_error;
end if;
end loop;
end;
/

alter session set sql_trace=true;
exec proc1


tkprof shows us:

SELECT credit_limit from (select credit_limit
from t
where customer_code = :b1
and effective_date < sysdate
order by effective_date DESC)
where rownum = 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.23 0.26 0 0 0 0
Fetch 1000 0.09 0.08 0 2000 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 0.32 0.34 0 2000 0 1000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 147 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1000 COUNT STOPKEY (cr=2000 r=0 w=0 time=67652 us)
1000 VIEW (cr=2000 r=0 w=0 time=54803 us)
1000 INDEX RANGE SCAN DESCENDING T_IDX (cr=2000 r=0 w=0 time=48422 us)(object id 32937)

********************************************************************************

SELECT credit_limit from t
where customer_code = :b1
and effective_date = (select max(effective_date)
from t
where customer_code = :b1
and effective_date < sysdate)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.43 0.39 0 2000 0 0
Fetch 1000 0.01 0.03 0 2006 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 0.44 0.42 0 4006 0 1000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 147 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1000 INDEX RANGE SCAN T_IDX (cr=2006 r=0 w=0 time=22097 us)(object id 32937)


the "order by" uses 1/2 the LIO's by avoiding the index scan twice.

but play around with different queries -- you might be surprised.





SELECT BULK COLLECT INTO

Dan Clamage, January 07, 2004 - 9:22 am UTC

A lot of explicit cursors can be rewritten as SELECT BULK COLLECT INTO. Then do the looping on the collection.

If you expect a LOT of rows, then go with an explicit cursor and FETCH BULK COLLECT INTO LIMIT in a loop.

Fewer context switches make for faster execution.

I really like the flexibility with bulk collects in 9.2.0.4.0. I can define a nested table as an explicit cursor %rowtype, so that if the cursor's SELECT clause changes, I don't have to change much else. Then just BULK COLLECT right into the composite collection. Sweet.

Ref cursors also offer added flexibility. I use them when I want the calling context to have the flexibility of determining what it wants to do with the result set. I can bulk collect from the ref cursor too. Or maybe it just wants the first 1 or N rows. Lower level routines shouldn't dictate much to the higher level routines that call them.

There are appropriate times to use implicit or explicit cursors.

If your code becomes torturous and convoluted, there's probably a cleaner way. Don't be afraid to rewrite entire sections of code. If you can do something more cleanly or elegantly, it's worth the trouble to rewrite it. Small improvements add up over time and effort.

Nothing worse than maintaining someone else's code and saying to oneself, "What the heck is this guy trying to do here?". Or on the occasions where you get to maintain your own code, "What the heck was I thinking?"

10g implicit cursors

Zoran Martic, February 06, 2004 - 6:34 am UTC

Hi Tom,

Now I found why is good sometimes folowing your path.

You said using constructs like this is the best and very easy to use:

for i in (select * from table_x) loop

In 10g Oracle finally optimizes this thing to use some sort of prefetching and it went 10x faster in my simple test then in 9i or 8i.
I was thinking why and how, but basically statistics showed me that on 8i and 9i, both explicit and implicit cursors are doing 1 db block read per one fetch (disaster that Oracle is doing this when you have in OCI prefetching from XX release of Oracle). In 10g they are finally optimizing fetches but only for implicit cursors :)

That means who ever coded for loops with Select inside will benefit immediately with upgrade to 10g.

In 9i you need to opt to BULK COLLECT to be fast as 10g normal for i in (select ....

Again, explicit cursors are working bad as in 8i, 9i story.
That is a shame for Oracle.
No reason that new smart optimizer cannot find out that you are going to loop in both cases: implicit or explicit.

Did not put tests and results because they are too obvious (10 lines of code).
Just loop through some big table and check that you are not hitting funny disks to make your results pointless.

In any case I wrote a lot of code your way with putting SQL inside the for loop and I am happy now, because upgrade to 10g will automatically benefit me :)

It is very strange that I realize now, after many years that this thing (looping through cursors) in PL/SQL was that bad. Without prefetching (I used from Oracle 7.3) in PL/SQL in 8i, 9i !!!!!

Regards,
Zoran










Tom Kyte
February 07, 2004 - 12:28 pm UTC

oh, that is cool -- I did not even realize that was happening :)

It is trivial to show, just:

begin
for x in ( select * from all_objects where rownum < 10000)
loop
null;
end loop;
end;

and in 9i, you'll see:


select *
from
all_objects where rownum < 10000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.27 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 10000 3.40 3.78 11 81153 0 9999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10002 3.44 4.05 11 81153 0 9999

and in 10g, you'll see:

SELECT *
FROM
ALL_OBJECTS WHERE ROWNUM < 10000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.10 0.24 0 121 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 100 1.13 1.18 0 15756 0 9999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 102 1.24 1.42 0 15877 0 9999



they are using a 100 (my favorite) row arraysize....


:)




Cursors

Zoran Martic, February 09, 2004 - 10:05 am UTC

Tom,

Yes, that is great.
100 is also my favourite or to say starting prefetching count, but of course depends mostly on the size and number of columns you are retreiving.
But it is very easy to find out for any SQL what is the best prefetching count with simple tests.

Maybe in Oracle 11 will be implemented some adaptive prefetching :)

This thing is looking as much bigger improvement in PL/SQL then some others mentioned in the docs :)

Thanks,
Zoran


this a bug or what ?

Robert, March 24, 2004 - 2:41 pm UTC

Tom, code below works on 8.1.7 but busts on 9iR1
(I think i does not happen on 9iR2)

DECLARE
CURSOR cur_selectlist
IS
SELECT BANNER || '?' x FROM v$version
UNION ALL
SELECT 'Hello' x FROM dual;

BEGIN
FOR i IN cur_selectlist
LOOP
dbms_output.put_line('this works on ' || i.x || '--Yes');
EXIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
FOR i IN (SELECT banner FROM v$version)
LOOP
dbms_output.put_line(i.banner);
END LOOP;
END;

-- 8.1.7 OUTPUT ----------------
this works on Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production?---Yes

-- 9i OUTPUT ----------------
ORA-06502: PL/SQL: numeric or value error
Personal Oracle9i Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production

Tom Kyte
March 24, 2004 - 2:55 pm UTC

seems to be the union all with the concat.... repros in 9ir1, but not the latest 9ir2 (but apparently did in the first 9ir2's which you have above...)

ops$tkyte@ORA9IR1> DECLARE
  2    CURSOR cur_selectlist
  3     IS
  4     SELECT 'x'||'x' x
  5       FROM dual
  6      UNION ALL
  7     SELECT 'x' x
  8       FROM dual;
  9
 10  BEGIN
 11    FOR i IN cur_selectlist
 12    LOOP
 13       null;
 14    END LOOP;
 15  END;
 16  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 11
 
ops$tkyte@ORA9IR2> DECLARE
  2    CURSOR cur_selectlist
  3     IS
  4     SELECT 'x'||'x' x
  5       FROM dual
  6      UNION ALL
  7     SELECT 'x' x
  8       FROM dual;
  9
 10  BEGIN
 11    FOR i IN cur_selectlist
 12    LOOP
 13       null;
 14    END LOOP;
 15  END;
 16  /
 
PL/SQL procedure successfully completed.



Yes, it would be a "product issue"

found this:

BugTag:             Support notes on <Bug:1918751>  - DDR info <BugDesc:1918751>
Affects:            RDBMS (90-A0)
NB:                    FIXED
Abstract:           ORA-6502 possible from UNION and CONCAT (||) with constant strings
Fixed-Releases:     9014 9202 A000

Details:            
  This problem is introduced in 9.0.1.0.
  "ORA-6502 Value or Numeric error" can occur when using a 
  PLSQL block  that has UNION and CONCAT clauses involving 
  constant strings.
  eg: for I in 
        (SELECT 'TEXT01 ' ||'TEXT02 ' stmt FROM  sys.dual
          UNION        
         SELECT 'TEXT11 ' stmt FROM  sys.dual) loop ... 

explicit

Muhammad Ibrahim, March 24, 2004 - 8:47 pm UTC

Dear Tom,
In some situation we may need to use Ref cursor so again
open
fetch
close
According to my understanding the ref cursor looks like explicit cursor. How about the performance then? Or is it possible to use Ref cursor as a implicit cursor i mean

For i in ( lRef_Cursor ) Loop
End Loop;
I am not that much familiar with Ref cursor! Please clarify. I have seen many of ref cursor you have defined in this site with open,fetch,close!

Regards,
Ibrahim.


Tom Kyte
March 25, 2004 - 8:54 am UTC

You cannot use a ref cursor in a loop like that. it would only be

open
loop
fetch into


exit when...
end loop;
close;


that is the only way to process a ref cursor.

Ref cursor

Muhammad Ibrahim, March 25, 2004 - 8:14 pm UTC

Dear Tom,

Then if we use ref cursor it may not be a good idea. Is that right?

Regards,
Ibrahim.

Tom Kyte
March 25, 2004 - 9:39 pm UTC

the rule is:

use STATIC sql in plsql until you get into a situation where you cannot use static SQL. ref cursors are a tool, they are neither 100% evil nor 100% good -- like EVERYTHING in the database.

Is there a known bug in Oracle 9.2?

Jens, May 07, 2004 - 11:48 am UTC

Hi Tom,

I was asked to look at a program, that runs for hours. Now I found out, that the implicit cursor causes this behaviour.

So I build up a simple test case with an explicit cursor:

DECLARE
CURSOR c1 IS
SELECT [..]
FROM big_table a -- 2,8 Million Records
-- The table was filled up via Direct-Path-Insert.
;

anz NUMBER := 0;

BEGIN
FOR rec IN c1 LOOP
anz := anz + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE( anz );
END;

The statement runs in 220 Sec. When I put the select into an implicit cursor it runs hours (I stopped it after a half hour when I saw time remaining about 5000 Sec. in V$SESSION_LONGOPS).

We run Oracle 9.2.0.2.

Is there a bug in Oracle?

Bye,

Jens


Tom Kyte
May 07, 2004 - 1:13 pm UTC

huh???


what "statement" runs in 220 seconds?

if you are saying "hey, i fired up this toad thing and ran the query and it ran in 220 seconds", well, then you are wrong (cause toad and most other gui's) just fetch the first couple.


And what did the fetching, if it was sqlplus, it fetched in arrays, not slow by slow (opps, meant row by row)


processing row by row by row = slow as slow as slow can get.

set operations = operating at the speed of light.


There is "no bug here", there is inefficient coding for sure, but not a bug.

if you think there is, use sql_trace and see what tkprof has to say about the performance of an implicit vs explicit cursor.

My test case

Jens, May 10, 2004 - 5:16 am UTC

Hi Tom, 

my DBA is today to busy for generating me a trace file :-( 

But now I could reproduce it:

I created a "big_table"

SQL> create table big_table as select * from all_objects nologging;

and inserted a lot of rows:

SQL> insert /*+append*/ into big_table select * from big_table;
...

-------------------------------------------------------
First try with an explicit cursor (look at the TRUNC)

11:41:06 SQL> SET SERVEROUTPUT ON
11:41:06 SQL>
11:41:06 SQL> DECLARE
11:41:06   2      CURSOR c1 IS
11:41:06   3      SELECT  owner,
11:41:06   4              object_name,
11:41:06   5              subobject_name,
11:41:06   6              object_id,
11:41:06   7              data_object_id,
11:41:06   8              object_type,
11:41:06   9              created,
11:41:06  10              TRUNC( last_ddl_time ) as last_ddl,
11:41:06  11              timestamp,
11:41:06  12              status,
11:41:06  13              temporary,
11:41:06  14              generated,
11:41:06  15              secondary
11:41:06  16      FROM    big_table
11:41:06  17      ;
11:41:06  18
11:41:06  19      anz NUMBER := 0;
11:41:06  20
11:41:06  21  BEGIN
11:41:06  22      FOR c IN c1
11:41:06  23      LOOP
11:41:06  24          anz := anz + 1;
11:41:06  25      END LOOP;
11:41:06  26      dbms_output.put_line( 'Anzahl: '||anz );
11:41:06  27  END;
11:41:06  28  /
Anzahl: 2359004

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Abgelaufen: 00:03:186.18

=> Runs in 3 Minutes

-------------------------------------------------------
Second try with an implicit cursor (no TRUNC)

11:44:12 SQL>
11:44:12 SQL> DECLARE
11:44:12   2      anz NUMBER := 0;
11:44:12   3  BEGIN
11:44:12   4      FOR c IN ( SELECT  owner,
11:44:12   5                         object_name,
11:44:12   6                         subobject_name,
11:44:12   7                         object_id,
11:44:12   8                         data_object_id,
11:44:12   9                         object_type,
11:44:12  10                         created,
11:44:12  11                         last_ddl_time,
11:44:12  12                         timestamp,
11:44:12  13                         status,
11:44:12  14                         temporary,
11:44:12  15                         generated,
11:44:12  16                         secondary
11:44:12  17                 FROM    big_table
11:44:12  18               )
11:44:12  19      LOOP
11:44:12  20          anz := anz + 1;
11:44:12  21      END LOOP;
11:44:12  22      dbms_output.put_line( 'Anzahl: '||anz );
11:44:12  23  END;
11:44:12  24  /
Anzahl: 2359004

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Abgelaufen: 00:02:167.21

=> Runs in 2-3 Minutes

-------------------------------------------------------
Third try with an implicit cursor (now with TRUNC)

11:47:00 SQL>
11:47:00 SQL>
11:47:00 SQL> DECLARE
11:47:00   2      anz NUMBER := 0;
11:47:00   3  BEGIN
11:47:00   4      FOR c IN ( SELECT  owner,
11:47:00   5                         object_name,
11:47:00   6                         subobject_name,
11:47:00   7                         object_id,
11:47:00   8                         data_object_id,
11:47:00   9                         object_type,
11:47:00  10                         created,
11:47:00  11                         TRUNC( last_ddl_time ),
11:47:00  12                         timestamp,
11:47:00  13                         status,
11:47:00  14                         temporary,
11:47:00  15                         generated,
11:47:00  16                         secondary
11:47:00  17                 FROM    big_table
11:47:00  18               )
11:47:00  19      LOOP
11:47:00  20          anz := anz + 1;
11:47:00  21      END LOOP;
11:47:00  22      dbms_output.put_line( 'Anzahl: '||anz );
11:47:00  23  END;
11:47:00  24  /
^C

=> Ok, I stopped it after 10 Minutes when I saw a TIME_REMAINING of 5126 seconds:

OPNAME     TARGET        SOFAR TOTALWORK UNITS  START_TIME          LAST_UPDATE_TIME    TIME_REMAINING ELAPSED_SECONDS             
---------- ------------- ----- --------- ------ ------------------- ------------------- -------------- ---------------
Table Scan ZIX.BIG_TABLE 3292  29739     Blocks 10.05.2004 11:47:01 10.05.2004 11:57:39 5126           638 
 

Tom Kyte
May 10, 2004 - 8:27 am UTC

9204 on linux:

big_table@ORA9IR2> set timing on
big_table@ORA9IR2>
big_table@ORA9IR2> DECLARE
2 CURSOR c1 IS
3 SELECT owner,
4 object_name,
5 subobject_name,
6 object_id,
7 data_object_id,
8 object_type,
9 created,
10 TRUNC( last_ddl_time ) as last_ddl,
11 timestamp,
12 status,
13 temporary,
14 generated,
15 secondary
16 FROM big_table
17 ;
18 anz NUMBER := 0;
19 BEGIN
20 FOR c IN c1
21 LOOP
22 anz := anz + 1;
23 END LOOP;
24 dbms_output.put_line( 'Anzahl: '||anz );
25 END;
26 /
Anzahl: 4000000

PL/SQL procedure successfully completed.

Elapsed: 00:01:38.68
big_table@ORA9IR2>
big_table@ORA9IR2> DECLARE
2 anz NUMBER := 0;
3 BEGIN
4 FOR c IN ( SELECT owner,
5 object_name,
6 subobject_name,
7 object_id,
8 data_object_id,
9 object_type,
10 created,
11 last_ddl_time,
12 timestamp,
13 status,
14 temporary,
15 generated,
16 secondary
17 FROM big_table
18 )
19 LOOP
20 anz := anz + 1;
21 END LOOP;
22 dbms_output.put_line( 'Anzahl: '||anz );
23 END;
24 /
Anzahl: 4000000

PL/SQL procedure successfully completed.

Elapsed: 00:01:25.49
big_table@ORA9IR2>
big_table@ORA9IR2>
big_table@ORA9IR2> DECLARE
2 anz NUMBER := 0;
3 BEGIN
4 FOR c IN ( SELECT owner,
5 object_name,
6 subobject_name,
7 object_id,
8 data_object_id,
9 object_type,
10 created,
11 TRUNC( last_ddl_time ),
12 timestamp,
13 status,
14 temporary,
15 generated,
16 secondary
17 FROM big_table
18 )
19 LOOP
20 anz := anz + 1;
21 END LOOP;
22 dbms_output.put_line( 'Anzahl: '||anz );
23 END;
24 /
Anzahl: 4000000

PL/SQL procedure successfully completed.

Elapsed: 00:01:37.00
big_table@ORA9IR2>



I cannot reproduce at all.

Perhaps a patch wopuld help...

Jens, May 10, 2004 - 9:49 am UTC

I tried my example more than 5 times, everytime the cursor was running very very slowly (SOFAR in V$SESSION_LONGOPS goes up for 2 Blocks in a second)

We run 9.2.0.2 on Solaris (64 Bit). And client version is 8.1.7.0 (don't ask me why). So I gave it to the DBA's. I hope I will get an answer from them in a few days.






10g prefetching

Marvin, May 14, 2004 - 7:43 am UTC

Hi

Does implicit cursors in 10g now do implciit bulk forall processing?

Tom Kyte
May 14, 2004 - 10:23 am UTC

yes. array fetches 100 rows at a time.

Implicit cursors are faster than explicit?

Tomasz Michniewski, July 27, 2004 - 7:15 am UTC

I am answering to the original Tom's answer.

I wonder whether you took into consideration the fact, that SELECT INTO has to check if there is another record satisfying the select or not. If so, then TO_MANY_ROWS exception has to be raised. In some cases the first row might be find fast, but looking for the next may take a while. On the contrary, single fetch doesn't care about the second record and that's why single fetch might be faster.

Of course interpreted code is slower, but I am afraid that looking for the second record might sometimes slow down the processing by the bigger factor that we get comparing the length of code. If we compare disk operations, then differences on code level are minor.

Moreover - it is not true that longer interpreted code runs slower than shorter interpreted code - we should remember about the algorithm. In the given case, the database performs different actions (additional looking for the second record by SELECT INTO statement).

Tom Kyte
July 27, 2004 - 7:46 am UTC

and did you think that if there were two records -- select into would let you know that and that your single fetch would not???????

Yes, I took that wholly into consideration -- to the effect of saying what a WONDERFUL thing this is.


If you want just the first row, 


select * into foo from t where ....your condition.... AND ROWNUM=1;

will achieve the same, be faster, easier to code, yadda yadda yadda.


The fact is -- if you are doing a select into and there could be two records, well, you got a problem there and you NEED TO BE MADE AWARE OF THAT.  In the exceeding RARE case you really don't care -- in the exceedingly rare case that you just want some RANDOM RECORD (that is what you are saying -- you don't care what record you get, just get one) "and rownum=1" solves the issue nicely.

that the database performs this extra check is AWESOME and virtually everyone would benefit from it! it would (it has time and time again) found bugs in the data no one was aware of.

I wrote about this extensively in my book "Effective Oracle By Design" -- here is an excerpt from that:


<b>Use Implicit Cursors for Single-Row Selections</b>

Suppose that we have a query that will return at most one row but may return less than one row. We can code this:

Select <columns> INTO <variables>
  From <tables> 

Or we can code this:

Is
   Cursor c is select <columns> from <tables> ....
   ...
Begin
   Open c;
   Fetch c into <variables>;
   Close c;
   ...

Which should we use? Without exception, we should use SELECT INTO, for the following reasons:

o It is less code (less chance for bugs) and easier to read.
o It runs faster (more efficient).
o It makes our code safer (more bug-free).

In order to fully emulate the entire functionality of a SELECT INTO, we must code this:

...
Is
   Cursor c is select <columns> from <tables> ?
   ?
Begin
   Open c;
   Fetch c into <variables>;
   If ( c%notfound ) 
   Then
      Raise NO_DATA_FOUND;
   End if;
   Fetch c into <variables>;
   If ( c%found ) 
   Then
      Raise TOO_MANY_ROWS;
   End if; 
   Close c;
   ...

The semantics of a SELECT INTO are such that the query must return at least one row and at most one row. The explicit cursor makes you code that yourself. 
Can you spot the bugs in the following code? This code is designed to report by department the department number, name, and the name of the most highly paid person in that department:

ops$tkyte@ORA920> declare
  2      l_dname  dept.dname%type;
  3      l_deptno dept.deptno%type;
  4      l_ename  emp.ename%type;
  5
  6      cursor c1
  7      is
  8      select deptno, dname
  9        from dept
 10       order by deptno;
 11
 12      cursor c2( p_deptno in number )
 13      is
 14      select ename
 15        from emp
 16       where deptno = p_deptno
 17         and sal = (select max(sal)
 18                      from emp
 19                     where deptno = p_deptno);
 20  begin
 21      open c1;
 22      loop
 23          fetch c1 into l_deptno, l_dname;
 24          exit when c1%notfound;
 25          open c2(l_deptno);
 26          fetch c2 into l_ename;
 27          close c2;
 28          dbms_output.put_line
             ( l_deptno || ', ' || l_dname || ', ' || l_ename );
 29      end loop;
 30      close c1;
 31  end;
 32  /

The two bugs are that it erroneously reports an employee name for Department 40 and erroneously forgets to tell us there are two people that make the most in Department 20. The output is as follows:

10, ACCOUNTING, KING
20, RESEARCH, SCOTT
30, SALES, BLAKE
40, OPERATIONS, BLAKE

PL/SQL procedure successfully completed.


Those are bugs that might go undetected for a long time, leading to other erroneous decisions. At the very least, they make you look bad when discovered; at worst, they lead to other data-integrity issues. 

Let's see what happens when we rewrite the code to use implicit cursors.

ops$tkyte@ORA920> declare
  2      l_ename  emp.ename%type;
  3  begin
  4      for x in (select deptno, dname from dept order by deptno)
  5      loop
  6          select ename into l_ename
  7            from emp
  8           where deptno = x.deptno
  9             and sal = (select max(sal)
 10                          from emp
 11                         where deptno = x.deptno);
 12          dbms_output.put_line
 13          ( x.deptno || ', ' || x.dname || ', ' || l_ename );
 14      end loop;
 15  end;
 16  /
10, ACCOUNTING, KING
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6

Immediately, this is telling us, "That query you expected a single row from returned two (or more)." We have a choice to make here now:

o We can report the first one back only. We would add and rownum = 1 to the query to indicate that was our intention.
o We can report all of the highest paid people.


Let's say we want to report just the first occurrence. We'll add the WHERE clause and restart.


ops$tkyte@ORA920> declare
  2      l_ename  emp.ename%type;
  3  begin
...
  9             and sal = (select max(sal)
 10                          from emp
 11                         where deptno = x.deptno)
 12                     and rownum = 1;
 13          dbms_output.put_line
...
 17  /
10, ACCOUNTING, KING
20, RESEARCH, SCOTT
30, SALES, BLAKE
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6

Now, Oracle is telling us that for some DEPTNO, there is no data to be found. The first query using explicit cursors found data didn't it? How could this be? The fact is that the explicit cursor did not find any data, but we didn't check. The SELECT INTO won't permit that; it makes us handle this case by throwing an exception. Here is our solution for that:

ops$tkyte@ORA920> declare
  2      l_ename  emp.ename%type;
  3  begin
  4      for x in (select deptno, dname from dept order by deptno)
  5      loop
  6          begin
  7             select ename into l_ename
  8               from emp
  9              where deptno = x.deptno
 10                and sal = (select max(sal)
 11                             from emp
 12                            where deptno = x.deptno)
 13             and rownum = 1;
 14          exception when no_data_found
 15          then
 16              l_ename := '(none)';
 17          end;
 18          dbms_output.put_line
 19          ( x.deptno || ', ' || x.dname || ', ' || l_ename );
 20      end loop;
 21  end;
 22  /
10, ACCOUNTING, KING
20, RESEARCH, SCOTT
30, SALES, BLAKE
40, OPERATIONS, (none)

PL/SQL procedure successfully completed.

Now, we have a debugged, safe report. It gets the right answer and takes a lot less code as well. But how does it perform? 

Let's compare the performance of a SELECT INTO with that of an OPEN/FETCH/TEST/FETCH/CLOSE. To set up this test, well create a table to look up from:


ops$tkyte@ORA920> create table t ( object_id primary key, object_name )
  2  organization index
  3  as
  4  select object_id, object_name from all_objects;

Table created.

Next, we'll set up two procedures to run against it.

ops$tkyte@ORA920> create or replace procedure explicit
  2  as
  3      l_object_name t.object_name%type;
  4      l_dummy       t.object_name%type;
  5
  6      cursor c( l_object_id in number )
  7      is
  8      select object_name
  9        from t
 10       where object_id = l_object_id;
 11  begin
 12      for i in 1 .. 30000
 13      loop
 14          open c(i);
 15          fetch c into l_object_name;
 16          if ( c%notfound )
 17          then
 18              l_object_name := null;
 19          end if;
 20          fetch c into l_dummy;
 21          if ( c%found )
 22          then
 23              raise too_many_rows;
 24          end if;
 25          close c;
 26      end loop;
 27  end;
 28  /
Procedure created.


This uses explicit cursors and simply iterates from 1 to 30,000 looking up object names. It must look for the NOTFOUND condition and set the object name to NULL if found (to avoid leaving garbage in the variable it is fetching into). It must look for and raise an error if too many rows are found, indicating logical data-integrity issues.

Now, let's code the same thing using a SELECT INTO.

ops$tkyte@ORA920> create or replace procedure implicit
  2  as
  3      l_object_name t.object_name%type;
  4  begin
  5      for i in 1 .. 30000
  6      loop
  7      begin
  8          select object_name into l_object_name
  9            from t
 10           where object_id = i;
 11      exception
 12          when no_data_found then
 13              l_object_name := null;
 14      end;
 15          end loop;
 16  end;
 17  /
Procedure created.

Notice that there is a lot less code in the second implementation. After running this test with Runstats, we discover the following:

ops$tkyte@ORA920> exec runStats_pkg.rs_start
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec implicit
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_middle
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec explicit
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_stop
Run1 ran in 419 hsecs
Run2 ran in 546 hsecs
run 1 ran in 76.74% of the time
PL/SQL procedure successfully completed.

This shows that the SELECT INTO is more efficient in its processing. It is the all-around winner. It is faster and results in code that is more bulletproof out of the box. I would much rather my code fails at some point with NO_DATA_FOUND or TOO_MANY_ROWS than have it "succeed" in such a case. Here, succeeding would actually be a bug and would either result in my application reporting back incorrect information or not detecting a logical integrity issue that exists in my database.  It is interesting to note that even if we remove all of the "extra" code in the explicit routine - on the supposition that you didn't care if there was no data found, or more then one row to be found, the implicit cursor still performs better.  So, changing the explicit routine to be:

ops$tkyte@ORA920> create or replace procedure explicit
  2  as
  3      l_object_name t.object_name%type;
  4      l_dummy       t.object_name%type;
  5
  6      cursor c( l_object_id in number )
  7      is
  8      select object_name
  9        from t
 10       where object_id = l_object_id;
 11  begin
 12      for i in 1 .. 30000
 13      loop
 14          open c(i);
 15          fetch c into l_object_name;
 16          close c;
 17      end loop;
 18  end;
 19  /

Procedure created.

And re-executing the runstats process, I observed:

ops$tkyte@ORA920> exec runStats_pkg.rs_stop
Run1 ran in 423 hsecs
Run2 ran in 481 hsecs
run 1 ran in 87.94% of the time

So, in every case, the SELECT INTO is faster and easier to code.

 

Partly agree

Tomasz Michniewski, July 28, 2004 - 4:54 am UTC

Well, I agree that SELECT INTO is easier to write, simpler to maintain, more foolproof, shorter for the interpreter, slightly faster, etc, etc.

I agree that SELECT INTO plus ROWNUM = 1 is as fast (or slightly faster) as OPEN/FETCH/CLOSE.

But I don't agree that SELECT INTO without ROWNUM = 1 is faster than OPEN/FETCH/CLOSE. I think that there might be cases when single FETCH is faster. The simples example is a relatively big table where the first and the only record (we know it in advance) is found fast, but reading the whole table takes a while. Then a single FETCH will return rapidly, while SELECT INTO without ROWNUM = 1 will waste time looking for the second record not knowing whether to return a row or the TOO_MANY_RECORDS exception.

Of course I agree that SELECT INTO is easier to write, simpler to maintain, more foolproof, shorter for the interpreter, slightly faster, etc, etc.

Frankly speaking I have never used the OPEN/FETCH/CLOSE construction to read a single row, because I always use SELECT INTO. But in some cases SELECT INTO must have ROWNUM = 1, because otherwise might be much slower.

Tom Kyte
July 28, 2004 - 8:25 am UTC

sure it is -- if you do not agree please (no not please -- rather "i demand") supply an example, a test case, a formal scientific proof.

"I think"
"In my opinion"
"I feel"

I ignore most statements that begin with those clauses (i have a slide I use lots that says that!).


If you do not care about "too many rows" -- you of course add "where rownum=1"

If you do care about "too many rows" (as you do 99.999999999999% of the time) you had better do that check.


I've said clearly that sometimes the select into would have the rownum=1 -- but NOT for performance reasons (never for performance reasons). Only when you want a single random record that meets your predicate.

And you are talking about a full table scan here -- with the open/fetch/close -- there is an equal chance the row you look for is at the head, middle or end of the table -- leading to wildly unpredicable runtimes.


bottom line:

case a: you don't care if there are two rows -- use rownum=1
case b: you do care -- well, nuff said.

Excellent

dharma, August 11, 2004 - 8:05 pm UTC

Hi Tom
In
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4696422878211, <code>
you have told that
declare
cursor c is select * from emp;
begin
for i in c
loop
do somtehting;
end loop;
end;
is almost an implicit cursor(not exactly).
But as quoted by Zoran Martic ( a few reviews above) in 10g, implicit cursors do an array fetch of 100; Would that affect the above programming style
by not being purely implicit.


Also for fetching the first record in an order by desc you have suggested a rownum=1 on the inline view. as in
SELECT *
FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM=1

I have seen elsewhere in the site about using analytic functions. In 9iRel2 SQLtrace doesnt show much difference except for the plans. Which approach do you suggest.

Thanks,
-dharma


Tom Kyte
August 12, 2004 - 8:44 am UTC

ops$tkyte@ORA10G> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
ops$tkyte@ORA10G> @test
ops$tkyte@ORA10G> declare
  2          cursor c is select * from all_objects way1;
  3  begin
  4          for i in c
  5          loop
  6                  null;
  7          end loop;
  8          for x in ( select * from all_objects way2 )
  9          loop
 10                  null;
 11          end loop;
 12  end;
 13  /
 
PL/SQL procedure successfully completed.


********************************************************************************
SELECT * FROM ALL_OBJECTS WAY1
                                                                                                                     
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.08       0.06          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      476      1.71       1.82        888      62107          0       47570
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      478      1.79       1.88        888      62107          0       47570
********************************************************************************
SELECT * FROM ALL_OBJECTS WAY2
                                                                                                                     
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.04          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      476      1.62       1.58        482      61760          0       47570
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      478      1.63       1.62        482      61763          0       47570


They both do array fetches of 100 in 10g -- they are still "the same"




As for the "first row" the rule is:

if you need the first row(s) from a result set -- ROWNUM is best by far.

if you need the first row(s) from each GROUP in a result set -- ROW_NUMBER() is the only game in town.

translated loosely "if you can use rownum, use it, it is more efficient than row_number() is.  use row_number() when rownum won't work, it is more powerful (it can group) but that comes at an obvious price (the grouping!)"
 

Steven Feuerstein vs. Tom

Oleksandr Alesinskyy, August 12, 2004 - 9:13 am UTC

I wish to comment to one old post here - "Steven Feuerstein vs. Tom", June 24, 2003 Reviewer: Jens Kropp from Germany.

I have read a lot of books and articles of Steven Feuerstein ant have to admit that my impression is "author never have written at least one real world application".


Testing existince of row

BB, August 12, 2004 - 10:14 am UTC

Tom,

I did some expirements with finding the fastest way to test the existence of at least one row in PL/SQL. The three variations I tested were:

SELECT COUNT(*)
INTO v_value
FROM DUAL
WHERE EXISTS( SELECT 1 FROM EMP e WHERE e.EMP_NAME = 'Tom' AND ROWNUM = 1 );

SELECT DECODE( COUNT(*), 0, 0, 1 )
INTO v_value
FROM EMP e
WHERE e.EMP_NAME = 'Tom'
AND ROWNUM = 1;

BEGIN
SELECT 1
INTO v_value
FROM EMP e
WHERE e.EMP_NAME = 'Tom'
AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_value := 0;
END;

CURSOR cur IS SELECT 1 FROM EMP e WHERE e.EMP_NAME = 'Tom' AND ROWNUM = 1; -- The declaration for the cursor is not included in the timing here.
OPEN cur;
FETCH cur INTO v_value;
IF cur%FOUND THEN
v_value := 1;
ELSE
v_value := 0;
END IF;
CLOSE cur;

The "SELECT e.EMP_NAME FROM EMP e" can be replaced by any query. I've found that the "AND ROWNUM = 1" is never detrimental when testing for at least one row.

Every test I've done has shown that the existence method works fastest. Since a test for existince is something we do frequently in PL/SQL, is there an even better way to do this?

Tom Kyte
August 12, 2004 - 10:39 am UTC

the best way to test for existence is not to test for existence at all!!!!

I've always been curious about this, why:


look for row
do something because row exists


instead of

do something as if row exists



*I cannot recall EVER testing for existence in my plsql code* So i beg to differ with the "something we do frequently"

what if the Client expects different Results depending on the Test for Existence

pasko, August 12, 2004 - 10:59 am UTC

Hi Tom,

regarding Test for Existence,
if a Java/C++ Client expects different results depending on a stored procedure Existence-Check,
i guess we will have no choice but to first select the row out .


Regards,




Tom Kyte
August 12, 2004 - 11:00 am UTC

give me a "for example"

Branching logic based on row existence

BB, August 27, 2004 - 7:25 pm UTC

I'm curious as to how you'd handle the following pseudo logic:

Do something when row exists
Do something else when row does not exist

A simple example of this is an upsert:
Update the row
If no update occurred insert the values

A slightly more complex example is something like
Grab price from users prices
If no user price, grab price from bid prices
If no bid price, grab price from system prices
If no system price, grab default price
If no default price, error

An more complex example would include business logic after each failure in the process.

Something like this could be done with a single query, but 90% of the time (or more) the user price exists. Thus a single query seems inefficient.

On that same note, is there a way to do something like:

SELECT val
FROM QUERY1
ELSE
SELECT val
FROM QUERY2
ELSE
SELECT val
FROM QUERY3
etc

In such a way that query 1 gets evaluated. If it returns a value, short circuit, if it doesn't, execute the next query.

Thanks for all the time you put into answering all the questions on the site. Best database information I've ever seen, because its mostly real world uses with theory to back it up every once in a while (as opposed to the way academia does things).

Tom Kyte
August 27, 2004 - 7:36 pm UTC

using your simple example:

update t set ... where ...
if ( sql%rowcount = 0 )
then
insert the darn thing
end if;

for the slightly more complex example -- seems to be a "datamodel issue" but you would just try to QUERY for it??


for the last part -- sure, it is procedural code

begin
select ... into ... from ...
exception
when no_data_found then
begin
select ... into ... from ...
exception
when no_data_fount then ...



One more follow up

Bob, August 30, 2004 - 12:29 pm UTC

Would you advise using exception handling to branch business logic? This seems a little cumbersome looking and I've seen various sources advise against it (Steven Feurstein for PL/SQL)

Tom Kyte
August 30, 2004 - 12:42 pm UTC

what kind of "business logic"


give me a "for example"

One more follow up

Bob, August 30, 2004 - 12:31 pm UTC

Would you advise using exception handling to branch business logic? This seems a little cumbersome looking and I've seen various sources advise against it (Steven Feurstein for PL/SQL)

(The data model was messed up because it was coming from my head. This is why we always write designs down where I work =))

Prefer this the other way round

Gary, September 02, 2004 - 7:56 pm UTC

Tom,
Just a comment on your update/insert statement :
  update t set ... where ...
  if ( sql%rowcount = 0 )
  then
     insert the darn thing
  end if;

As a rule of thumb (though I know you don't like ROT), I find :
  begin
   insert
  exception
    when dup_val_on_index then update...
  end;
slightly more robust for concurrency.

create table sales 
  (prod_id number(2) constraint sales_pk primary key, 
   qty number(3));

user (a) does (with no commit) :
insert into sales values (20,5);

user(b) comes along
If they do an update, it will return no rows.
SQL> update sales set qty=qty+5
  2  where prod_id=20;

0 rows updated.

SQL> insert into sales values(20,5);
.......waits for user(a) to commit.......
insert into sales values(20,5)
*
ERROR at line 1:
ORA-00001: unique constraint (xxxx.SALES_PK) violated

[And it will be one of those 'mostly it works but occasionally it fails, difficult to reproduce bugs']

The alternative of trying insert, then update will mean the insert will wait for the commit, then raise the exception, allowing the update to work correctly.
 

Tom Kyte
September 03, 2004 - 9:44 am UTC

it is much worse for performance however (in general, unless the row is almost *never* existing in the table in the first place).

And I don't see how it is more concurrent. We have to assume the update is via primary key (unique, primary key in place).

the "update if insert" block is something that should take on the order of 0.01 in general


I'll stick with "update if 0 insert" as it NEVER should be (thinking of the practical use here -- a program implementing a MERGE -- it is the only time I can think of this being *MEANINGFUL* in a program) the case that you have your above situation.

If you do, we need to talk about your application design and how the transactions actually happen.

Implicit cursor in 9i

Chris, September 13, 2004 - 9:58 am UTC

Tom,
I did not see any info in 9i changes relating to properties of implicit cursors but it appears that "count" is no longer a valid property. The following works in 8i:

begin
for c in (select 'x' from dual) loop
dbms_output.put_line(c.count);
end loop;
end;
/

But this produces an error in 9i:

PLS-00302: component 'COUNT' must be declared

Is there a paper on changes that you could point me to that would include this, or at a minimum, what is the syntax for this in 9i.

Tom Kyte
September 13, 2004 - 11:38 am UTC

In your example, C is a record -- and the records never had any "implict attributes".

But that aside, in 8i or whatever -- count was not "valid" as an attribute of an implicit select cursors?



ops$tkyte@ORA817DEV> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
 
ops$tkyte@ORA817DEV> set echo on
ops$tkyte@ORA817DEV> @test
ops$tkyte@ORA817DEV> begin
  2     for c in (select 'x' from dual) loop
  3        dbms_output.put_line(c.count);
  4     end loop;
  5  end;
  6  /
      dbms_output.put_line(c.count);
                             *
ERROR at line 3:
ORA-06550: line 3, column 30:
PLS-00302: component 'COUNT' must be declared
ORA-06550: line 3, column 7:
PL/SQL: Statement ignored
 
 
ops$tkyte@ORA817DEV> begin
  2     for c in (select 'x' from dual) loop
  3        dbms_output.put_line(sql%count);
  4     end loop;
  5  end;
  6  /
      dbms_output.put_line(sql%count);
                               *
ERROR at line 3:
ORA-06550: line 3, column 32:
PLS-00207: identifier 'COUNT', applied to implicit cursor SQL, is not a legal cursor attribute
ORA-06550: line 3, column 7:
PL/SQL: Statement ignored
 
<b>this code isn't valid in any release</b>

 

Followup on implicit cursor

Chris, September 13, 2004 - 10:05 am UTC

Sorry Tom,
Fired the last email a little too quick. What works in 8i is:

begin
for c in (select count(*) from dual) loop
dbms_output.put_line(c.count);
end loop;
end;
/

but not in 9i. Obviously 8i creates an alias of count for the count(*), while 9i does not. That's just poorly written code to begin with, unfortunately there is a lot of it in the app I'm maintaining.

Tom Kyte
September 13, 2004 - 11:40 am UTC

Ahh, now I see -- yes, it should never have worked in the first place. You'll have to contact support to see if there is some event you can set to get the old behaviour whilst correcting the code.

Existence with implicit cursors

Kevin, January 15, 2005 - 12:29 am UTC

I think I have one of those examples you asked for regarding a case in which I need to test for existence within a cursor. Basically, documents may have zero or more control numbers -- if they match, then I want to note that; if neither document has a control number, I want to note that too (essentially to weigh that higher than non-matching control numbers)

The existing code is:
for x in (select documentid from doccontrolnum where docid = iTargetid)
loop
for y in (select documentid from doccontrolnum where docid = iCandidateID)
loop
if (x.documentid = y.documentid) then
iScore = iScore + 100;
end if;
end loop;
end loop;

While I can certainly brute-force a separate pair of queries to check for no rows, I'm hoping for some clever way to do it with what I have.


Tom Kyte
January 15, 2005 - 9:41 am UTC

I see no test for existence?


I do however see "a join", one that you are not letting the database do, but rather are trying to do yourself.


select x.*, count(y.documentid) * 100
from (X), (Y)
where x.documentid = y.documentid(+)
group by x.documentid
/


or if you need to preserve the y rows


select x.*, y.*, (count(y.documentid) over (partition by x.documentid)) * 100
from (X), (Y)
where x.documentid = y.documentid(+)
/




That is why I ask these questions...

Kevin, January 15, 2005 - 11:01 am UTC

I don't have a test for existence (yet) -- my brute force approach would be:

select count(*) into iTargetid from doccontrolnum where docid = iTargetID;
if iTargetID = 0 then
select count(*) into iCandidateId from doccontrolnum where docid = iCandidateID;
if iCandidateid = 0 then
iScore := iScore + 50;
end if;
else
select nvl(sum(Points),0) into iPoints from
(select documentid, count(*) * 100 Points
from (select docid, documentid from amsdoccontrolnum where docid = iSourceid)
inner join (select docid, documentid from amsdoccontrolnum where docid = iTargetID)
using (documentid) group by documentid);
iScore := iScore + iPoints;
end if;

That means I'm selecting from the table with twice. Can I get it down to one?



Tom Kyte
January 15, 2005 - 4:04 pm UTC

why -- just query?!?


just query -- why do you feel the need to peek first? query, see what you get and do what you want afterward.

Still Confused.

Kevin, January 15, 2005 - 10:38 pm UTC

I'm still not getting it, or not explaining myself very well. Here's the business case: documents may have zero to N control numbers, which are stored in the doccontrolnum table. If two documents have control numbers that are different, it is highly unlikely that they are duplicates. If two documents have the same control number, it is very likely that they are duplicates, so make sure they are retained for manual review. However, if the two documents have no control numbers at all, that is a weak but potentially important correlation. Give that correlation more weight than the different control numbers, but less than the non-null matches.
So, in my code, I'm trying to accumulate points toward a score that I will show to a user if it's significant enough, using the following:
Exact match, non-null control number -- 100 pts./match
Match, both docs have no control numbers -- 50 pts.
No Match -- 0 pts.

So I'm 'peeking' to determine if both have no rows. If there's some way to divine that from the query (not that there is no match, but that they both have no rows), then hit me over the head with it, because it's not sinking in.

Tom Kyte
January 16, 2005 - 10:56 am UTC

(strange, I'm probably sitting mere miles from you in Leesburg VA...)


Anyway, just full outer join TGT to CAN by documentid.

every row that has tgt_documentid = can_documentid -- you get 100 points.

rows where tgt_documentid is null and can_documentid is not null (and vice versa), you get zero points.

SUM those scores -- take the NVL() of that sum to be 50 (no records in either table!)

Like this:


ops$tkyte@ORA9IR2> create table doccontrolnum ( docid number, documentid number );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> -- perfect hit
ops$tkyte@ORA9IR2> insert into doccontrolnum values ( 1, 10 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into doccontrolnum values ( 2, 10 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> -- total miss
ops$tkyte@ORA9IR2> insert into doccontrolnum values ( 3, 100 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into doccontrolnum values ( 4, 101 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into doccontrolnum values ( 5, 1000 );
 
1 row created.
 
ops$tkyte@ORA9IR2> -- 6 is missing
ops$tkyte@ORA9IR2>



ops$tkyte@ORA9IR2> -- 7 is missing
ops$tkyte@ORA9IR2> insert into doccontrolnum values ( 8, 10000 );
 
1 row created.
 
ops$tkyte@ORA9IR2>



ops$tkyte@ORA9IR2> -- 9/10 missing
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable iTargetId number
ops$tkyte@ORA9IR2> variable iCandidateId number
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :iTargetid := 1; :iCandidateid := 2;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select nvl( sum( case when tgt_documentid = can_documentid
  2                  then 100
  3                  when tgt_documentid is null and can_documentid is not null
  4                  then 0
  5                  when tgt_documentid is not null and can_documentid is null
  6                  then 0
  7               end ), 50 ) score
  8    from (
  9  select tgt.documentid tgt_documentid,
 10         can.documentid can_documentid
 11    from (select * from doccontrolnum where docid = :iTargetID ) tgt full outer join
 12         (select * from doccontrolnum where docid = :iCandidateID ) can on (tgt.documentid = can.documentid)
 13         ) x
 14  /
 
     SCORE
----------
       100
 
ops$tkyte@ORA9IR2> exec :iTargetid := 3; :iCandidateid := 4;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> /
 
     SCORE
----------
         0
 
ops$tkyte@ORA9IR2> exec :iTargetid := 5; :iCandidateid := 6;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> /
 
     SCORE
----------
         0
 
ops$tkyte@ORA9IR2> exec :iTargetid := 7; :iCandidateid := 8;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> /
 
     SCORE
----------
         0
 
ops$tkyte@ORA9IR2> exec :iTargetid := 9; :iCandidateid := 10;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> /
 
     SCORE
----------
        50
 
ops$tkyte@ORA9IR2>

 

Question for Kevin.....

Mark J. Bobak, January 16, 2005 - 8:19 am UTC

What if one doc has a control number but the other doesn't?
Is that 50 points? 0 points? Something else?

-Mark

You nailed it, as usual.

kevin, January 17, 2005 - 12:15 am UTC

(Must be the other side of Leesburg -- Osmosis clearly doesn't work over more than a mile).
That type of answer was tantalizingly close, but I could not get my head around it. Many thanks.

To Mark -- 0 points. These are historical documents, almost always stamped with some kind of marker -- numeric, correspondence tracking, etc. -- if a document wasn't marked at all, that makes it 'interesting'.

Tom Kyte
January 17, 2005 - 7:56 am UTC

just to walk through it

a) the inline views on lines 11/12 should be self evident. all of the docControlNum records for targetID and candidateID repectively.

b) a full outer join makes sure we get ALL records from BOTH sides of the join -- if TGT does not have a match in CAN, it'll make up a NULL record for it. And vice versa. Any records that join naturally, they will be there as well. This way -- the only way to get ZERO records is if neither TGT or CAN had any records matching those ID's.

So, we full outer join TGT to CAN by documentid

c) the code on 1..7 does two things.
1) the case assigns:
100 for a HIT
0 for a total MISS (only one documentid, means only one "table" has it)

2) the sum counts up the 100's and 0's

d) if we get a sum of NULL -- that means there were ZERO records in both TGT and CAN -- so, the nvl(sum(),50) gives you that score of 50 when neither table produced any rows. Else if the sum() was not null -- there is your score.



Hate to be a pain, but...

kevin, January 17, 2005 - 11:03 pm UTC

The above SQL works perfectly [with one added join condition], passed my test data with flying colors -- until I put it inside of a package function. Then I get a variety of odd ORA-09XX errors (0905, 0920). I can cut & paste the command into sqlplus and it works as expected. Is there any part of this that might break in PL/SQL? Here's the function:

Function CalcControlNumScore (iTargetID in number, iCandidateid in number)
return number as
iPoints number := 0;
begin
select nvl( sum( case
when tgt_documentid = can_documentid then 100
when tgt_documentid is null and can_documentid is not null then 0
when tgt_documentid is not null and can_documentid is null then 0
end ), 50 ) score into :iPoints
from ( select tgt.documentid tgt_documentid, can.documentid can_documentid
from (select * from doccontrolnum inner join controlsystem using (controlsystemid) where controlsystem = 'AGENCY' and docid = :iTargetID ) tgt
full outer join
(select * from doccontrolnum inner join controlsystem using (controlsystemid) where controlsystem = 'AGENCY' and docid = :iCandidateID ) can
on (tgt.documentid = can.documentid) ) x;

return iPoints;
end;

Tom Kyte
January 18, 2005 - 9:42 am UTC

remove the binds!!  you use host variables (plsql variables) in plsql for binding...




ops$tkyte@ORA9IR2> create or replace function compute_score( l_targetid number, l_candidateid number ) return number
  2  as
  3          l_return number;
  4  begin
  5  select nvl( sum( case when tgt_documentid = can_documentid
  6                  then 100
  7                  when tgt_documentid is null and can_documentid is not null
  8                  then 0
  9                  when tgt_documentid is not null and can_documentid is null
 10                  then 0
 11               end ), 50 ) score
 12    into l_return
 13    from (
 14  select tgt.documentid tgt_documentid,
 15         can.documentid can_documentid
 16    from (select * from doccontrolnum where docid = l_TargetID ) tgt full outer join
 17         (select * from doccontrolnum where docid = l_CandidateID ) can on (tgt.documentid = can.documentid)
 18         ) x;
 19
 20
 21  return l_return;
 22  end;
 23  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_output.put_line( compute_score( 1, 2 ) );
100
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_output.put_line( compute_score( 3, 4 ) );
0
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_output.put_line( compute_score( 5, 6 ) );
0
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_output.put_line( compute_score( 7, 8 ) );
0
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_output.put_line( compute_score( 9, 10) );
50
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
 

Variety of errors?

Mark J. Bobak, January 18, 2005 - 4:01 am UTC

First thing I'd suspect is the ANSI join syntax.
Try re-writing it into conventional Oracle join syntax.
There have been several bugs surrounding ANSI join syntax.

-Mark

PS That's a total guess, I don't have any first hand
knowledge of a specific bug in your situation.

Tom Kyte
January 18, 2005 - 9:59 am UTC

It is the bind variables in the query -- you don't create plsql with binds in that manner :)

Not the binds

kevin, January 18, 2005 - 10:34 pm UTC

Tom,
I solemnly swear that the binds you see in my code were a copy/paste error -- I was swapping between Enterprise Mgr and sqlplus while testing this.
I was able to resolve the problem this morning by rewriting the query as follows:
Function CalcControlNumScore (iTargetID in number, iCandidateid in number)
return number as
iPoints number := 0;
begin
select nvl( sum( case
when tgt_documentid = can_documentid then 100
when tgt_documentid is null and can_documentid is not null then 0
when tgt_documentid is not null and can_documentid is null then 0
end ), 50 ) score into iPoints
from ( select tgt.documentid tgt_documentid, can.documentid can_documentid
from (select documentid from doccontrolnum where controlsystemid = (select controlsystemid from controlsystem where controlsystem = 'AGENCY') and docid = iTargetID ) tgt
full outer join
(select documentid from doccontrolnum where controlsystemid = (select controlsystemid from controlsystem where controlsystem = 'AGENCY') docid = iCandidateID ) can
on (tgt.documentid = can.documentid) ) x;

return iPoints;
end;

So that does point toward the join syntax as a possible problem. The thing that throws me is that the same syntax works in sqlplus -- and I thought that Oracle9i features the grand unification of parsers ???

Now, if I could just get those couple of hours back...



Tom Kyte
January 19, 2005 - 10:21 am UTC

version, I swear to you the above was a verbaitim cut and paste from my 9ir2 system.

Is the 100 row "prefetch" documented?

Steve, June 06, 2005 - 4:13 pm UTC

Hi Tom,

In response to the follow-up

10g implicit cursors February 06, 2004
Reviewer: Zoran Martic from Dublin, Ireland

Is this feature documented anywhere? I filed a TAR with
Oracle Support about this issue (the 100 row "prefetch"
isn't happening all the time for me), and the support
person came back and said they knew nothing about this feature. Can you point me to something "official" about
this feature?

Thanks for your time.

Tom Kyte
June 06, 2005 - 5:51 pm UTC

no, it like many optimizations are just there. It is like the fact that in 9i:


for i in 1 .. 100
loop
execute immediate 'insert into ' || p_tname || ' values ( :x )' using i;
end loop;

would have parsed the statement 100 times but 10g will only parse it once. There are hundreds of optimizations that are not documented. They need not be, they are not "features" just language optimizations.

Are explicit cursors converted to implicit cursors

Thirumaran, July 01, 2005 - 12:20 am UTC


Hi TOM,

In oracle are explicit cursors converted to implicit cursors by the oracle Db engine under any circumstances.

Please illustrate with some example.

Thanks
Thirumaran



Tom Kyte
July 01, 2005 - 9:43 am UTC

explicit and implicit are purely "programming constructs and terminology"

Explicit cursors in plsql are those that YOU the programmer are responsible for opening, fetch, setting up host variables for, closing.

Implicit cursors in plsql are those that PLSQL the runtime engine is responsible for opening, fetching into host variables it typically sets up, and closing.


implicit:

begin
  for x in ( select * from t )
  loop
     process x;
   end loop;
end;

Explicit:

declare
   cursor c is select * from t;
   l_rec c%rowtype;
begin
   open c;
   loop
      fetch c into l_rec;
      exit when (c%notfound);
      process l_rec;
   end loop;
   close c;
end;
/


Other than the fact you write lots more code, code that has the potential for many more bugs due to an oversight by you the programmer -- to the database, these pieces of code are identical.

Well, until 10g when the first becomes much more efficient than the second due to PLSQL making implicit cursors even more performant than they used to be!


The Oracle database engine has no concept of "implicit" vs "explicit", it is purely a LANGUAGE thing, implict being easier to code with.


ops$tkyte@ORA10G> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
ops$tkyte@ORA10G> @test
ops$tkyte@ORA10G> begin
  2    for x in ( select * from all_objects implicit )
  3    loop
  4       null; -- process x;
  5     end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> declare
  2     cursor c is select * from all_objects explicit;
  3     l_rec c%rowtype;
  4  begin
  5     open c;
  6     loop
  7        fetch c into l_rec;
  8        exit when (c%notfound);
  9        null; -- process l_rec;
 10     end loop;
 11     close c;
 12  end;
 13  /
 
PL/SQL procedure successfully completed.


SELECT * FROM ALL_OBJECTS IMPLICIT
                                                                                                                       
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      487      1.78       1.79          0      63769          0       48665
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      489      1.80       1.81          0      63769          0       48665
                                                                                                                       
SELECT * FROM ALL_OBJECTS EXPLICIT
                                                                                                                       
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    48666      2.32       2.40          0     113210          0       48665
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    48668      2.33       2.44          0     113210          0       48665
 

Another release issue??

A reader, September 14, 2005 - 10:34 am UTC

Tom,

Back on March 24, 2004 in this thread, (search for "6502"), you responded to a users query about a potential bug or as you put it: "product issue" ;)

In short, it was determined that Oracle 91R1 would generate the error, but Oracle 91R2 would not ..

I have a very similar situation/query as this issue, but with Oracle 8 vs Oracle 10 .. and need some help understanding what is happening ...

Here's my situation:

Oracle v8.1.7.4.0
=================
drop table junk;
create table junk
( cola varchar2(4) );

declare
l_num number := 123123123;

CURSOR c_cursor ( a_parm IN junk.cola%TYPE
) IS
SELECT 1
FROM dual
WHERE a_parm = a_parm
or a_parm is null;

BEGIN

FOR c_loop in c_cursor(l_num ) LOOP
NULL;
END LOOP;

end;
/

And this "appears" to run fine ... (it hurts my brain, as I know it "shouldn't" .. but it seems to .. )

Table dropped.


Table created.


PL/SQL procedure successfully completed.

However, trying this on Oracle 10.1.0.4.0
I get this:

Table dropped.


Table created.

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 13

================

More than anything else, I'm actually very concerned that the code that actually runs in Oracle 8 may not be providing the correct result .. (this is an overly simplified example of a larger query I have that's generating this error as we move to Oracle 10).

I'm very concerned that this code (which is obviously "wrong" - ie assigning a large number into a small varchar2 .. *sigh* ) is running, but silently generating incorrect results.

So I tried to display what the Oracle 8 was generating ... I tried to display the value of the cursor parameter "a_parm" ...

I changed the cursor to this:

CURSOR c_cursor ( a_parm IN junk.cola%TYPE
) IS
SELECT a_parm
FROM dual
WHERE a_parm = a_parm
or a_parm is null;

However, when I ran that on our Oracle 8 ... I then get the numeric value error ...

Tom, please, I know the code is bad .. I've already changed it .. but what I need to know is something I can put in front of my managers with a definite: "No, that data's fine, Oracle 8 converted things just fine" ... or "Yep, the data's all screwed, because Oracle 8 truncated the numeric when it went into the varchar

How can I figure that out??


Tom Kyte
September 14, 2005 - 11:14 am UTC

ops$tkyte@ORA817DEV> declare
  2    l_num   number   := 123123123;
  3
  4     CURSOR c_cursor ( a_parm IN junk.cola%TYPE
  5                              ) IS
  6     SELECT '"' || a_parm || '"' data
  7     FROM    dual
  8     WHERE   a_parm = a_parm
  9        or a_parm is null;
 10
 11  BEGIN
 12
 13     FOR c_loop in c_cursor(l_num ) LOOP
 14          dbms_output.put_line( c_loop.data );
 15     END LOOP;
 16
 17  end;
 18  /
"123123123"
 
PL/SQL procedure successfully completed.


It seems to be "ok" - applying the function to it (||) removed the varchar2(4) limit.
 

implicit curosr loop

raaghid, October 24, 2005 - 9:52 am UTC

very useful tips about implicit cursor.
I have also used implicit cursor and now pl confirm whether my way of doing is efficient.
Module -- HR-Payroll process
In PL/SQL pkg---procedure

for c1 in (sql query employee master-- all employees)
loop
for c2 in (SQL query of salary monthly variables (basic, hra etc ) PER EMPLOYEE <<<<ie emp_no = c1.emp_no>>>> )
loop
---- coding ----
end loop
for c3 in (SQL query of yearly perks - PER EMPLOYEE ie emp_no = c1.emp_no)
--- coding ----
loop
end loop
---- coding -----
end loop1
---- same approach i am using for find leave taken, tax calc etc... --------
I am scanning through employee master and then for EACH EMPLOYEE scanning through monthly parameters (amount) and yearly perks for salary processing. Is this correct way of doing.

Please confirm.

Tom Kyte
October 24, 2005 - 11:45 am UTC

Why not just join? when I see queries in loops of queries - I think "just join"

thanks -- other table may or may not have entry

raaghid, October 26, 2005 - 3:52 am UTC

Tom, thanks for your reply.

I have done this way for the following reasons.

1- Since the other table (leave details etc) may not have entry --- so we are forced to use outer join, which will slow down the process.

2- And also the first query will be the combination of so many tables, hence it will become complicated also..

Please share your further views


Tom Kyte
October 26, 2005 - 11:50 am UTC

1) it'll be faster than doing it yourself.

2) so? your code is complicated too? comments make things understandable.


JUST JOIN

that is my only, and very much consistent, view of the world.

a comment

A reader, May 16, 2006 - 10:45 am UTC

Tom,

I agreed with you that implicit cursor are faster. You have proved it. however, in all your example I see that you are selecting into.. using one value and also selecting from one table. My observation is that you will create a view? here I am putting a perfect example...in this
case pvisit and pref_id..

CURSOR tg_cur(pvisit gate_visit.visit%TYPE
,pref_id gate_containers.reference_id%TYPE
)
IS
SELECT gc.action, gc.booking, gv.coop_in_date, gc.ssl_user_code, s.long_description
FROM gate_containers gc
,gate_visit gv
,ssl@tripoli s
WHERE gc.visit = pvisit
AND gc.reference_id = pref_id
AND gc.visit = gv.visit
AND gc.ssl_user_code = s.code(+);

Tom Kyte
May 16, 2006 - 11:00 am UTC

select c1, c2, ..... CN
into host_var1, host_var2, ..... host_varN
from table1, table2, table3, .... tableM
where ....


you can have as many columns as you like retrieved from as many tables as you like.

Cursor Binding

Mikey, October 28, 2007 - 6:33 pm UTC

I have been told a number of times by a DBA where I work, that a major advantage of explicit cursors over implicit is that explicit cursors bind themselves into the shared memory. So the select in the cursor does not need to be re-parsed, but rather replace the value and just execute.

This has been a debate over this for a while now. Yes, the implicit cursors do run faster, but when the procedure is called by a number of users hundreds of times a day, he believes that it is actually faster with explicit than implicit.

Is all this true?

RE: implicit cursors don't bind? Bet me...

Duke Ganote, March 12, 2008 - 4:42 pm UTC

Mikey-- Hmm, sounds like time to "question authority" (and I don't mean just "ask Tom" :).

Both implicit and explicit cursors are in the shared pool. And they'll even share with each other.

Here's what I did. You should be able to try it with minimal developer privileges. Well, except flushing the shared pool, which I could because I have DBA privileges on my database. You could just check ahead of time the shared SQL doesn't exist or change the name of the table to something unique.

1. Created a table.
2. Created two procedures. One used an implicit cursor, the other an explicit.
3. Flushed the shared pool of SQL.
4. Called both procs repeatedly from a couple of sessions, starting with the implicit (allegedly "unsharable" proc), then the explicit.
5. Saw how many pieces of SQL exist in the shared pool.

ALTER SYSTEM FLUSH SHARED_POOL
/
CREATE table departmentalize as select 1 as deptno from dual
/
create or replace
PROCEDURE implicit_count IS
BEGIN
FOR rec IN ( select count(*) cnt from departmentalize ) LOOP
DBMS_OUTPUT.PUT_LINE(rec.cnt);
END LOOP;
END implicit_count;
/
create or replace
PROCEDURE explicit_count IS
cursor c IS ( select count(*) cnt from departmentalize );
l_cnt number;
BEGIN
open c;
fetch c INTO l_cnt;
close c;
DBMS_OUTPUT.PUT_LINE(l_cnt);
END explicit_count;
/
exec IMplicit_count
exec IMplicit_count
exec IMplicit_count
exec IMplicit_count
exec EXplicit_count
exec EXplicit_count
exec EXplicit_count
exec EXplicit_count

OK, let's see the results:

select sql_text
from v$sql
where lower(sql_text) like '%departmentalize%'
and upper(sql_text) not like '%V$SQL%'
/
ROWNUM SQL_TEXT
---------- ---------------------------------------
1 SELECT COUNT(*) CNT FROM DEPARTMENTALIZE
2 ( SELECT COUNT(*) CNT FROM DEPARTMENTALIZE )

I even started another session (same userID), and reran the procs.

Same result from the shared pool in the original session:

select sql_text
from v$sql
where lower(sql_text) like '%departmentalize%'
and upper(sql_text) not like '%V$SQL%'
/
ROWNUM SQL_TEXT
---------- ---------------------------------------
1 SELECT COUNT(*) CNT FROM DEPARTMENTALIZE
2 ( SELECT COUNT(*) CNT FROM DEPARTMENTALIZE )

RE: implicit and explicit will "even share with each other"

Duke Ganote, March 12, 2008 - 10:16 pm UTC

I didn't show that implicit and explicit cursors "even share with each other" in the first example, but it's only a minor tweak in the procs to make the SQL match exactly, and thus be shared:

ALTER SYSTEM FLUSH SHARED_POOL
/
CREATE OR REPLACE PROCEDURE explicit_count IS
cursor c IS ( select count(*) cnt from departmentalize ) ;
l_cnt number;
BEGIN
open c;
fetch c INTO l_cnt;
close c;
DBMS_OUTPUT.PUT_LINE(l_cnt);
END explicit_count;
/
CREATE OR REPLACE PROCEDURE implicit_count IS
BEGIN
FOR rec IN ( ( select count(*) cnt from departmentalize ) ) LOOP
DBMS_OUTPUT.PUT_LINE(rec.cnt);
END LOOP;
END implicit_count;
/
exec IMplicit_count

exec EXplicit_count

select sql_text
from v$sql
where lower(sql_text) like '%departmentalize%'
and upper(sql_text) not like '%V$SQL%'
and upper(sql_text) not like '%PARALLEL%'
/
SQL_TEXT
---------------------------------------------
( SELECT COUNT(*) CNT FROM DEPARTMENTALIZE )

RE: shared pool for explicit and implicit cursor

Duke Ganote, March 13, 2008 - 6:55 am UTC

I double checked meaning of the contents of v$sql (shared pool)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:385254862843
and looks like my memory didn't fail me (yesterday at least).

Tom Kyte
March 13, 2008 - 8:48 am UTC

funny thing is - implicit and explicit are purely PLSQL / Pro*C concepts - they are programming language concepts.

All SQL in Oracle is dynamic sql - all of it looks the same to Oracle, all of it.

in the server there is no difference between the two, there is no distinction, there is no way to tell the difference.

funny no match

Andras Gabor, March 13, 2008 - 10:31 am UTC

Hi,

Brackets are there because you added: them to the explicit part.
PROCEDURE explicit_count IS
   cursor c IS ( select count(*) cnt from departmentalize );
   l_cnt number;
 BEGIN

instead of:
PROCEDURE explicit_count IS
   cursor c IS select count(*) cnt from departmentalize;
   l_cnt number;
 BEGIN

Cursor declarations need brackets for the parameters only, not after the IS keyword.
I haven't checked your example, but I am pretty sure about this :)

RE: unmatched SQL

Duke Ganote, March 13, 2008 - 11:38 am UTC

Andras-- You're right, I could've tweaked the procs like you suggested (script below) to get the same parsed-out SQL without parentheses. However, the intended point was: implicit and explicit cursors can share the same SQL -- despite any allegations by Mikey's DBA here
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1205168148688#515175200346173986

create or replace
PROCEDURE implicit_count IS
BEGIN
FOR rec IN ( select count(*) cnt from departmentalize ) LOOP
DBMS_OUTPUT.PUT_LINE(rec.cnt);
END LOOP;
END implicit_count;
/
create or replace
PROCEDURE explicit_count IS
cursor c IS select count(*) cnt from departmentalize ;
l_cnt number;
BEGIN
open c;
fetch c INTO l_cnt;
close c;
DBMS_OUTPUT.PUT_LINE(l_cnt);
END explicit_count;
/
exec implicit_count

exec explicit_count

select sql_text
from v$sql
where lower(sql_text) like '%departmentalize%'
and upper(sql_text) not like '%V$SQL%'
and upper(sql_text) not like '%PARALLEL%'
/
SQL_TEXT
----------------------------------------------------
SELECT COUNT(*) CNT FROM DEPARTMENTALIZE


implicit cursor

A reader, March 13, 2008 - 10:39 pm UTC

excellent explanation by the best in the business

AH HA! book says: "Explicit and implicit cursors do not match up in the shared pool"

Duke Ganote, March 15, 2008 - 12:12 pm UTC

Perhaps Mikey's DBA read this statement:

"Explicit and implicit cursors do not match up in the shared pool."
-- p. 98 of "Oracle PL/SQL for DBAs" Arup Nanda, Steven Feuerstein

The book's example is roughly like this (modified to follow my previous example). A single proc with both implicit SELECT INTO and explicit cursors:

ALTER SYSTEM FLUSH SHARED_POOL
/
DECLARE
cursor c IS
select count(*) cnt from departmentalize;
l_cnt number;
BEGIN
open c;
fetch c INTO l_cnt;
close c;
select count(*) cnt INTO l_cnt from departmentalize;
END;
/
select '"'||sql_text||'"'
from v$sql
where lower(sql_text) like '%departmentalize%'
and upper(sql_text) not like '%V$SQL%'
and upper(sql_text) not like '%PARALLEL%'
and upper(sql_text) not like '%CURSOR%'
/
'"'||SQL_TEXT||'"'
------------------------------------------
"SELECT COUNT(*) CNT FROM DEPARTMENTALIZE"
"SELECT COUNT(*) CNT FROM DEPARTMENTALIZE"

As far as I can discern, the SQL is identical. Yet, sure enough, the SELECT INTO cursor isn't shared with the explicit cursor.

I modified the single proc to use an implicit FOR LOOP, which curiously is shared with the explicit cursor:

ALTER SYSTEM FLUSH SHARED_POOL
/
DECLARE
cursor c IS
select count(*) cnt from departmentalize;
l_cnt number;
BEGIN
open c;
fetch c INTO l_cnt;
close c;
FOR REC IN ( select count(*) cnt from departmentalize) LOOP
l_cnt := rec.cnt;
END LOOP;
END;
/
select '"'||sql_text||'"'
from v$sql
where lower(sql_text) like '%departmentalize%'
and upper(sql_text) not like '%V$SQL%'
and upper(sql_text) not like '%PARALLEL%'
and upper(sql_text) not like '%CURSOR%'
/
'"'||SQL_TEXT||'"'
------------------------------------------
"SELECT COUNT(*) CNT FROM DEPARTMENTALIZE"

This book, to its credit, shows a reproducible test case and is written by a couple of Oracle ACEs.

It may be the source then of related statements on the web, e.g.
"If you use an explicit cursor, you are more likely (or at least able) to reuse that cursor, which increases the chance that it will be pre-parsed in shared memory when needed."

http://www.bhatipoglu.com/entry/27/implicit-vs-explicit-cursors-performance-analysis
Tom Kyte
March 15, 2008 - 2:14 pm UTC

I fail to see how the comment:

...
"If you use an explicit cursor, you are more likely (or at least able) to
reuse that cursor, which increases the chance that it will be pre-parsed in
shared memory when needed."
......

follows from the observation?? Especially in light of:

ops$tkyte%ORA10GR2> create table departmentalize ( x int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

ops$tkyte%ORA10GR2> DECLARE
  2     cursor c IS
  3     select count(*) cnt from departmentalize;
  4     l_cnt number;
  5  BEGIN
  6     open c;
  7     fetch c INTO l_cnt;
  8     close c;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> DECLARE
  2     l_cnt number;
  3  begin
  4     select count(*) cnt INTO l_cnt from departmentalize;
  5  END;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> DECLARE
  2     l_cnt2 number;
  3  begin
  4          if ( 1=1 ) then
  5             select count(*) cnt
  6               INTO l_cnt2
  7                   from departmentalize;
  8          end if;
  9  END;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> SELECT COUNT(*) CNT FROM DEPARTMENTALIZE;

       CNT
----------
         0

ops$tkyte%ORA10GR2> column sql_id new_val S
ops$tkyte%ORA10GR2> select sql_id, executions, '"'||sql_text||'"'
  2    from v$sql
  3   where lower(sql_text) like '%departmentalize%'
  4     and upper(sql_text) not like '%V$SQL%'
  5     and upper(sql_text) not like '%PARALLEL%'
  6     and upper(sql_text) not like '%CURSOR%'
  7     and upper(sql_text) not like '%DECLARE%'
  8  /

SQL_ID        EXECUTIONS
------------- ----------
'"'||SQL_TEXT||'"'
-------------------------------------------------------------------------------
6cdnz0kssshbm          1
"SELECT COUNT(*) CNT FROM DEPARTMENTALIZE"

6cdnz0kssshbm          2
"SELECT COUNT(*) CNT FROM DEPARTMENTALIZE"

6cdnz0kssshbm          1
"SELECT COUNT(*) CNT FROM DEPARTMENTALIZE"


ops$tkyte%ORA10GR2> select sql_id, DIFF_CALL_DURN, TOP_LEVEL_RPI_CURSOR from v$sql_shared_cursor where sql_id = '&S';
old   1: select sql_id, DIFF_CALL_DURN, TOP_LEVEL_RPI_CURSOR from v$sql_shared_cursor where sql_id = '&S'
new   1: select sql_id, DIFF_CALL_DURN, TOP_LEVEL_RPI_CURSOR from v$sql_shared_cursor where sql_id = '6cdnz0kssshbm'

SQL_ID        D T
------------- - -
6cdnz0kssshbm N N
6cdnz0kssshbm Y N
6cdnz0kssshbm N Y


Under similar circumstances - they are as "shareable" as anything else.

Let us all understand that that last paragraph should be:

If you use SQL, you are able to reuse that SQL.

period. Absolutely 100% period.




Also, that referenced article states:

... In previous versions, implicit cursors cannot handle multiple rows. ...


that was never true:
declare
  cursor c is select * from emp;
begin
  for x in C
  loop
      ....


C is explicitly defined, but implicitly dealt with - no open, no close, no fetch - multiple rows.


And ALL sql is as shareable as ANY sql.


implicit, explicit or whatever.

Under similar circumstances SQL is shareable

Duke Ganote, March 15, 2008 - 3:50 pm UTC

Perhaps it's the murkiness of "similar circumstances" that led Nanda and Feuerstein to conclude:

"The moral is that you can't assume that implicit and explicit cursors will match up in the shared pool. It's best to stick with one way or the other."

I think I agree wit their "moral", but disagree with their prescription.

Similar circumstances
Your example shows that SELECT INTO cursors are sharable with other SELECT INTO cursors.

I think my example shows that explicit cursors are shareable with implicit FOR LOOP cursors, and vice versa.

Dissimilar circumstances
Perhaps one special subclass of implicit cursors (those used for SELECT INTO statements) are not sharable with implicit FOR LOOP cursors or explicit cursors?

Certainly, the doc's definitions
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2124.htm#sthref4059
of v$sql_shared_cursor columns, like
DIFF_CALL_DURN - If Slave SQL cursor/single call
TOP_LEVEL_RPI_CURSOR -Is top level RPI cursor
aren't immediately meaningful to me.

Tom Kyte
March 15, 2008 - 4:29 pm UTC

all I was showing is - all sql is shareable - there is no real difference between implicit OR explicit sql (they are all the same really)

the different call duration has to do with the magic optimization of a select into.

And besides - given that SQL should not really be "repeated" over and over in different locations the entire "sharability" discussion doesn't fly with me - the query used in the select into appearing else where in the code NOT as a select into should happen approximately: zero times.

Instead of:

"The moral is that you can't assume that implicit and explicit cursors will
match up in the shared pool. It's best to stick with one way or the other."


I would say:

"The moral is that you can't assume that cursors executed in different environments, using different methods will match up in the shared pool. It's best to stick with modular code and execute the sql from one location - to avoid all kinds of mis-match issues."

Some consultants and some best practices

Marcus Rangel, August 15, 2008 - 5:19 pm UTC

Hi Tom,

I had a meeting yesterday with some consultants about problems identified on my application (financial app based on Oracle Forms). They pointed out the use of "select into" as a big problem and presented their "best practices" document to prove it. The customer accepted it immediately as the truth because the consultants were recommended by Oracle Brazil, but I disagree. I think this practice applies only for populating Forms fields (if so). Could you please share your thoughts on this good practice below ?

*********************************************************

(...)

The syntax for implicit cursors is easier to use yet often, less efficient. An explicit cursor is DECLARE'd OPEN'ed FETCH'ed and CLOSE'ed. Note that the user has to explicitly define each step.

(...)

Why explicit cursors are more advantageous than:

* Implicit Cursors: a SELECT INTO clause will make two accesses to the database when it queries in a value, to determine whether or not it should raise the TOO_MANY_ROWS exception. An example of this is, if the application uses POST-QUERY to populate some lookup fields on query. Usually, a SELECT field INTO :field FROM table WHERE primarykey=:primkey; is expected to return one row. If a row is returned, PL/SQL will access the database again to see if the form is attempting to fit two rows into one slot. With an explicit cursor, one fetch can be explicitly stated to be performed, assuming somewhat correctly that there are no duplicate primary keys returned. Thus on average, an implicit cursor will make two accesses to the database on a single row query while an explicit cursor will make only one. This could be a significant performance advantage, especially in client-server environments.

(...)

*********************************************************
Tom Kyte
August 20, 2008 - 8:56 am UTC

that is 100% totally a lie.

select into is more efficient than open/fetch/close.

select into a more proper (does the right thing).


IT IS A TOTAL MYTH THAT SELECT INTO IS NOT GOOD.......

they are totally wrong with their "make two accesses to the database"


and you know what - if there are TOO_MANY_ROWS - do not not think you would LIKE TO KNOW THAT - rather then select a random row from obviously corrupted data???!?!?!?!?!?



if these consultants have you go through the code and change your select intos into open/fetch/close they will

a) have utterly wasted your time and money
b) introduced potential bugs into your application (the too many rows, you sort of need that logic...)
c) made your code more complex - more lines of code - you have to remember to open/fetch/inspect fetch result/close - every time, over and over and over


do not do this.

If anything is a significant performance advantage - they would have hard cold numbers to back it up

and then won't, because they do not exist.



Thank you very much

Marcus Rangel, August 22, 2008 - 12:04 pm UTC

Thanks a lot, Tom. You are the original MythBuster. :-)

Why so much "official" opposition to implicit cursor for loops?

Stew Ashton, October 31, 2008 - 7:42 am UTC


Tom, the latest Oracle Magazine repeats something I read in a recent Oracle white paper on PL/SQL "best practices":

"If you expect to retrieve multiple rows of data and you do not know the upper limit, use BULK COLLECT with a FETCH statement that relies on a LIMIT clause to ensure that you do not consume too much per-session memory.....
using a cursor FOR loop ...isn't the most efficient approach (BULK COLLECT offers a faster way of querying data)." Steven Feuerstein
http://www.oracle.com/technology/oramag/oracle/08-nov/o68plsql.html
Has anyone anywhere ever offered any proof that BULK COLLECT is faster?

I just ran a comparison using your RUNSTATS package between the completely implicit
for rec in (select * from t) loop
null;
end loop;
and the BULK COLLECT approach ( t is a copy of all_objects with 100K, 200K or 300K rows). Differences are small and variable.

What is behind this preference for the BULK COLLECT approach? The only thing I can think of is that you can choose your own LIMIT.
Tom Kyte
November 02, 2008 - 4:44 pm UTC

Has anyone anywhere ever offered any proof that BULK COLLECT is faster?

It is - but "it depends"

If you are doing something where a forall *could* be used inside the loop, eg:


open c for ....;
loop
   fetch bulk collect into limit 100;
   ... process....
   forall i in 1 .. array.count insert/update/delete....;
   exit when c%notfound;
end loop;



bulk collect makes sense, if you are just retrieving and doing something one at a time, bulk collect will not (in 10g and above) since we do it (array fetch) already.


Why is implicit cursor of complex query is MUCH slower than a cursor of a simple one.

Yuri Gorelik, March 02, 2009 - 3:12 pm UTC

Tom,
Thank you very much for the explanations.
I don't understand though why an implicit cursor of a complex query is significantly slower than a cursor of a simple one.
In my case I have 2 options
1.
BEGIN
FOR rec IN (complex query with multiple joins and outer
joins of big tables returning about 2M rows)
LOOP
processing...
writing to a file using utl_file
END LOOP;
END;

A little more than 6 hours

2.
create materialized view of same complex query
BEGIN
DBMS_MVIEW('MV_NAME','c',ATOMIC_REFRESH => FALSE);
FOR rec IN (SELECT * FROM MV_NAME) LOOP
processing...
writing to a file using utl_file
END LOOP;
END;

Less than 1 hour.

Thanks a lot,
Yuri

Very informative discussion

Earl, January 06, 2011 - 11:02 am UTC

This is one of the most informative and valuable discussions I've seen about implicit vs. explicit cursors. Unfortunately I found it after I let someone talk me into converting from implicit to explicit cursors. Oh well...at least I know now and can go back and "re-fix" things when I have the time. Thanks Tom! Your the best!

cursor

sam, September 14, 2011 - 6:25 pm UTC

Tom:

In your example here, let us say the query should always return ONE row or NO ROWS. it retreives vvalues from 12 columns from 5 tables joined together

Would you still write it like this and declare 15 local variables
(select col1,col2, col3,...col12,
into l_col1, l_col2, l_col3,.....
from table1, table2, table3, table3, table 5
where.......)

or you would declare a cursor and a cursor variable and do the fetch/loop or some other better method?


declare
l_dummy dual.dummy%type;
begin
select * into l_dummy from dual;
end;


Tom Kyte
September 15, 2011 - 7:30 am UTC

you decide, it is your call. Just remember that if you use an explicit cursor, you must code:

declare
cursor c is ....;
l_rec c%rowtype;
l_extra c%rowtype;
begin
open c;
fetch c into l_rec;
if (c%notfound) then raise no_data_found; end if;
fetch c into l_extra;
if (c%found) then raise too_many_rows; end if;
close c;
....

end;


otherwise, you would just define a record and use one line of code - select into l_rec from .....

A reader, September 15, 2011 - 6:42 am UTC

Hi Tom,

in the above case :
loop
fetch bulk collect into limit 100;
... process....
forall i in 1 .. array.count insert/update/delete....;
exit when c%notfound;
end loop;

Does the User process send 100 update statements in a SINGLE CALL to the database when we use FOR ALL and an update statement inside it (assume array.count=100)?


Regards
Bix

Tom Kyte
September 15, 2011 - 7:56 am UTC

it sends ONE update statment with the direction to execute it 100 times.


In a single call.

cursor

sam, September 15, 2011 - 10:29 am UTC

Tom:

I like the one line approach for cursor that returns one ROW.

<<otherwise, you would just define a record and use one line of code - select into l_rec from ..... >>

But correct me if i am wrong.

Am i Stuck with declaring 15 variables using this approach?

If I use a CURSOR variable I can declare

CURSOR l_template is select a.col1, a.col2,.....from T1, T2, T3 where
l_rec l_template%rowtype;

begin

I can reference variables using "l_rec.col1", "l_rec.col2"

But I still need to OPEN, FETCH and handle MANY_ROWS and NO_ROWS exceptions in the LOOP.


correct?

Tom Kyte
September 16, 2011 - 1:20 pm UTC

Am i Stuck with declaring 15 variables using this approach?


read again:

<<otherwise, you would just define a record and use one line of code - select
into l_rec from ..... >>


You can either

a) define a record and select into
b) write all of the code I outlined above.

I frankly don't care which way you do it - it is your choice.

cursor

sam, September 15, 2011 - 11:22 am UTC

Tom:

I think you meant creating a record type like this which involves simlar work to creating 15 local variables

SQL> DECLARE
  2    TYPE t_StudentRecord IS RECORD (
  3      FirstName  lecturer.first_name%TYPE,
  4      LastName   lecturer.last_name%TYPE,
  5      Major      lecturer.major%TYPE);
  6
  7    myLecturer  t_StudentRecord;
  8  BEGIN
  9    SELECT first_name, last_name, major
 10      INTO myLecturer
 11      FROM lecturer
 12      WHERE ID = 10012;
 13  END;
 14  /

PL/SQL procedure successfully completed.


I thought you might be interested in this article from oracle web applications book by oreilly:

1) SELECT..INTO is slower than explicit cursor because it makes two fetches insead of one. The first fetch determines how many rows the qyeru returns, and the second fetch actually retrieves the data and assigns columns to variables.

2) SELECT...INTO raises an expcetion if query does nto return any rows. This behavious often results in unhandled exceptions, because the harried programmer in a rush to finish, makes wrong assumption about the query.

3) SELECT..INTO makes you lazy. WE should take positive steps to prevent forseable errors, not simply respond to them as if they're controllable acts of God. The extra time it takes to implement a single row SELECT using an explicit cursor almost always outweigh the short-term benefits of SELECT..INTO command.

I disagree with their analysis that Explicit cursor is better than implicit cursor. Do you agree with their points?

Tom Kyte
September 16, 2011 - 1:26 pm UTC

1) that is a lie, it is trivial to prove that is a lie. Just turn on sql trace and tell me how many fetch calls you see.

2) and if the programmer didn't handle the exception (which by the way is a GOOD THING) - if the programmer did it using explicit cursors and ignored the fact no data was fetched - then the programmer HAS A BUG.

So once again, this is a lie. we want the exception to be thrown that way we KNOW there was an error instead of silently hiding it.

3) that is "not smart" select into makes your code faster, safer, smaller, easier to debug, easier to maintain.



Everything they wrote is the complete opposite of reality. I have a presentation (effective plsql) that I deliver that rips all of those points apart.


A question and some comments

Tim, September 15, 2011 - 2:48 pm UTC

First my question.  I did not see this particular question addressed previously, so I apologize if it was already answered, but in the scenario that you have to loop through a set of records and perform some action on them, is there a significant performance difference between the following:

    declare
        cursor c_cursor is
            select * from table_x;
    begin
        for r in c_cursor loop
            ...
        end loop;
    end;

and

    begin
        for r in (select * from table_x) loop
            ...
        end loop;
    end;

If I understand the issue correctly, the overhead is in the extra code handling the opening, fetching and closing of the cursors.  So am I right in thinking that if we already have to code this for the loop, that it does not matter too much if it is a explicit or implicit cursor that the FOR LOOP works with?


Second, I really found the TOAD reference post amusing.  I cannot remember how many times a developer or report writer has come to me wondering why their “super fast query” runs slow in code or in a report.  If I got a dollar for every time I said, “wrap the query with a count and then how fast is it in TOAD?” I could retire.  Or, at least have a nice lunch.


Lastly, I wanted to mention something in regards to the post “Partly agree” by Tomasz Michniewski.  He contends that an OPEN/FETCH/CLOSE is faster than a SELECT INTO in the scenario that the table is large and there are records “deeper” in the table that the SELECT INTO will have to process.  Well, unless I am mistaken, OPEN/FETCH/CLOSE has the same issue, as the entire query is processed before you are able to fetch the values out, correct?  That is, at least, how the thing behaves.  In my example here, I will select two rows out of a table in the cursor, then change the value of the second row.  When I get to the second row and output the results, you will see that the cursor does not “see” the update.  This implies that the cursor queried the value of the second row before the code ever attempted to fetch the second row.  Then I select from the table at the end to prove that the record was indeed updated (and I realize I am referencing an old post, but I found this out back when I was developing on 8i, so I am pretty sure it worked like this at the time of the original post, too):


Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> set serveroutput on
SQL> drop table t;

Table dropped.

SQL> create table t (pk number, val varchar2(10));

Table created.

SQL> insert into t values (1, 'test1');

1 row created.

SQL> insert into t values (2, 'test2');

1 row created.

SQL> create or replace procedure test_t
is
  2    3        v_pk number;
  4      v_val varchar2(10);
  5
  6      cursor c_cursor is
  7             select *
  8          from t
  9          order by pk asc;
 10  begin
 11     open c_cursor;
 12      fetch c_cursor into v_pk, v_val;
 13
 14      update t set val = 'changed' where pk = 2;
 15      commit;
 16
 17      dbms_output.put_line('pk is: ' || v_pk || '; val is: ' || v_val);
 18
 19      fetch c_cursor into v_pk, v_val;
 20
 21      dbms_output.put_line('pk is: ' || v_pk || '; val is: ' || v_val);

 22   23        close c_cursor;
 24  end;
 25  /

Procedure created.

SQL> exec test_t;
pk is: 1; val is: test1
pk is: 2; val is: test2

PL/SQL procedure successfully completed.

SQL> select * from t;

        PK VAL
---------- ----------
         1 test1
         2 changed

SQL>

Tom Kyte
September 16, 2011 - 1:58 pm UTC

there is no difference between:

declare
        cursor c_cursor is
            select * from table_x;
    begin
        for r in c_cursor loop
            ...
        end loop;
    end;

and

    begin
        for r in (select * from table_x) loop
            ...
        end loop;
    end;



they are both implicit cursor for loops. You can define your cursor in a declare block or not - that is up to you (mostly a matter of style, I like them inline usually myself). But they are identical at runtime.

BOTH OF THEM ARE IMPLICIT CURSORS however - you do not have any explicit cursors.

An explicit cursor is a cursor you explicitly OPEN, FETCH and CLOSE. If you do not use OPEN on the cursor - it is implicit. Defining it doesn't make it explicit.



Well, unless
I am mistaken, OPEN/FETCH/CLOSE has the same issue, as the entire query is
processed before you are able to fetch the values out, correct?


that is very false - very wrong. We have this thing called read consistency. We do not copy a result set anywhere necessarily before you get the first row. We read the data many times straight from the table as you fetch it. We DO NOT materialize a query before you get the first row.

http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html


Try fetching the first row from your biggest table. Measure how many IO's it does (almost none).


As for what Tomasz was stating - here is an example that demonstrates what he meant:

ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t as select rownum id, all_objects.* from all_objects;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> delete from t where id > 1 and id < (select count(*) from t);
ops$tkyte%ORA11GR2> commit;
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select id from t;

        ID
----------
         1
     72265

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_monitor.session_trace_enable;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> declare
  2          l_id number;
  3          cursor c is select id from t explicit where id = 1;
  4  begin
  5          select id into l_id from t implicit where id = 1;
  6  
  7          open c;
  8          fetch c into l_id;
  9          close c;
 10  end;
 11  /

PL/SQL procedure successfully completed.


the tkprof will show:

SELECT ID 
FROM
 T IMPLICIT WHERE ID = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0       1084          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0       1084          0           1


SELECT ID
FROM
 T EXPLICIT WHERE ID = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          4          0           1




but I would say this use case is meaningless. If you are doing a select into, you are going for one row. When you go for one row - what kind of plan are you expecting? I'd be expecting (demanding, insisting) on an index access - wouldn't you? If that was the case - if we index ID:

SELECT ID 
FROM
 T IMPLICIT WHERE ID = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1

SELECT ID 
FROM
 T EXPLICIT WHERE ID = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1





well - there you go - no difference.

But, I would also maintain that you need to have a second fetch on the explicit cursor - in order to ensure that there is no second row! If there is, you have data corruption (and you'd like to know that - sooner rather than later).

Hey Sam....

Tim, September 15, 2011 - 3:23 pm UTC

Hey Sam, did you copy and paste that article or re-type it by hand? There are a lot of typo's and spelling errors in it. If that came straight from the author, I would question their ability to guage quality. They talk about SELECT...INTO making coders lazy, when they are too lazy to hit spell check.

Reason #1 is just plain wrong, and Tom addressed that multiple times earlier in the thread. SELECT...INTO makes 1 fetch.

But, my favorite is reason #2. The arguement against SELECT...INTO is that developers are too stupid to handle the exceptions that get thrown by these statements? That is ignoring the 10-15 times Tom has correctly pointed out that these exceptions are typically catching bugs in the code (note that the majority of the audience to this article is developers; just start your article out with "you are all stupid"). SELECT...INTO is bad because a rushed developer may assume that there will not be more than one row and an exception does not get handled? But it is not just as bad if that same developer assumes there will not be more than one row and nobody ever knows that the code is not doing what it is supposed to do? That is better? Please.

Reason #3 is contradictory to itself. It says that we should prevent forseeable errors. Well, I would think that processing the first row returned and ignoring that there might be more rows is a HUGE forseeable error. If you only want one row, and you know you only want one row, then your query should be written to return only the row you wish to process, regardless of which method you use. ROW_NUMBER() and ORDER BY/ROWNUM are the RIGHT way to minimize errors in this regard, not ignoring the fact that more than 1 row may return.

A reader, September 16, 2011 - 12:33 pm UTC

Tom what is your salary? In oracle how much they pay DBAs?
Tom Kyte
September 16, 2011 - 2:20 pm UTC

Frankly, it is none of your business, is it.





Above question...

Alexander, September 16, 2011 - 2:08 pm UTC

So hoping that was real and not a joke otherwise not nearly as funny.

That reminds me, can I have your cell number I have a release this weekend 3am Sunday morning I might want to give you a call for some assistance. Thanks.

cursors

sam, September 16, 2011 - 3:50 pm UTC

that was a stupid question on salary that has nothing to do with this thread at all.

A DBA salary normally depends on
1) Country (Nigeria would be different than USA)
2) Skill level (junior, mid-level, senior, master)
3) company you work for
etc., etc..

If anyone is going for the $$$ do not go for it because you will never be good at something unless you have a passion for it and enjoy it. This is one reason why Tom is excellent in it.

The article I posted was from this book and I typed it pretty fast so the typos are mine.

"Oracle Web Application - PL/SQL Developer Introduction"
Oreilly - Andrew Odewahn

Now I am dissapointed and I am not sure I can trust many of the books I have. I will stay focused on Tom's books although I have not seen much on PL/SQL itself. Most of the stuff is by Steve Feurestein and others.
Tom Kyte
September 16, 2011 - 4:49 pm UTC

When reading a book - or a web page - or anything related to technology -

LOOK FOR SOME NUMBERS

look for some evidence that what the person states has a probability of being true given the circumstances they are having (their context). Look for the full example from start to finish - so you can see what their environment looked like, what their data looked like, what their example was demonstrating.

If you don't see that - don't use the advice.

Be "numerate"

cursors

sam, September 16, 2011 - 8:28 pm UTC

That means I will stop reading.
How many web sites or books do you see that provide evidence or numbers that prove what they state.

Most of them just present ideas and recommendations without any proof.

You would think that poeople who write books are super-experts in that field/subject but obviously some are not and just trying to make a buck.

What can "turn off" implicit cursor array fetching

Stew Ashton, December 01, 2011 - 10:39 am UTC


Tom, I am using a packaged version of Oracle 11.2.0.2 that is provided in a VM for OTN Developer Day. I discover that implicit array fetching is not working. Is there any parameter, hidden or not, that can turn this off?

Thanks in advance for any help you can give.
> select * from v$version
BANNER 
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production 
PL/SQL Release 11.2.0.2.0 - Production 
CORE 11.2.0.2.0 Production 
TNS for Linux: Version 11.2.0.2.0 - Production 
NLSRTL Version 11.2.0.2.0 - Production 

> alter system flush shared_pool;
system FLUSH altered.
> begin
for REC in (
  SELECT * FROM ALL_USERS
) LOOP
  null;
end loop;
end;
/
anonymous block completed
> SELECT fetches, rows_processed FROM V$SQL
WHERE sql_text like 'SELECT * FROM ALL_USERS%';
FETCHES                ROWS_PROCESSED 
---------------------- ---------------------- 
54                     53

Never mind, found PLSQL_OPTIMIZE_LEVEL

Stew Ashton, December 02, 2011 - 2:29 am UTC


Sorry, Google started working this morning ;)

The OTN Developer Day VM has set PLSQL_DEBUG to FALSE and PLSQL_OPTIMIZE_LEVEL to 0 (possibly as a result). Funny thing is, PLSQL_DEBUG is deprecated...

Never mind again...

Stew Ashton, December 02, 2011 - 9:47 am UTC


Sorry, I accused the wrong tool. SQL Developer has "PL/SQL Compiler" preferences, including the optimization level which appears to be set to 0 by default.

SQL Developer users beware...