Performance Tuning

sqlldr performance spans five levers: load path choice, index management, redo minimization, I/O buffers, and parallelism. Work through them in order — the highest-impact changes are at the top.

1. Choose direct path first

Direct path bypasses the SQL layer, buffer cache, and undo generation:

sqlldr userid=dw_load/pw control=fact_sales.ctl DIRECT=TRUE

Typical speedup: 5–20× over conventional path for bulk loads. Use conventional only when triggers must fire, FKs must be validated at load time, or the table is clustered.

2. Disable indexes before loading

The single biggest win for large loads — avoid per-row index maintenance:

# Mark all indexes unusable before load
sqlldr userid=dw_load/pw control=fact.ctl direct=true SKIP_INDEX_MAINTENANCE=TRUE

# Rebuild after
sqlplus dw_load/pw @rebuild_indexes.sql

-- rebuild_indexes.sql
ALTER INDEX dw.fact_sales_pk          REBUILD NOLOGGING PARALLEL 8;
ALTER INDEX dw.fact_sales_date_ix     REBUILD NOLOGGING PARALLEL 8;
ALTER INDEX dw.fact_sales_customer_ix REBUILD NOLOGGING PARALLEL 8;

One index rebuild over the full dataset is dramatically faster than maintaining the index for every inserted row.

3. Minimize redo

-- In the control file, before LOAD DATA
UNRECOVERABLE
LOAD DATA
...

Or set the table to NOLOGGING mode:

ALTER TABLE dw.fact_sales NOLOGGING;
-- run sqlldr direct ...
ALTER TABLE dw.fact_sales LOGGING;   -- restore afterwards

UNRECOVERABLE suppresses redo for data blocks (index redo is still written). Take a backup immediately after.

4. Tune I/O buffers

For conventional path, match BINDSIZE and READSIZE:

sqlldr userid=hr/hr control=orders.ctl \
       bindsize=33554432 readsize=33554432 rows=20000

Parameter	Purpose	Recommended
BINDSIZE	Bind array size in bytes	32–256 MB
READSIZE	File read buffer in bytes	Same as BINDSIZE
ROWS	Rows per commit	10,000–50,000
COLUMNARRAYROWS	Override array row count	Rarely needed

For direct path, ROWS doesn't affect commits (direct writes are block-level), but READSIZE still controls I/O throughput:

sqlldr userid=hr/hr control=orders.ctl direct=true readsize=67108864

5. Parallel direct path

Split the input file into shards and run simultaneous sqlldr sessions:

# Session 1
sqlldr userid=dw_load/pw control=fact_p1.ctl direct=true parallel=true &

# Session 2
sqlldr userid=dw_load/pw control=fact_p2.ctl direct=true parallel=true &

# Session 3
sqlldr userid=dw_load/pw control=fact_p3.ctl direct=true parallel=true &

wait   # wait for all to finish

All sessions must use the same table, APPEND mode, and PARALLEL=TRUE. Indexes must be UNUSABLE before starting.

-- Before parallel load
ALTER TABLE dw.fact_sales NOLOGGING;
ALTER INDEX dw.fact_sales_pk UNUSABLE;

-- After all sessions finish
ALTER TABLE dw.fact_sales LOGGING;
ALTER INDEX dw.fact_sales_pk REBUILD NOLOGGING PARALLEL 8;

6. Constraint management

Temporarily disable or defer constraints to avoid validation overhead:

-- Disable FK for the load, re-enable with NOVALIDATE for speed
ALTER TABLE dw.fact_sales DISABLE CONSTRAINT fk_customer_id;
-- run sqlldr ...
ALTER TABLE dw.fact_sales ENABLE NOVALIDATE CONSTRAINT fk_customer_id;

-- Or use deferred constraints (only if FK is declared DEFERRABLE)
ALTER SESSION SET CONSTRAINTS = DEFERRED;

7. Partitioned tables

Direct path loads into a single partition when possible:

INTO TABLE dw.fact_sales PARTITION (p_2024_q1)
APPEND

Loading into a partition is faster than loading into the whole table — the HWM is per-partition, and index maintenance is scoped to the partition.

Performance comparison

Scenario	Rows/sec (approx)	Notes
Conventional, default settings	20,000–50,000	Buffer cache + redo + indexes
Conventional, tuned BINDSIZE	50,000–150,000	Fewer round-trips
Direct path	200,000–500,000	Bypasses SQL layer
Direct + NOLOGGING	400,000–1,000,000	No redo for data blocks
Parallel direct + NOLOGGING	1M–5M+	Limited by I/O bandwidth

Numbers vary heavily by hardware, network, row width, and index count.

Monitoring a running load

-- Check progress via v$session_longops
SELECT sid, serial#, opname, sofar, totalwork,
       ROUND(sofar/totalwork*100,1) pct_done,
       time_remaining
FROM   v$session_longops
WHERE  opname LIKE '%LOAD%'
AND    totalwork > 0
ORDER  BY start_time DESC;

-- Redo rate
SELECT name, value FROM v$sysstat
WHERE name IN ('redo size','redo writes','redo write time');

-- Buffer cache hit ratio for conventional path
SELECT 1 - (phyrds / (dbgr + csgr + dbpr)) ratio
FROM (SELECT SUM(DECODE(name,'physical reads',value)) phyrds,
             SUM(DECODE(name,'db block gets',value)) dbgr,
             SUM(DECODE(name,'consistent gets',value)) csgr,
             SUM(DECODE(name,'db block gets from cache',value)) dbpr
      FROM v$sysstat);

Checklist for maximum throughput

□ DIRECT=TRUE
□ SKIP_INDEX_MAINTENANCE=TRUE  (rebuild after)
□ UNRECOVERABLE or ALTER TABLE NOLOGGING
□ PARALLEL=TRUE (if splitting input)
□ FKs disabled or deferred
□ BINDSIZE = READSIZE = 32+ MB (conventional) or READSIZE = 64+ MB (direct)
□ ROWS = 10,000–50,000 (conventional)
□ Load into partition if applicable
□ Take backup after UNRECOVERABLE load
□ Re-enable constraints and rebuild indexes after load

Best practices

• Profile first: run with ROWS=1000 and check the log rate, then scale up
• The index rebuild usually takes 30–50% of total load time — don't skip it even when in a hurry
• Keep run logs for trend analysis: load rate drops over time usually mean index fragmentation or storage contention
• For nightly loads, pre-truncate + direct path + skip_index_maintenance + rebuild is almost always faster than APPEND conventional