BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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:

is table of

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

          VALUES (p_cust_key,

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.


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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter