Using PL/SQL Bulk Inserts to Improve SQL Insert Speed Oracle Tips by Burleson Consulting

One of the inherent issues with SQL inserts is that an individual insert statement must be generated and executed for every row that is added to the database.  The PL/SQL language has a procedure where bulk insertions can be done far faster than the traditional row-at-a-time method.

Important note:  Staring in Oracle 11g release 2, we see the new append_values hint., which is 1./3 faster than a vanilla forall bulk operator!

When inserting rows in PL/SQL, developers often place the insert statement inside a FOR loop.  To insert 1,000 rows using the FOR loop, there would be 1,000 context switches between PL/SQL and the Oracle library cache. Oracle8i allows you to do bulk binds using the PL/SQL FORALL loop, which requires only one context switch.  This is achieved by the FORALL statement passing an entire PL/SQL table to the SQL engine in a single step.  Internal tests show that this process results in substantial performance increases over traditional methods.

Here's how it works. This example, assume that a Pro*C program is calling a PL/SQL function for a bulk insert.

1.      In your PL/SQL package, you create some global PL/SQL table variables for each column of the your table.  Here is an example of the PL/SQL type definition:

type
   cust_key_array
is table of
   customer.cust_key%TYPE
INDEX BY BINARY_INTEGER;

2.      Next, you create a PL/SQL procedure with parameters of type PL/SQL tables:

  PROCEDURE customer_insert
    (p_cust_key IN           customer.cust_key%TYPE,
     p_current_state IN      current_state_array,
     p_anno_user_category IN anno_user_category_array,
     p_anno_name IN          anno_name_array,
     p_color IN              color_array)

3.      To invoke the procedure, you pass a C array for each parameter that is defined in the PL/SQL table.

4.      To properly perform the bulk insert you must get the number of items passed in by the Pro*C program.  You can do this by checking one of the PL/SQL parameters using the PL/SQL table built-in function COUNT.

lv_rowcount := p_cust_key.COUNT;

5.      Finally, we can insert using the FORALL statement, using the lv_rowcount parameter as the terminator of the FORALL loop.

      FORALL i IN 1..lv_rowcount
        INSERT INTO customer
                 (cust_key,
                  page_seq_nbr,
                  user_unique_id,
                  in_page_seq_nbr,
                  date_time,
                  posx,
                  posy,
                  length,
                  current_state,
                  anno_user_category,
                  anno_name,
                  color)
          VALUES (p_cust_key,
                  p_page_seq_nbr(i),
                  p_user_unique_id,
                  p_in_page_seq_nbr(i),
                  SYSDATE,
                  p_posx(i),
                  p_posy(i),
                  p_length(i),
                  p_current_state(i),
                  p_anno_user_category(i),
                  p_anno_name(i),
                  p_color(i));

In sum, the FORALL clause in PL/SQL can speed-up SQL DML that is called from an external programming language such as Pro*C or Pro*Cobol.  You can also use the dbms_sql package for bulk inserts of Oracle rows.

Conclusion

This chapter has reviewed the fundamentals for tuning Oracle SQL DML statements. The main points of this chapter include these:

• There is only one SQL hint that assists with the speed of DML, the append hint.

• The append hint tells Oracle to bypass the table freelists and place inserted rows on fresh data blocks. This is done by raising the high-water mark for the table, thereby placing the new rows at the end of the table.

• Oracle DML performance is heavily dependent on the Oracle table storage parameters pctfree, pctused, and freelists.

• The pctfree parameter controls freelist unlink operations, and a too-high setting for pctfree can precipitate row chaining at update time.

• The pctused parameter controls freelist relink operations. A too-high setting for pctused will cause data blocks to be relinked when they can only accept a few new rows before being unlinked.

• The freelists parameter controls concurrent DML. The setting for freelists should always be set to the high-water mark for concurrent DML operations against the object.

• A shortage of freelists commonly causes buffer busy waits because DML tasks must enqueue to access the segment header block.

• Oracle update DML often requires correlated subqueries. These subqueries cannot be rewritten as standard equi-joins because update statements may only have one table name in the from clause. Hence, the parallel hint can improve the speed of resulting full-table scans.

• For large-volume DML with SQL*Loader, Oracle import, or batch loads, referential integrity constraints can be temporarily disabled and later re-enabled to improve performance.

• Oracle indexes are often dropped and rebuilt after bulk inserts, deletes, or updates. This improves the overall speed of the bulk operations and ensures that all B-tree indexes are well balanced following the bulk operation.
   

Next, let’s turn our attention to the tuning of Oracle SQL with the use of temporary tables. By giving your developers the ability to create temporary tables, you can radically improve the speed of certain types of Oracle SQL statements.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.