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.
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
