Wednesday, August 17, 2011

Optimizing Oracle SQL INSERT Performance

Below are some tips of how to optimize Oracle Sql Insert:
  • Parallelize load
    • Data Manipulation Language (DML) such as INSERT can be parallelize by Oracle. Parallel execution can speed up large DML operations and is particularly advantageous in data warehousing environments where it's necessary to maintain large summary or historical tables. Standard INSERT statements using a VALUES clause cannot be parallelizedOracle can parallelize only INSERT . . . SELECT . . . FROM statements. The syntax is as below. 
    • ALTER SESSION ENABLE PARALLEL DML;  
    • CREATE TABLE TEST_M94 AS SELECT * FROM M94 WHERE 0 = 1;            
    • INSERT /* parallel (TEST_M94, 4, 1)*/ INTO TEST_M94 SELECT * FROM M94;  
    • ALTER SESSION DISABLE PARALLEL DML;
  • Use append hint
    • If we use append hint we ensure that Oracle will grab fresh blocks by rising the high water-mark of the table. If we are using the parallel hint the append mode is the default so we don't need to specify it. Before 11g R2 append is supported only in INSERT...SELECT and not in VALUE clause. The syntax is as below:
    • INSERT /* + append */ INTO TEST_M94 SELECT * FROM M94
  • Use nologging
    • The keyword NOLOGGING tells Oracle not to enter anything in the redo log files. Ideally, when any SQL command is executed, Oracle keeps on writing the events in the redo log file so that if anything goes wrong, Oracle can rollback the changes. Hence adding the NOLOGGING parameter will ensure that you save on the time that oracle spends to log events.
  • Disable 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.
  • Use large block size
    • If the block size is larger it reduce the I/O because more rows fits in that block before the block gets full.

No comments:

Post a Comment