How to speed up Oracle large batch data processing ____oracle

First, to improve the DML operation method:

In short,

1, suspend the index, restore after the update. Avoid the rebuilding of indexes in the process of updating.

2, batch update, every update some records in time to submit action. Avoid large amounts of rollback segments and or temporary tablespaces.

3, create a temporary large table space to respond to these update actions.

4, batch update, every update some records in time to submit action. Avoid large amounts of rollback segments and or temporary tablespaces.

5, create a temporary large table space to respond to these update actions.

6, increase the sort buffer

Alter session set sort_area_size=100000000;

INSERT INTO TableB select * from TableA;

Commit

If the update is an indexed field, it will involve the rebuilding of the index, which will not increase the speed of indexing, but may reduce the update speed.

Because the index can increase the query speed of the data in the update, the speed reduction caused by rebuilding the index is not very significant.

Oracle Optimization modification parameters can only improve performance by up to 15%, most of which are optimized for SQL statements.

Update is generally slower than insert:

Some suggestions:

1, if the updated data volume close to the entire table, should not use index and should be full table scan

2, reduce unnecessary index, because update table usually need update index

3, if your server has more than one CPU, using parellel hint, can greatly improve the efficiency

In addition, the parameter of the table is very important, for the updated very frequently table, we recommend to increase the value of pctfree to ensure that there is enough space in the data block for update, thereby reducing chained_rows.

Second, various batch DML operations:

(1), Oracle Bulk copy:

Set ArraySize 20

Set Copycommit 5000

Copy from Username/password@oraclename append table_name1

Using a SELECT * from Table_name2;

(2), the General insertion mode:

INSERT INTO T1 select * from T;

To increase the speed, you can use the following methods to reduce the log generated during the insert process:

ALTER TABLE T1 nologging;

INSERT INTO T1 select * from T;

Commit

(3), CTAs way:

CREATE TABLE T1

As

select * from T;

To increase the speed, you can use the following methods to reduce the log generated during the insert process, and you can set the degree of parallelism:

CREATE TABLE T1 nologging parallel (degree 2) as SELECT * from T;

(4), Direct-path insert:

Insert/*+append*/into T1 select * from T;

Commit

To increase the speed, you can use the following methods to reduce the log generated during the insert process:

ALTER TABLE T1 nologging;

Insert/*+append*/into T1 select * from T;

Direct-path Insert Features:

1, append only in insert ... select ... , like insert/*+ append/into t values (...) This kind of statement is not working. In the update, delete operations, append also does not work.

2, Direct-path will make the database does not record the direct path to import data redo log, will bring trouble to restore.

3, Direct-path directly in the table section of the high watermark above the blank data block to write data, will not reuse the space below the high watermark, will be used to create a certain waste of space, the performance of the query will also have a certain impact. Conventional inserts give priority to using blocks of data that have free space underneath the high watermark. So theoretically direct-path inserts are faster than regular inserts, because Direct-path uses new blocks of data directly, and a regular insert traverses freelist to get available free blocks of data, which is more obvious if combined with nologging.

4. After inserting records in a append manner, a commit can be executed to query the table. Otherwise, an error occurs: ORA-12838: The object cannot be read/modified after being modified in parallel mode.

5. After importing data with Append, if no commit or rollback is made, any DML to the table in other sessions will be blocked (no error), but queries on the table can be performed normally.

6, in the archive mode, to set the table to Nologging, and then the append way to add a record batch, will significantly reduce the number of redo. In non-archive mode, you can reduce the number of redo by not having to set the Nologging property of the table. If there is an index on the table, the append way to add the records in batches does not reduce the number of redo produced on the index, and the number of redo on the index may be larger than the number of redo in the table.

7. Data is inserted directly into data files, bypassing buffer cache and ignoring referential integrity constraints.

8, regardless of whether the table is under Nologging, as long as direct insert, will not generate undo data content.

9. Oracle performs index maintenance on tables with indexes at the end of the Direct-path INSERT operation, thus avoiding rebuild after dropping the index.

10. Direct-path insert requires more space than regular inserts. Because it inserts the data above the high water level. Inserting a non-partitioned table in parallel requires more space because it requires the creation of temporary segments for each parallel thread.

11. During insertion, the database obtains exclusive locks on the table, and the user cannot perform parallel inserts, updates, or deletions on the table, and concurrent index creation and build are not allowed. But you can query in parallel, but the query returns the result set before it was inserted.

(5), parallel DML:

If your server has more than one CPU, using parellel hint, you can greatly improve the efficiency

ALTER session ENABLE PARALLEL DML;

INSERT/*+ PARALLEL (TableA, 2) */into TableA

SELECT * from TableB;

To increase the speed, you can use the following methods to reduce the log generated during the insert process:

INSERT/*+ PARALLEL (TableA, 2) */into TableA nologging

SELECT * from TableB;

Oracle does not open PDML by default and must be manually enabled for DML statements. That requires execution

ALTER TABLE enable parallel DML command.

Parallel DML Features:

1, in parallel DML mode, the default is Direct-path insert, in order to run the parallel DML mode, the following conditions must be met:

A, must be Oracle Enterprise Edition;

b, the parallel DML must be in session to take effect, executing the following SQL statement:

ALTER Session {ENABLE | FORCE} PARALLEL DML;

c, you must specify the parallel properties of the table, either at the time of creation or at another time, or with the "PARALLEL" hint when you insert the operation.

D, in order for the Direct-path insert mode to fail, specify the "Noappend" prompt in the INSERT statement to overwrite the parallel DML pattern.

2, parallel Direct-path Insert to partition table:

Similar to serial Direct-path inserts, each parallel operation is assigned to one or more partitions, and each parallel operation inserts data above the high watermark of the respective partition segment, after which the user can see the updated data.

3, parallel Direct-path insert to non-partition table:

Each parallel execution assigns a new temporary segment and inserts the data into the temporary segment. When a commit runs, the parallel execution coordinator merges the new temporary segment to the main table segment, and the user sees the updated data.

4, Direct-path Insert can use log or do not use log.

5. It is also important to say that parallelism is not an extensible feature, and that it is very prudent to use parallelism in an OLTP environment only if the data warehouse or a small number of tools, such as DBAs, facilitate the full utilization of resources in bulk data operations. In fact, there are a number of limitations to PDML, such as not supporting triggers, referential constraints, advanced replication, and distributed transactions, as well as additional space usage, PDDL.