Skip to Main Content
  • Questions

  • Cursor parameters versus global bind value

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, leor.

Asked: September 19, 2017 - 3:52 pm UTC

Last updated: December 21, 2017 - 10:50 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Tom, we have having a bit of a religious war at work regarding declaration of cursors and usage.

Some developers will write a cursor such as:

      cursor c_cur1 is  
      select * from foo where foo_id = myID;


Where myID is a pl/sql variable defined in the package or procedure in the package.

Alternate method is
       cursor c_curl1( P_ID IN NUMBER) is
        select * from foo where foo_id = P_Id;


I prefer the 2nd method, as you don't have to go hunting around looking for the bind variables. Would there be any non-negligible timing difference?
I said, not enough to care about it.

Thanks,
Leor

and Chris said...

OK, time for a test!

Let's compare the performance of the two methods:

create or replace package pkg as 
  procedure cur_global_var;
  procedure cur_local_var;
  procedure implicit_cur;
end pkg;
/

create or replace package body pkg as 

  g_var pls_integer := 1;

  procedure cur_global_var as
    cursor c is 
      select * from dual
      where  1 = g_var;
    res varchar2(1);
  begin
    open c;
    fetch c into res;
    close c;
  end;
  
  procedure cur_local_var as
    cursor c ( var in pls_integer ) is 
      select * from dual
      where  1 = var;
    v pls_integer := 1;
    res varchar2(1);
  begin
    open c (v);
    fetch c into res;
    close c;
  end;
  
  procedure implicit_cur as

    var pls_integer := 1;
    res varchar2(1);
  begin
    select * into res 
    from dual where  1 = var;
  end;
end pkg;
/

declare
  iterations pls_integer := 100000;
begin
  pkg.cur_local_var;
  pkg.cur_global_var;
  runstats_pkg.rs_start;
  
  for i in 1 .. iterations loop
    pkg.cur_global_var;
  end loop;

  runstats_pkg.rs_middle;
  
  for i in 1 .. iterations loop
    pkg.cur_local_var;
  end loop;
  
  runstats_pkg.rs_stop(100);
  
end;
/

===============================================================================================
RunStats report : 21-DEC-2017 02:35:39
===============================================================================================


-----------------------------------------------------------------------------------------------
1. Summary timings
-----------------------------------------------------------------------------------------------

Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIMER elapsed time (hsecs)                                      8,551        8,546           -5
TIMER cpu time (hsecs)                                          8,517        8,524            7

Comments:
1) Run2 was .1% quicker than Run1
2) Run2 used .1% less CPU time than Run1


-----------------------------------------------------------------------------------------------
2. Statistics report
-----------------------------------------------------------------------------------------------

Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
LATCH virtual circuit holder                                      107            5         -102
LATCH checkpoint queue latch                                      486          378         -108
LATCH virtual circuit queues                                      115            5         -110
LATCH ktfbn latch                                                   1          129          128
LATCH Real-time descriptor latch                                  257          128         -129
LATCH object queue header freelist                                197           23         -174
LATCH JS queue state obj latch                                 15,408       15,196         -212
LATCH session idle bit                                            305           77         -228
LATCH cache table scan latch                                      392           14         -378
LATCH enqueue hash chains                                      32,457       32,025         -432
LATCH virtual circuit buffers                                     555           21         -534
LATCH multiblock read objects                                     800           32         -768
LATCH simulator hash latch                                      1,117          110       -1,007
LATCH shared pool simulator                                     1,944            9       -1,935
LATCH object queue header operation                             6,442          411       -6,031
LATCH shared pool                                           2,016,955    2,002,078      -14,877
LATCH cache buffers chains                                  6,017,789    6,001,423      -16,366
STAT  session pga memory                                   -3,866,624            0    3,866,624


-----------------------------------------------------------------------------------------------
3. Latching report
-----------------------------------------------------------------------------------------------

Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
LATCH total latches used                                    8,109,708    8,066,138      -43,570

Comments:
1) Run2 used .5% fewer latches than Run1


