Cursor Basics: Bulk Binds

declare
    l_task varchar2(100);
    cursor c is
    select level as n
    from dual
    connect by level <= 10;
begin
    l_task := 'print all the rows of explicit cursor c to output' || chr(10)
        || 'without row by row (slow by slow) iteration';
    dbms_output.put_line(l_task);
end;

In the previous post we discussed the most basic approaches to iterating through a resultset using cursors. Since those approaches traverse the cursor one row at a time they are often referred to as slow by slow processing. This slowness turns out to be the cost of switching between the PL/SQL engine and the SQL engine as each record is fetched. This post will explore a revolutionary optimization in cursor iteration used to minimize the cost of these context switches: the bulk bind. Using bulk binding to fetch data is more commonly referred to as bulk collect.

Getting Started

The source code for all articles here on practicalplsql.org is available in the practicalplsql public repository on github in the scripts directory. You can use scripts/create.user.practicalplsql.sql to create the practicalplsql schema. Sample data for articles can be found in the scripts/examples directory organized by example name.

Scripts for this post are located in scripts/articles/cursor-basics/part-2-bulk-binds. For many of the examples I will be using a simple table populated with employees, storing the name and job of each employee. This example is located at scripts/examples/simple-employees. The example includes a few standalone procedures for commonly repeated code to keep the code more readable. These include the following: print_boolean_attribute, print_employee and print_timing.

The performance tests in this article use the standalone function get_sql_engine_fetches to estimate the number of fetches for a given sql statement. This is done by using a text literal to track the sql statement fetches using v$sqlarea.

At times I may use syntax specific to a more recent version of Oracle for clarity of presentation. In these cases, I will include the more recent syntax in the article and use conditional compilation in the actual scripts to make the examples backward compatible when possible. All examples have been tested in sql developer. All scripts should be executed with serveroutput on, so this repetition is omitted for clarity. Finally, when several scripts produce the same correct output, the output will only be shown once to avoid repetition. Because each example is meant to focus on different aspects of the code, some parts are ommitted when they are not necessary to make the code compile and execute. This also helps to show that code can be written which compiles and works in many cases even when it is lacking in part of the implementation.

Bulk Binding

Bulk binding a cursor into a collection type makes it much faster to get all of the records for processing by passing an empty collection to the SQL engine as an out-bind variable. Instead of fetching each record and returning it, the SQL engine fetches all of the records at once into the bind variable and returns the entire collection to the PL/SQL engine. This results in a measurable performance gain because of the cost incurred every time execution is switched between the context of the PL/SQL engine and the context of the SQL engine, in addition to letting the SQL engine use set based operations when fetching the records. After the collection is populated and returned to the PL/SQL environment, the contents can be iterated very quickly.

Prompt Method: Bulk Bind
declare
    cursor c_emps is
         select e.name, e.job
         from employees e
         order by e.job, e.name;
    type t_emps is table of c_emps%rowtype;
    l_emps t_emps;
begin
    open c_emps;
    fetch c_emps bulk collect into l_emps;
    close c_emps;
    
    for i in indices of l_emps loop
        print_employee(l_emps(i).name, l_emps(i).job);
    end loop;
end;

Gina SALES_EXEC
Ann SALES_MGR
Tobias SALES_MGR
Alex SALES_REP
Jane SALES_REP
John SALES_REP
Julie SALES_REP
Sarah SALES_REP
George SALES_SUPPORT
Martin SALES_SUPPORT
Thomas SALES_SUPPORT

The bulk bind syntax is very similar to the open, fetch, close method for iterating cursor results. The cursor has to be opened prior to fetching the records and the cursor should be closed after the fetch is completed. With bulk binding, we declare a collection type and a collection variable to hold the fetched records. This collection variable is passed to the SQL engine as an out-bind variable. Instead of fetching each record in a loop, all records are fetched into the collection variable with a single call using bulk collect. The cursor can be closed right after this bulk fetch because all of the records are now in the collection and the dialog with the SQL engine is finished.

Bulk Bind Performance

If we compare the time it takes to iterate through a resultset we can see the performance gains from bulk binds even with simple cursors.

prompt Bulk Bind Timing Comparison
declare
    l_records number := 10000;
    l_start timestamp;
    cursor cur_data is
        select 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;   
    type t_rows is table of cur_data%rowtype index by pls_integer;
    l_row cur_data%rowtype;
    l_row_table t_rows;
begin
    l_start := localtimestamp;

    open cur_data;
    loop
        fetch cur_data into l_row;
        exit when cur_data%notfound;
    end loop;
    close cur_data;
    
    print_timing(l_start, l_records, 'open, fetch, close');
        
    l_start := localtimestamp;
    
    open cur_data;
    fetch cur_data bulk collect into l_row_table;
    close cur_data;
    
    print_timing(l_start, l_records, 'bulk collect');

