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.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