-----------------------------------------------------------------------------------------------
4. Time model report
-----------------------------------------------------------------------------------------------

Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIME  parse time elapsed                                           25           45           20
TIME  PL/SQL execution elapsed time                         6,128,530    6,124,314       -4,216
TIME  DB time                                              83,814,683   86,089,628    2,274,945
TIME  sql execute elapsed time                             83,809,344   86,089,628    2,280,284
TIME  DB CPU                                               83,437,000   85,869,000    2,432,000


-----------------------------------------------------------------------------------------------
5. About
-----------------------------------------------------------------------------------------------
- RunStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
- Based on the original RUNSTATS utility by Tom Kyte

===============================================================================================
End of report
===============================================================================================


So you're talking a runtime difference of 5 hundredths of a second over a million executions... Seems negligible to me!

Of course, the examples search by ID. Implying you're doing a primary key lookup and expect a single row.

So to me the debate of explicit cursor methods misses the point. Implicit fetches are faster than both:

</code>declare
iterations pls_integer := 1000000;
begin
pkg.cur_local_var;
pkg.implicit_cur;
runstats_pkg.rs_start;

for i in 1 .. iterations loop
pkg.implicit_cur;
end loop;

runstats_pkg.rs_middle;

for i in 1 .. iterations loop
pkg.cur_local_var;
end loop;

runstats_pkg.rs_stop(100);

end;
/

===============================================================================================
RunStats report : 21-DEC-2017 02:39:14
===============================================================================================


-----------------------------------------------------------------------------------------------
1. Summary timings
-----------------------------------------------------------------------------------------------

Type Name Run1 Run2 Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIMER elapsed time (hsecs) 7,955 8,658 703
TIMER cpu time (hsecs) 7,928 8,637 709

Comments:
1) Run1 was 8.1% quicker than Run2
2) Run1 used 8.1% less CPU time than Run2


-----------------------------------------------------------------------------------------------
2. Statistics report
-----------------------------------------------------------------------------------------------

Type Name Run1 Run2 Diff
----- -------------------------------------------------- ------------ ------------ ------------
LATCH cache buffers chains 6,000,143 6,000,262 119
LATCH Real-time descriptor latch 129 256 127
LATCH messages 1,814 1,958 144
LATCH SQL memory manager workarea list latch 1,743 1,948 205
STAT recursive cpu usage 7,106 7,381 275
LATCH shared pool 5,001,008 5,001,424 416
LATCH active service list 5,263 5,737 474
STAT CPU used by this session 7,932 8,641 709
LATCH JS queue state obj latch 14,328 15,588 1,260
LATCH enqueue hash chains 30,077 32,758 2,681
STAT session uga memory max 867,592 900,904 33,312
STAT session uga memory 900,928 867,488 -33,440
STAT recursive calls 1,002,735 2,002,735 1,000,000
STAT buffer is not pinned count 2,000,000 0 -2,000,000
STAT session pga memory -3,866,624 0 3,866,624


-----------------------------------------------------------------------------------------------
3. Latching report
-----------------------------------------------------------------------------------------------

Type Name Run1 Run2 Diff
----- -------------------------------------------------- ------------ ------------ ------------
LATCH total latches used 11,058,949 11,064,732 5,783

Comments:
1) Run1 used .1% fewer latches than Run2


-----------------------------------------------------------------------------------------------
4. Time model report
-----------------------------------------------------------------------------------------------

Type Name Run1 Run2 Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIME parse time elapsed 24 22 -2
TIME PL/SQL execution elapsed time 4,482,207 5,959,845 1,477,638
TIME DB time 78,864,287 86,093,126 7,228,839
TIME sql execute elapsed time 78,859,881 86,093,126 7,233,245
TIME DB CPU 78,571,000 85,880,000 7,309,000


-----------------------------------------------------------------------------------------------
5. About
-----------------------------------------------------------------------------------------------
- RunStats v2.01 by Adrian Billington ( http://www.oracle-developer.net )
- Based on the original RUNSTATS utility by Tom Kyte

===============================================================================================
End of report
===============================================================================================</code>

This gives a more notable 7 seconds benefit over 1 million executions. Still small in the grand scheme of things, but much bigger than the two explicit approaches!

And you get the no_data_found and too_many_rows exceptions for free. You have to code these yourself when using explicit cursors!

NB - I've used Adrian Billington's adaption of Tom's runstats to get the stats:

https://github.com/oracle-developer/runstats

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library