end;

0.019 seconds for 10000 rows: open, fetch, close
0.006 seconds for 10000 rows: bulk collect

This timing gain is due to the elimination of context switches and leveraging the ability of the SQL engine to do set based operations.

If we seed the sql statement with a text literal, we can view the number of fetches required to iterate the cursor records using the v$sqlarea view.

prompt Bulk Bind Fetch Comparison
declare
    l_records number := 10000;
    l_fetches_previous number;
    l_fetches number;
    cursor cur_data is
        select 
            'bulk_bind_fetch_comparison' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;   
    type t_rows is table of cur_data%rowtype index by pls_integer;
    l_row cur_data%rowtype;
    l_row_table t_rows;
begin

    l_fetches_previous := get_sql_engine_fetches('bulk_bind_fetch_comparison');

    open cur_data;
    loop
        fetch cur_data into l_row;
        exit when cur_data%notfound;
    end loop;
    close cur_data;
    
    l_fetches := get_sql_engine_fetches('bulk_bind_fetch_comparison') - l_fetches_previous;
    
    dbms_output.put_line('open, fetch, close: ' 
        || l_fetches || ' fetches for ' || l_records || ' records');
        
    l_fetches_previous := get_sql_engine_fetches('bulk_bind_fetch_comparison');

    open cur_data;
    fetch cur_data bulk collect into l_row_table;
    close cur_data;
    
    l_fetches := get_sql_engine_fetches('bulk_bind_fetch_comparison') - l_fetches_previous;
    
    dbms_output.put_line('bulk collect: ' 
        || l_fetches || ' fetches for ' || l_records || ' records');

end;


open, fetch, close: 10001 fetches for 10000 records
bulk collect: 1 fetches for 10000 records

By using bulk collect, we have eliminated 10,000 context switches by letting the SQL engine iterate the cursor! The fact that only one fetch was required clearly demonstrates that the SQL engine doesn’t fetch each record into the collection variable individually. All 10,000 records were fetched into the collection variable as a single set.

Ref Cursors with Static SQL

A cursor variable can be used with bulk binding using static sql. The cursor is opened, fetched into the collection using bulk collect, and then closed. The primary difference with using cursor variables is that we have to declare a record type for fetching into. We can then define the collection type as a table of this record type. Because the strongly typed cursor variable returns this record type, the collection can also be anchored to the rowtype.

Prompt Strongly Typed Cursor Variable Bulk Bind
declare
    type t_emp_rec is record(
        name employees.name%type, 
        job employees.job%type);
    type t_emp_cur is ref cursor return t_emp_rec;
    c_emps t_emp_cur;
    --type t_emps is table of t_emp_rec;
    type t_emps is table of c_emps%rowtype;
    l_emps t_emps;
begin
    open c_emps for
        select e.name, e.job
        from employees e
        order by e.job, e.name;

    fetch c_emps bulk collect into l_emps;
    close c_emps;
    
    for i in indices of l_emps loop
        print_employee(l_emps(i).name, l_emps(i).job);
    end loop;
end;

A weakly typed cursor variable requires the collection type to be based on the record type.

Prompt Weakly Typed Cursor Variable Bulk Bind
declare
    type t_emp_rec is record(
        name employees.name%type, 
        job employees.job%type);
    c_emps sys_refcursor;
    type t_emps is table of t_emp_rec;
    l_emps t_emps;
begin
    open c_emps for
        select e.name, e.job
        from employees e
        order by e.job, e.name;

    fetch c_emps bulk collect into l_emps;
    close c_emps;
    
    for i in indices of l_emps loop
        print_employee(l_emps(i).name, l_emps(i).job);
    end loop;
end;

Cursor Variables with Dynamic SQL

We can use bulk binding to populate a collection from a cursor variable that is defined at run-time using dynamic sql, but this will only work for weakly typed cursor variables.

Prompt Strongly Typed Cursor Variable Bulk Bind using Dynamic Sql Fails
declare
    type t_emp_rec is record(
        name employees.name%type, 
        job employees.job%type);
    type t_emp_cur is ref cursor return t_emp_rec;
    c_emps t_emp_cur;
    type t_emps is table of t_emp_rec;
    l_emps t_emps;
    l_sql varchar2(100);
begin
    l_sql := '
        select e.name, e.job
        from employees e
        order by e.job, e.name';
        
    open c_emps for l_sql;
    fetch c_emps bulk collect into l_emps;
    close c_emps;
    
    for i in indices of l_emps loop
        print_employee(l_emps(i).name, l_emps(i).job);
    end loop;
end;

PLS-00455: cursor 'C_EMPS' cannot be used in dynamic SQL OPEN statement

This has nothing to do with bulk binding, using dynamic sql we cannot use a strongly typed ref cursor. The compiler cannot know if the sql statement that is used to open the cursor variable at run-time will have the required return type.

