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


本文转自海天一鸥博客园博客,原文链接:http://www.cnblogs.com/sgsoft/archive/2011/01/06/1927053.html,如需转载请自行联系原作者

相关文章
|
10月前
|
关系型数据库 MySQL 数据库
INSERT IGNORE与INSERT INTO的区别
INSERT IGNORE与INSERT INTO的区别
234 0
|
10月前
|
SQL
DML(insert与delete)
DML(insert与delete)
59 0
|
SQL 关系型数据库 MySQL
Select for update使用详解
前言 近期开发与钱相关的项目,在高并发场景下对数据的准确行有很高的要求,用到了for update,故总结一波以便日后留恋。 for update的使用场景 如果遇到存在高并发并且对于数据的准确性很有要求的场景,是需要了解和使用for update的。 比如涉及到金钱、库存等。一般这些操作都是很长一串并且是开启事务的。如果库存刚开始读的时候是1,而立马另一个进程进行了update将库存更新为0了,而事务还没有结束,会将错的数据一直执行下去,就会有问题。所以需要for upate 进行数据加锁防止高并发时候数据出错。
2386 0
|
关系型数据库 PostgreSQL
PostgreSQL merge insert(upsert/insert into on conflict) 如何区分数据是INSERT还是UPDATE
标签 PostgreSQL , merge insert , upsert , insert into on conflict , 区分 insert update , xmin , xmax 背景 使用insert into on conflict update语法,可以支持UPSERT的功能,但是到底这条SQL是插入的还是更新的呢?如何判断 通过xmax字段的值是否不为0,可以判断,如果是UPDATE,XMAX里面会填充更新事务号。
2208 0
|
关系型数据库 MySQL 数据库
mysql字符集,insert,update,delete,select
发现有错误:数据太长了。//查看数据库的所有编码:show variables like 'character%';-----+| character_set_client     | utf8    设置客户端的字符集     || character_set_connection | utf8    设置连接的字符集     || character_set_database   | ut
1131 0