Large DML insert/update hanging tips

Source: Internet
Author: User
Document directory
  •  

Expert Oracle tips by Burleson Consulting
August 24,201 0

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 faster Ming large batch insert jobs to avoid hanging?
Answer:When you run a large batch insert or updates job you risk aborting:

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 specify Ming 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 switching 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.