We can use a weakly typed ref cursor with dynamic sql.

Prompt Weakly Typed Cursor Variable Bulk Bind using Dynamic Sql
declare
    type t_emp_rec is record(
        name employees.name%type, 
        job employees.job%type);
    c_emps sys_refcursor;
    type t_emps is table of t_emp_rec;
    l_emps t_emps;
    l_sql varchar2(100);
begin
    l_sql := '
        select e.name, e.job
        from employees e
        order by e.job, e.name';
        
    open c_emps for l_sql;
    fetch c_emps bulk collect into l_emps;
    close c_emps;
    
    for i in indices of l_emps loop
        print_employee(l_emps(i).name, l_emps(i).job);
    end loop;
end;

Implicit cursors

There is actually no reason to use an explicit cursor if we are going to bulk bind an entire resultset into a collection variable. We can simple select into a collection variable using bulk collect. The variable has to declared as a collection of records with the same type that will be returned by the SQL statement. This will open an implicit cursor in the SQL engine that we don’t have to define, open or close.

Prompt Implicit Cursor Bulk Bind
declare
    type t_emp_rec is record(
        name employees.name%type, 
        job employees.job%type);
    type t_emps is table of t_emp_rec;
    l_emps t_emps;
begin
    select e.name, e.job
    bulk collect into l_emps
    from employees e
    order by e.job, e.name;

    for i in indices of l_emps loop
        print_employee(l_emps(i).name, l_emps(i).job);
    end loop;
end;

Because there is no need to declare an explicit cursor or cursor variable, there are also no limitations when using dynamic sql with an implicit cursor.

Prompt Implicit Cursor Bulk Bind using Dynamic Sql
declare
    type t_emp_rec is record(
        name employees.name%type, 
        job employees.job%type);
    type t_emps is table of t_emp_rec;
    l_emps t_emps;
    l_sql varchar2(100);
begin
    l_sql := '
        select e.name, e.job
        from employees e
        order by e.job, e.name';
        
    execute immediate l_sql bulk collect into l_emps;
    
    for i in indices of l_emps loop
        print_employee(l_emps(i).name, l_emps(i).job);
    end loop;
end;

If the implicit cursor is so flexible, you may be wondering why we took the time to look at using bulk collect with explicit cursors? It takes more code to open the explicit cursors and cursor variables have limitations when it comes to using dynamic sql, perhaps the explicit cursor approach is unnecessary with bulk collect? To answer these questions, consider one aspect of traditional single row processing that is efficient, the memory footprint.

If a cursor is just instructions for how to create a resultset and we can fetch just a single record at a time from that resultset, the process doesn’t seem to require much more memory overhead than the size of a single record. How much more memory does it take to populate a collection of a million records using bulk bind? The short answer is about a million times more memory. Is the performance gain worth this increase in memory requirements?

Whether we use explicit or implicit cursors, the memory cost of selecting a large resultset into a collection using bulk collect is the same. The collection contents need memory, and this takes up the session’s memory. Large collections with many columns and many rows can take a significant amount of session memory. Many user sessions populating large collections can put a large load on the database with the user sessions competing for memory resources. Fortunately there is a way to have the performance benefits of bulk binding and minimize the memory required for the collection variables: the limit clause.

The Limit Clause

If populating a large collection takes a large amount of memory, there must be a way to break this down into smaller sets that take up less memory. The limit clause allows us to optimize the memory footprint of bulk collect by only fetching a limited set of rows into the collection at a time. This combines the efficiency of bulk collect with the ability to tune the amount of session memory required by processing code.

This optimization is only available with explicit cursors and cursor variables. The limit clause is invalid when selecting a bulk collect into a collection with an implicit cursor and the code will not compile.

Prompt Implicit Cursor Bulk Bind with Limit Fails
declare
    type t_emp_rec is record(
        name employees.name%type, 
        job employees.job%type);
    type t_emps is table of t_emp_rec;
    l_emps t_emps;
begin
    select e.name, e.job
    bulk collect into l_emps limit 3
    from employees e
    order by e.job, e.name;
end;

PL/SQL: ORA-00923: FROM keyword not found where expected

When using the limit clause, the syntax looks almost like the traditional open, fetch, close method of iterating cursor results. The cursor is opened, there is a loop for fetching records, and the cursor is closed. Inside the loop, the single record fetch has been replaced by a bulk collect fetch with a specified limit and there is an exit condition.

Prompt Method: Bulk Bind With Limit Clause
declare
    cursor c_emps is
        select e.name, e.job
        from employees e
        order by e.job, e.name;
    type t_emps is table of c_emps%rowtype;
    l_emps t_emps;
