Batch window shrinking. ETL job running 60 seconds when it should run 9. Three million rows and the clock is ticking.
The fix is direct path insert — and most DBAs never configure it deliberately.
Here’s the complete breakdown with a live benchmark proving the difference.
The Problem: Three Scenarios Where INSERT Kills Performance
- Loading millions of rows from flat files (CSV) into Oracle
- Populating a large table from another large table
- Bulk INSERT statements missing their batch window
All three have the same root cause — conventional insert path. All three have the same fix.
60-Second Fix Matrix
| Loading Scenario | Fastest Option |
|---|---|
| Insert from another large table | INSERT /*+ APPEND */ INTO target SELECT * FROM source; |
| Create new table from existing data | CREATE TABLE target NOLOGGING AS SELECT * FROM source; |
| Bulk PL/SQL array insert | FORALL with INSERT /*+ APPEND_VALUES */ |
| Need parallel load | Enable parallel DML + PARALLEL hint with APPEND |
Conventional vs Direct Path: What Oracle Actually Does
| Factor | Conventional Path | Direct Path |
|---|---|---|
| Block selection | Searches for partially filled blocks | Always writes above HWM |
| Buffer cache | Writes through buffer cache | Bypasses buffer cache entirely |
| Locking | Lock/unlock per array of rows | Lock once at start, unlock at end |
| UNDO | Full UNDO generated | Reduced — not eliminated |
| Index maintenance | Row by row index update | Mini indexes built and merged in bulk |
Important correction from common misconception:
Direct path insert reduces UNDO for table data blocks — it does not eliminate UNDO entirely. Oracle still generates UNDO for transaction metadata, dictionary changes, and index-related operations.
Redo behavior also depends on logging mode, NOLOGGING setting, force logging, and Data Guard requirements. Direct path insert does not automatically mean no redo.
Direct Path Insert Syntax
Three forms — pick based on statement type:
-- Form 1: Create table from another table (fastest for new tables)
CREATE TABLE dpathinsert
NOLOGGING
AS
SELECT * FROM source_table;
-- Form 2: Insert from SELECT — use APPEND hint
INSERT /*+ APPEND */ INTO dpathinsert
SELECT ROWNUM, 'DBA_DATAVERSE_' || ROWNUM
FROM dual
CONNECT BY ROWNUM <= 3000000;
COMMIT;
-- Form 3: Bulk VALUES insert — use APPEND_VALUES
INSERT /*+ APPEND_VALUES */ INTO dpathinsert
VALUES (:id, :name);
COMMIT;Critical rule:
APPEND→ use withINSERT INTO ... SELECTAPPEND_VALUES→ use withINSERT INTO ... VALUES— bulk PL/SQL FORALL only, not single-row OLTP inserts- Wrong hint = silent fallback to conventional path, no error thrown
APPEND does not make INSERT parallel. For parallel DML, explicitly enable it:
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(dpathinsert 4) */
INTO dpathinsert
SELECT /*+ PARALLEL(source_table 4) */ *
FROM source_table;
COMMIT;Live Benchmark on Oracle 19c (pdvddv on ddv01)
Setup
ALTER SESSION SET CONTAINER = pdvddv;
CREATE TABLE dpathinsert (
id NUMBER,
name VARCHAR2(100)
);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DPATHINSERT');Baseline — HWM and Direct Path Write Stats
-- Check blocks below HWM
SELECT blocks, empty_blocks
FROM user_tables
WHERE table_name = 'DPATHINSERT';
-- Result:
-- BLOCKS EMPTY_BLOCKS
-- ------- ------------
-- 5 0
-- Check direct path write baseline
SELECT name, value
FROM v$mystat s
JOIN v$statname n ON s.statistic# = n.statistic#
WHERE n.name = 'physical writes direct';
-- Result:
-- NAME VALUE
-- ---------------------- -----
-- physical writes direct 0Conventional Insert
SET TIMING ON
INSERT INTO dpathinsert
SELECT ROWNUM, 'DBA_DATAVERSE_' || ROWNUM
FROM dual
CONNECT BY ROWNUM <= 3000000;
COMMIT;
-- Check direct path writes after conventional insert
SELECT name, value
FROM v$mystat s
JOIN v$statname n ON s.statistic# = n.statistic#
WHERE n.name = 'physical writes direct';
-- Result:
-- NAME VALUE
-- ---------------------- -----
-- physical writes direct 0
-- Zero direct path writes — Oracle used buffer cache throughoutDirect Path Insert
TRUNCATE TABLE dpathinsert;
SET TIMING ON
INSERT /*+ APPEND */ INTO dpathinsert
SELECT ROWNUM, 'DBA_DATAVERSE_' || ROWNUM
FROM dual
CONNECT BY ROWNUM <= 3000000;
COMMIT;
-- Check HWM after direct path insert
SELECT blocks, empty_blocks
FROM user_tables
WHERE table_name = 'DPATHINSERT';
-- Result:
-- BLOCKS EMPTY_BLOCKS
-- ------- ------------
-- 9 0
-- HWM moved from 5 to 9 blocks — Oracle wrote above the mark
-- Check direct path writes
SELECT name, value
FROM v$mystat s
JOIN v$statname n ON s.statistic# = n.statistic#
WHERE n.name = 'physical writes direct';
-- Result:
-- NAME VALUE
-- ---------------------- -----
-- physical writes direct 1
-- Direct path write confirmedPerformance Comparison: 3 Million Rows
| Method | Min Time | Max Time | Consistent? |
|---|---|---|---|
| Conventional INSERT | 21 seconds | 60 seconds | No — buffer cache contention varies |
| Direct Path INSERT | 9 seconds | 9 seconds | Yes — flat every run |
The variance in conventional insert (21s to 60s) is buffer cache contention from concurrent sessions. Direct path bypasses that entirely — which is why the number stays constant regardless of database load.
Bottom line: Direct path insert delivered a 2.3x improvement over the best conventional insert time and a 6.7x improvement over the worst case.
Production Warnings — Read Before Adding APPEND
-- After direct path insert, always COMMIT immediately
-- Table is locked for other DML until COMMIT is issued
INSERT /*+ APPEND */ INTO dpathinsert SELECT ...;
COMMIT; -- Never skip thisUse direct path insert when:
- Loading millions of rows into staging, reporting, or archive tables
- Running ETL batch loads with a controlled window
- Creating new tables from existing data (CTAS)
- Space reuse is not a priority
Avoid direct path insert when:
- Table is actively used by concurrent OLTP sessions
- Existing free space must be reused (space-constrained environments)
- Table has triggers — direct path bypasses them silently
- Only inserting a few rows — overhead not justified
- Data Guard or recovery policy requires full redo with force logging enabled
Verify Direct Path Insert Worked
Always confirm. Do not assume the hint was honored:
-- Check physical writes direct in same session
SELECT name, value
FROM v$mystat s
JOIN v$statname n ON s.statistic# = n.statistic#
WHERE n.name LIKE 'physical writes direct%';
-- If value > 0 after insert — direct path was used
-- If value = 0 — hint was silently ignored, check for triggers, constraints
-- Check HWM movement after load
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DPATHINSERT');
SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = 'DPATHINSERT';Version Note
Validated on Oracle 19c (pdvddv on ddv01). Direct path insert behavior is consistent across Oracle 12c, 19c, 21c, and Oracle 26ai. The APPEND and APPEND_VALUES hints work identically across all versions. NOLOGGING behavior and Data Guard interaction should be validated per environment.
This is how we can Speed up Inserts in our database.
Other Worth Reading:
Oracle Documents:
1 thought on “Oracle INSERT Too Slow? Fix It Now”