DMS Oracle Full Load Slow for Small/Empty Tables

Question

My AWS DMS full load task from an Oracle source is taking hours to complete, even though most tables have very few rows or are empty. I've tried increasing the replication instance size, reducing MaxFullLoadSubTasks, and running full-load-only (no CDC), but nothing improves the load time. Per-table timings show that even 0-row tables take 10-20 minutes each. The source and target databases are both idle during the load. Why is DMS slow when there's almost no data to migrate?

Short Answer

DMS fetches table metadata (columns, constraints, indexes, partitions) from Oracle's data dictionary on-demand for each table during full load, and these fetches are serialized across all subtasks by an internal mutex. If Oracle's dictionary statistics are stale or missing, each metadata query can take seconds instead of milliseconds. With hundreds or thousands of tables serialized one at a time, this adds up to hours of overhead that is entirely independent of row count or data volume.

The fix is to gather Oracle dictionary statistics on the source database before running the DMS full load:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Detailed Explanation

How DMS Fetches Table Metadata During Full Load

When a DMS full load task starts processing a table, it does not use a pre-cached schema definition. Instead, it queries the Oracle data dictionary in real time to retrieve:

• Object resolution (ALL_OBJECTS)
• Column metadata (ALL_TAB_COLS)
• Primary key and constraint information (ALL_CONSTRAINTS, ALL_CONS_COLUMNS)
• Index definitions
• Partition information

These queries are executed under a task-wide mutex that serializes all metadata fetches across all subtasks. This means that even if you configure 8 or 16 MaxFullLoadSubTasks, only one subtask at a time can query the Oracle dictionary for table definitions. The other subtasks must wait in queue.

Why Stale Dictionary Statistics Cause Slowness

Oracle's data dictionary views (ALL_OBJECTS, ALL_TAB_COLS, ALL_CONSTRAINTS, etc.) are backed by SYS-owned base tables. The Oracle query optimizer uses statistics on these SYS tables to choose efficient execution plans for dictionary queries.

When SYS statistics are stale or missing:

• The optimizer may choose full table scans instead of index lookups on internal SYS tables
• Each dictionary query can take 1-5 seconds instead of 10-50 milliseconds
• With the task-wide mutex serializing these queries, each table must wait for all preceding tables to complete their metadata fetches

The Math

If each dictionary query takes 2 seconds (due to stale stats) and you have 8 subtasks contending on the mutex:

• Per-table wait time: up to 8 x 2 seconds = 16 seconds per table in the queue
• With 1000 tables: 1000 x 16 seconds = ~4.4 hours of pure metadata overhead

If the same queries take 50 milliseconds (with fresh stats):

• Per-table wait time: 8 x 50ms = 400ms per table
• With 1000 tables: 1000 x 400ms = ~7 minutes of metadata overhead

Symptoms That Point to This Issue

• Empty or very small tables take the same time as large tables (the bottleneck is metadata lookup, not data transfer)
• Both source and target databases show low CPU/IO during the load
• Increasing the replication instance size (compute) does not help
• Reducing or increasing MaxFullLoadSubTasks does not significantly help (more subtasks can even make it worse by increasing queue depth)
• Running full-load-only (without CDC) does not help
• The same task configuration works fast in a different region or against a different Oracle instance

Resolution

Step 1: Confirm the Diagnosis

Run these queries on the Oracle source database (read-only, no impact on production):

SET TIMING ON

-- Check how long a typical dictionary query takes
SELECT column_name, data_type, data_length, data_precision, data_scale, nullable
FROM all_tab_cols
WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE'
ORDER BY column_id;

-- Check dictionary statistics freshness
SELECT table_name, last_analyzed
FROM dba_tab_statistics
WHERE owner = 'SYS'
ORDER BY last_analyzed DESC NULLS LAST
FETCH FIRST 10 ROWS ONLY;

If the first query takes more than 500 milliseconds, or if the second query shows NULL or very old dates for last_analyzed, stale dictionary statistics are the likely cause.

Step 2: Gather Dictionary Statistics

Run on the Oracle source database before starting your next DMS full load:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

This operation:

• Takes 2-10 minutes depending on the number of SYS objects
• Does NOT modify, lock, or touch any user data or schemas
• Only updates optimizer statistics for SYS-owned dictionary tables
• Is a standard Oracle-recommended maintenance operation
• Is reversible via DBMS_STATS.RESTORE_DICTIONARY_STATS if needed

Step 3: Verify and Re-run

After gathering statistics, re-run the diagnostic query from Step 1. It should now complete in milliseconds. Then restart your DMS full load task -- you should see a dramatic improvement in per-table load times.

Step 4: Prevent Recurrence

Schedule periodic dictionary stats gathering to prevent recurrence:

-- Run monthly, or after major schema changes (bulk DDL, migrations, upgrades)
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

On Amazon RDS for Oracle, you can schedule this via a DBMS_SCHEDULER job or run it manually during maintenance windows. Dictionary statistics do not auto-refresh on schema changes -- they must be explicitly gathered.

Important Notes

• The mutex serialization in DMS is by design (it prevents concurrent metadata modifications from corrupting shared state). It is not a bug.
• This issue is specific to the Oracle source database instance's internal state. Two identical DMS configurations pointing at different Oracle instances can exhibit different behavior depending on each instance's dictionary statistics freshness.
• This applies to DMS full load operations from Oracle sources. CDC-only tasks are not affected in the same way since table definitions are cached after the initial load.
• The GATHER_DICTIONARY_STATS operation is safe for production Oracle databases and is recommended by Oracle as part of routine database maintenance.

Related Information

• AWS DMS Best Practices for Oracle Sources
• Oracle Documentation: DBMS_STATS.GATHER_DICTIONARY_STATS