begin
    open c_emps;
    
    <<outer>>
    loop
        fetch c_emps bulk collect into l_emps limit 3;
        exit when l_emps.count = 0;
        
        <<inner>>
        for i in indices of l_emps loop
            print_employee(l_emps(i).name, l_emps(i).job);
        end loop inner;
        
    end loop outer;
    close c_emps;
end;

Gina SALES_EXEC
Ann SALES_MGR
Tobias SALES_MGR
...

In contrast to the single record fetch, the bulk collect exit condition checks the collection’s count instead of the cursor %notfound attribute….things are definitely different here. When we fetch single records, an unsuccessful fetch doesn’t modify the record variable and an unsuccessful fetch always sets the cursor %notfound attribute to true. Bulk collect behaves differently, an unsuccessful fetch leaves the collection empty and %notfound is true for both the last successful fetch and the final unsuccessful fetch.

Prompt Bulk Bind With Limit Clause and %notfound
declare
    cursor c_items is
        select level as n
        from dual
        connect by level <= 5;
    type t_items is table of c_items%rowtype;
    r_item c_items%rowtype;
    l_items t_items;
    i number := 0;
begin
    dbms_output.put_line('Fetching single records');
    open c_items;
    loop
        i := i + 1;
        fetch c_items into r_item;
        dbms_output.put('fetch ' || i 
            || ': r_item.n = ' || r_item.n);
        print_boolean_attribute(c_items%notfound, '%notfound');            
        exit when c_items%notfound;
    end loop;
    close c_items;

    dbms_output.put_line('Bulk Collect with Limit');
    i := 0;
    open c_items;    
    loop
        i := i + 1;
        fetch c_items bulk collect into l_items limit 3;
        dbms_output.put('bulk fetch ' || i 
            || ': l_items.count = ' || l_items.count);
        dbms_output.put(case when l_items.count = 0 then ' no' end || ' values ');
        
        for i in indices of l_items loop
            dbms_output.put(l_items(i).n || ' ');
        end loop;
            
        print_boolean_attribute(c_items%notfound, '%notfound');            
        exit when l_items.count = 0;
        
    end loop;
    close c_items;
end;

Fetching single records
fetch 1: r_item.n = 1(%notfound is false)
fetch 2: r_item.n = 2(%notfound is false)
fetch 3: r_item.n = 3(%notfound is false)
fetch 4: r_item.n = 4(%notfound is false)
fetch 5: r_item.n = 5(%notfound is false)
fetch 6: r_item.n = 5(%notfound is true)

Bulk Collect with Limit
bulk fetch 1: l_items.count = 3 values 1 2 3 (%notfound is false)
bulk fetch 2: l_items.count = 2 values 4 5 (%notfound is true)
bulk fetch 3: l_items.count = 0 no values (%notfound is true)

When fetching single records in a loop, each fetch doesn’t reset the record variable. In this example we see that the 6th fetch was unsuccessful because of the %notfound cursor attribute and the value of the record variable was still 5 from the preceding fetch. So we exit the loop as soon as the cursor %notfound attribute is true to avoid processing the last record twice.

With bulk collect limit, the collection is reset with every fetch. The third fetch was unsuccessful, leaving the collection empty with a count of 0. Even though the second fetch was successful, the %notfound cursor attribute was set to true. If we used %notfound as the exit condition we would not have processed the second set of records. The fact that the collection is empty after an unsuccessful fetch is the correct exit condition when using bulk collect with a limit.

In the following example we can see that this works whether or not the total number of records is a multiple of the limit value.

Prompt Bulk Collect Limit: Exit Before Processing When Count = 0
declare
    cursor c_items(p_count in number) is
        select level as n
        from dual
        connect by level <= p_count;
    type t_items is table of c_items%rowtype;
    l_items t_items;
    i number := 0;
begin
    
    dbms_output.put_line('Total items is not a multiple of limit');
    i := 0;
    open c_items(5);    
    loop
        i := i + 1;
        fetch c_items bulk collect into l_items limit 3;
        dbms_output.put('bulk fetch ' || i 
            || ': l_items.count = ' || l_items.count 
            || case when l_items.count > 0 then ' values ' end);
        if l_items.count = 0 then 
            dbms_output.put_line(' exit on empty');
        end if;
        exit when l_items.count = 0;
        for i in indices of l_items loop
            dbms_output.put(l_items(i).n || ' ');
        end loop;
        dbms_output.put_line('processed');                    
    end loop;
    close c_items;
    
    dbms_output.put_line('Total items is a multiple of limit');
    i := 0;
    open c_items(6);    
    loop
        i := i + 1;
        fetch c_items bulk collect into l_items limit 3;
        dbms_output.put('bulk fetch ' || i 
            || ': l_items.count = ' || l_items.count 
            || case when l_items.count > 0 then ' values ' end);
        if l_items.count = 0 then 
            dbms_output.put_line(' exit on empty');
        end if;
        exit when l_items.count = 0;
        for i in indices of l_items loop
            dbms_output.put(l_items(i).n || ' ');
        end loop;
        dbms_output.put_line('processed');                    
    end loop;
    close c_items;

