Large DML insert/update hanging tips

 

Expert Oracle Tips by Burleson Consulting
August 24, 2010

Question:  We were trying to insert approximately 76 million rows with about 4 gigabytes of space with a batch job when the insert operation failed with the archive log error.  What are the best practices for performing large batch insert jobs to avoid hanging?
Answer: When you run a large batch insert or updates job you risk aborting with:

1 – Insert aborts with a ORA-01555 snapshot too old

2 - Insert hangs when your archive redo log directory becomes full.

There are several approaches to performing large batch update or tuning insert jobs:

• Divide and Conquer:  Make the job re-startable and commit every 1 5 minutes to release held rollback segments (undo logs).
• Check space in archived redo log filesystem:  Make sure that you have enough spaced to hold all of the new archived redo logs.  See my notes on monitoring Oracle redo log activity.
• Dedicated undo: Assign a giant, dedicated rollback segment to the batch job, large enough to hold all of the before images for any updates.
• Parallelize the insert job:  There are two types of parallelism for large DML jobs: 
  (1) You can use parallel DML, or
  (2) submit multiple simultaneous insert jobs, making sure to you have enough freelists allocated to the table to prevent buffer busy waits. 
  Multiple freelists add additional segment header blocks, removing the bottleneck.  You can also use Automatic Segment Space Management (bitmap freelists) to support parallel DML, but ASSM has some limitations.
• Bulk DML: Consider using PL/SQL bulk operators to improve load speed by reducing context switching.
• Consider NOLOGGING: Take a full backup first and run the insert with the NOLOGGING clause.  Note:  INSERT APPEND supports only the subquery syntax of the INSERT statement, not the VALUES clause. For more information on the subquery syntax of INSERT statements see Oracle nologging tips.
• Use insert append: Using the “append” hint to your insert ensures that you always grab a fresh, dead-empty block from your freelists.  If you are doing parallel DML, the Append mode is the default and you don't need to specify an APPEND hint. 
• Disable/drop indexes:  It's far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.

REF:Oracle Tuning- The Definitive Reference Second Edition