end;

Total items is not a multiple of limit
bulk fetch 1: l_items.count = 3 values 1 2 3 processed
bulk fetch 2: l_items.count = 2 values 4 5 processed
bulk fetch 3: l_items.count = 0 exit on empty
Total items is a multiple of limit
bulk fetch 1: l_items.count = 3 values 1 2 3 processed
bulk fetch 2: l_items.count = 3 values 4 5 6 processed
bulk fetch 3: l_items.count = 0 exit on empty

In this case, the third fetch is always unsuccessful. The collection count after the second fetch was less than the limit whenever the total records were not a multiple of the limit. When the total records were a multiple of the limit, all successful fetches always had a count equal to the limit. Could these facts be used to exit the loop without a final unsuccessful fetch? We will definitely have to move the exit condition after the processing code, so that all the records are processed even when the fetch gets less rows than the limit. Exiting when the count is less than or equal to the limit would always exit after the first fetch, so that would not work. Let’s see what happens if we exit after processing when the count is less than the limit.

Prompt Bulk Collect Limit: Exit After Processing When Count < Limit
declare
    cursor c_items(p_count in number) is
        select level as n
        from dual
        connect by level <= p_count;
    type t_items is table of c_items%rowtype;
    l_items t_items;
    i number := 0;
begin
    
    dbms_output.put_line('Total items is not a multiple of limit');
    i := 0;
    open c_items(5);    
    loop
        i := i + 1;
        fetch c_items bulk collect into l_items limit 3;
        dbms_output.put('bulk fetch ' || i 
            || ': l_items.count = ' || l_items.count 
            || case when l_items.count > 0 then ' values ' end);
        for i in indices of l_items loop
            dbms_output.put(l_items(i).n || ' ');
        end loop;
        dbms_output.put_line(case when l_items.count > 0 then 'processed ' end 
            || case when l_items.count < 3 then ' exit on count < limit' end);
        exit when l_items.count < 3;
    end loop;
    close c_items;
    
    dbms_output.put_line('Total items is a multiple of limit');
    i := 0;
    open c_items(6);    
    loop
        i := i + 1;
        fetch c_items bulk collect into l_items limit 3;
        dbms_output.put('bulk fetch ' || i 
            || ': l_items.count = ' || l_items.count 
            || case when l_items.count > 0 then ' values ' end);
        for i in indices of l_items loop
            dbms_output.put(l_items(i).n || ' ');
        end loop;
        dbms_output.put_line(case when l_items.count > 0 then 'processed ' end 
            || case when l_items.count < 3 then ' exit on count < limit' end);
        exit when l_items.count < 3;
    end loop;
    close c_items;

end;

Total items is not a multiple of limit
bulk fetch 1: l_items.count = 3 values 1 2 3 processed 
bulk fetch 2: l_items.count = 2 values 4 5 processed  exit on count < limit
Total items is a multiple of limit
bulk fetch 1: l_items.count = 3 values 1 2 3 processed 
bulk fetch 2: l_items.count = 3 values 4 5 6 processed 
bulk fetch 3: l_items.count = 0 exit on count < limit

Our clever optimization saved us a single unsuccessful fetch, in some cases. Whenever the total record count was a multiple of the limit there will still be a final unsuccessful fetch. The cost of an unsuccessful fetch is just one context switch, so this optimization will sometimes help, in a very slight way. If there were other efforts involved with the processing code that ran even if the collection was empty, the benefits of occasionally avoiding a single unsuccessful fetch would be lost. It is better to always exit the loop immediately when the collection count is zero.

Bulk Collect Limit Performance

Using limit with bulk collect can be used to change the memory requirements during processing of large resultsets. Lots of rows take lots of memory, and the limit clause provides the ability to tune this footprint. If a single row takes n bytes of memory, bulk fetching 100 rows takes 100 * n bytes, and bulk fetching a million rows at once will take 1 million * n bytes.

Lets confirm that the limit clause is working to control the number of fetches and the number of rows fetched.

prompt Bulk Bind Limit Fetch Comparison
declare
    l_records number := 500000;
    l_limit number;
    l_fetches_previous number;
    l_fetches number;
    cursor cur_data is
        select 
            'bulk_bind_limit_fetches' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;   
    type t_rows is table of cur_data%rowtype index by pls_integer;
    l_row cur_data%rowtype;
    l_row_table t_rows;
begin
    l_fetches_previous := get_sql_engine_fetches('bulk_bind_limit_fetches');
    open cur_data;
    loop
        fetch cur_data into l_row;
        exit when cur_data%notfound;
    end loop;
    close cur_data;
    
    l_fetches := get_sql_engine_fetches('bulk_bind_limit_fetches') - l_fetches_previous;
    dbms_output.put_line('fetching row by row: ' 
        || l_fetches || ' fetches for ' || l_records || ' records');

    l_fetches_previous := get_sql_engine_fetches('bulk_bind_limit_fetches');
    open cur_data;
    fetch cur_data bulk collect into l_row_table;
    close cur_data;
    l_fetches := get_sql_engine_fetches('bulk_bind_limit_fetches') - l_fetches_previous;
    dbms_output.put_line('bulk collect no limit: ' 
        || l_fetches || ' fetches for ' || l_records || ' records');

    l_fetches_previous := get_sql_engine_fetches('bulk_bind_limit_fetches');
    l_limit := 100;
    open cur_data;
    loop
        fetch cur_data bulk collect into l_row_table limit l_limit;
        exit when l_row_table.count = 0;
    end loop;
    close cur_data;
    l_fetches := get_sql_engine_fetches('bulk_bind_limit_fetches') - l_fetches_previous;
    dbms_output.put_line('bulk collect limit ' || l_limit || ': ' 
        || l_fetches || ' fetches for ' || l_records || ' records');

    l_fetches_previous := get_sql_engine_fetches('bulk_bind_limit_fetches');
    l_limit := 1000;
    open cur_data;
    loop
        fetch cur_data bulk collect into l_row_table limit l_limit;
        exit when l_row_table.count = 0;
    end loop;
    close cur_data;
    l_fetches := get_sql_engine_fetches('bulk_bind_limit_fetches') - l_fetches_previous;
    dbms_output.put_line('bulk collect limit ' || l_limit || ': ' 
        || l_fetches || ' fetches for ' || l_records || ' records');

    l_fetches_previous := get_sql_engine_fetches('bulk_bind_limit_fetches');
    l_limit := 10000;
    open cur_data;
    loop
        fetch cur_data bulk collect into l_row_table limit l_limit;
        exit when l_row_table.count = 0;
    end loop;
    close cur_data;
    l_fetches := get_sql_engine_fetches('bulk_bind_limit_fetches') - l_fetches_previous;
    dbms_output.put_line('bulk collect limit ' || l_limit || ': ' 
        || l_fetches || ' fetches for ' || l_records || ' records');

end;

fetching row by row: 500001 fetches for 500000 records
bulk collect no limit: 1 fetches for 500000 records
bulk collect limit 100: 5001 fetches for 500000 records
bulk collect limit 1000: 501 fetches for 500000 records
bulk collect limit 10000: 51 fetches for 500000 records

Clearly, the limit clause allows us to control the amount of data that is bulk collected with each call. These limited bulk fetches will definitely have a better memory footprint, but does this come with a performance cost? Each bulk collect limit call will still involve a context switch and the out-bind collection variable has to be returned to the PL/SQL engine. Reviewing the timing will show if the memory footprint tuning is worth the effort.

prompt Bulk Bind Limit Timing Comparison
declare
    l_records number := 500000;
    l_limit number;
    l_start timestamp;
    cursor cur_data is
        select 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;   
    type t_rows is table of cur_data%rowtype index by pls_integer;
    l_row cur_data%rowtype;
    l_row_table t_rows;
begin
    l_start := localtimestamp;
    open cur_data;
    loop
        fetch cur_data into l_row;
        exit when cur_data%notfound;
    end loop;
    close cur_data;
    print_timing(l_start, l_records, 'open, fetch, close');
        
    l_start := localtimestamp;
    open cur_data;
    fetch cur_data bulk collect into l_row_table;
    close cur_data;
    print_timing(l_start, l_records, 'bulk collect no limit');

    l_start := localtimestamp;
    l_limit := 100;
    open cur_data;
    loop
        fetch cur_data bulk collect into l_row_table limit l_limit;
        exit when l_row_table.count = 0;
    end loop;
    close cur_data;
    print_timing(l_start, l_records, 'bulk collect limit ' || l_limit);

    l_start := localtimestamp;
    l_limit := 1000;
    open cur_data;
    loop
        fetch cur_data bulk collect into l_row_table limit l_limit;
        exit when l_row_table.count = 0;
    end loop;
    close cur_data;
    print_timing(l_start, l_records, 'bulk collect limit ' || l_limit);

    l_start := localtimestamp;
    l_limit := 10000;
    open cur_data;
    loop
        fetch cur_data bulk collect into l_row_table limit l_limit;
        exit when l_row_table.count = 0;
    end loop;
    close cur_data;
    print_timing(l_start, l_records, 'bulk collect limit ' || l_limit);

end;

1.125 seconds for 500000 rows: open, fetch, close
0.32 seconds for 500000 rows: bulk collect no limit
0.219 seconds for 500000 rows: bulk collect limit 100
0.204 seconds for 500000 rows: bulk collect limit 1000
0.211 seconds for 500000 rows: bulk collect limit 10000

Combined with the limit clause, bulk collect shows definite performance improvements. I ran the above test with a half million rows in the resultset so that we can see more clearly what happens with bigger limit values. Bulk collect without a limit shows a 3.5x improvement in performance over row by row processing. Bulk collect with a limit is approximately 5x faster than ‘slow by slow’ processing, and 1.5x faster than using bulk collect without a limit. Using different values for the limit in this simple case doesn’t increase performance significantly. If you play around with setting different limit values you will find that the benefits generally occur around the 100 row limit.

Cursor For Loop Optimization

In the puzzle at the end of Cursor Basics Part 1, changing a single character in the test script impacted the performance of a cursor for loop in comparison to row by row fetching with the open, close, fetch method. Without this change, the performance of the cursor for loop was comparable to a bulk collect operation. The 100 row bulk collect limit ‘sweet spot’ is the key to solving the puzzle, and the reason bulk collect is not always better than the simple cursor for loop. This becomes apparent when we review the timing of the cursor for loop in contrast to the open, fetch, close method and using bulk collect with a limit.

prompt Cursor For Loop Timing Comparison
declare
    l_records number := 500000;
    l_start timestamp;
    cursor cur_data is
        select 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;   
    type t_rows is table of cur_data%rowtype index by pls_integer;
    l_row cur_data%rowtype;
    l_row_table t_rows;
begin
    l_start := localtimestamp;
    open cur_data;
    loop
        fetch cur_data into l_row;
        exit when cur_data%notfound;
    end loop;
    close cur_data;
    print_timing(l_start, l_records, 'open, fetch, close');
        
    l_start := localtimestamp;
    open cur_data;
    loop
        fetch cur_data bulk collect into l_row_table limit 100;
        exit when l_row_table.count = 0;
    end loop;
    close cur_data;
    print_timing(l_start, l_records, 'bulk collect limit 100');

    l_start := localtimestamp;
    for r in cur_data loop
        null;
    end loop;
    print_timing(l_start, l_records, 'cursor for loop');

end;

1.108 seconds for 500000 rows: open, fetch, close
0.212 seconds for 500000 rows: bulk collect limit 100
0.218 seconds for 500000 rows: cursor for loop

The cursor for loop offers about the same performance as bulk collect with a limit, with a lot less code. The explanation becomes clear when we examine the fetch counts of the various approaches.

prompt Cursor For Loop Fetch Comparison
declare
    l_records number := 500000;
    l_fetches_previous number;
    l_fetches number;
    cursor cur_data is
        select 
            'cursor_for_loop_fetches' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;   
    type t_rows is table of cur_data%rowtype index by pls_integer;
    l_row cur_data%rowtype;
    l_row_table t_rows;
begin
    l_fetches_previous := get_sql_engine_fetches('cursor_for_loop_fetches');
    open cur_data;
    loop
        fetch cur_data into l_row;
        exit when cur_data%notfound;
    end loop;
    close cur_data;    
    l_fetches := get_sql_engine_fetches('cursor_for_loop_fetches') - l_fetches_previous;
    dbms_output.put_line('fetching row by row: ' 
        || l_fetches || ' fetches for ' || l_records || ' records');

    l_fetches_previous := get_sql_engine_fetches('cursor_for_loop_fetches');
    open cur_data;
    loop
        fetch cur_data bulk collect into l_row_table limit 100;
        exit when l_row_table.count = 0;
    end loop;
    close cur_data;
    l_fetches := get_sql_engine_fetches('cursor_for_loop_fetches') - l_fetches_previous;
    dbms_output.put_line('bulk collect limit 100: ' 
        || l_fetches || ' fetches for ' || l_records || ' records');

    l_fetches_previous := get_sql_engine_fetches('cursor_for_loop_fetches');
    for r in cur_data loop
        null;
    end loop;
    l_fetches := get_sql_engine_fetches('cursor_for_loop_fetches') - l_fetches_previous;
    dbms_output.put_line('cursor for loop: ' 
        || l_fetches || ' fetches for ' || l_records || ' records');

end;

fetching row by row: 500001 fetches for 500000 records
bulk collect limit 100: 5001 fetches for 500000 records
cursor for loop: 5001 fetches for 500000 records

The cursor for loop has been optimized to bulk prefetch 100 rows at a time without any additional code! For most use cases, we can use the simplicity of the cursor for loop and get the performance gains of bulk binds. Unless the situation calls for a cursor variable or dynamic sql, the cursor for loop still provides the low code solution.

This bulk prefetch is undocumented and can only be seen with plsql_optimize_level of 2 (the default). The 100 row limit may be subject to change in a future version of Oracle, but testing with versions from 12c to 23Free shows that it is still set to 100. If we create a test procedure and modify the optimizer level, we can see how to turn off the optimization. Because of this performance difference, it is important to take advantage of the plsql optimizer and leave it set at the default level of 2 when compiling and testing code that uses cursor for loops.

prompt Cursor For Loop Optimization
create or replace procedure cursor_for_loop_optimization
is
    l_records number := 500000;
    l_start timestamp := localtimestamp;
    l_fetches_previous number;
    l_fetches number;
    cursor cur_data is
        select 
            'cursor_for_loop_optimization' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;   
begin

    l_fetches_previous := get_sql_engine_fetches('cursor_for_loop_optimization');
    for r in cur_data loop
        null;
    end loop;
    l_fetches := get_sql_engine_fetches('cursor_for_loop_optimization') - l_fetches_previous;
    
    dbms_output.put(l_fetches || ' fetches, ');
    print_timing(l_start, l_records, 'cursor for loop, optimize_level ' || $$plsql_optimize_level);

end cursor_for_loop_optimization;

alter procedure cursor_for_loop_optimization compile plsql_optimize_level=0;
begin
    cursor_for_loop_optimization;
end;

alter procedure cursor_for_loop_optimization compile plsql_optimize_level=1;
begin
    cursor_for_loop_optimization;
end;

alter procedure cursor_for_loop_optimization compile plsql_optimize_level=2;
begin
    cursor_for_loop_optimization;
end;


500001 fetches, 0.992 seconds for 500000 rows: cursor for loop, optimize_level 0
500001 fetches, 0.999 seconds for 500000 rows: cursor for loop, optimize_level 1
5001 fetches, 0.333 seconds for 500000 rows: cursor for loop, optimize_level 2

Because this optimization is implemented behind the scenes, I prefer the cursor for loop for most situations because of its elegance and simplicity.

More complex use cases with cursor variables and dynamic sql still require the extra code that comes with implementing bulk binds. ETL transformations done with pipelined functions with ref cursor input parameters will perform better with bulk binding and cannot implement cursor for loops. Situations involving large recordsets that need to be processed in sets are also a case where the bulk bind code is preferrable to the cursor for loop. We will look at these more when we cover bulk in-binds and the forall statement. It is always good for a practical Oracle developer to know about all of the possible approaches and employ the method best suited to each use case.

In the final part of this article on cursor basics we will explore a completely new approach to iterating cursors that was introduced with Oracle 21c: the cursor iteration control.

–Anthony Harper

Puzzle

Can the following block of code be rewritten to iterate both cursors simultaneously?

prompt Challenge: Iterate Multiple Cursors With One For Loop Without Using Union
create table birthdays (
    person varchar2(50),
    birth_date date);

create table events (
    event_name varchar2(50),
    event_date date);
    
begin
    insert into birthdays values ('Martin Luther King, Jr', date '1929-01-15');
    insert into birthdays values ('Edgar Frank Codd', date '1923-08-19');
    insert into birthdays values ('Frida Kahlo', date '1907-07-06');    
    insert into birthdays values ('Ludwig Wittgenstein', date '1889-04-26');
    insert into birthdays values ('Mohandas K Ghandhi', date '1869-10-02');
    insert into events values ('Valentina Tereshkova Was The First Woman In Space', '1963-06-16');
    insert into events values ('Neil Armstrong Walked On The Moon', '1969-07-20');
    insert into events values ('Oracle Corporation Was Founded', '1977-06-16');
    insert into events values ('Oracle 23c Free Announced', '2023-04-03');
    commit;
end;
/

declare
    cursor c_birthdays is
        select person || ' was born.', birth_date 
        from birthdays 
        order by birth_date;
        
    cursor c_events is
        select event_name, event_date 
        from events 
        order by event_date;
        
    type t_reminders is table of c_events%rowtype;
    l_birthdays t_reminders;
    l_events t_reminders;
    l_reminders t_reminders;
begin
    
    open c_birthdays;
    fetch c_birthdays bulk collect into l_birthdays;
    close c_birthdays;
    
    open c_events;
    fetch c_events bulk collect into l_events;
    close c_events;
    
    l_reminders := l_birthdays multiset union l_events;
    
    for i in indices of l_reminders.count loop
        dbms_output.put_line(to_char(l_reminders(i).event_date, 'fmMonth DD, YYYY":  "') || l_reminders(i).event_name);
    end loop;
    
end;
/

October 2, 1869:  Mohandas K Ghandhi was born.
April 26, 1889:  Ludwig Wittgenstein was born.
July 6, 1907:  Frida Kahlo was born.
August 19, 1923:  Edgar Frank Codd was born.
January 15, 1929:  Martin Luther King, Jr was born.
June 16, 1963:  Valentina Tereshkova Was The First Woman In Space
July 20, 1969:  Neil Armstrong Walked On The Moon
June 16, 1977:  Oracle Corporation Was Founded
April 3, 2023:  Oracle 23c Free Announced