Oracle: Tuning Slow ALTER TABLE DROP COLUMN

Product: Oracle
Version: 8i - 18c

I came across an interesting question that why Oracle takes so long to drop a column while MS SQL Server is immediate:
Alter Table [Table_name] Drop ([col1], [col2], [col3], .....);

Firstly, let's clarify that MS SQL Server only logically drop the column, while Oracle physically drop the column.  This means the space taken by the dropped column is not free up in MS SQL Server, but it is for Oracle.  However, for Oracle it is consider free space fragmentation, just like you delete record from the table, or set the column value to NULL.  Additional defragmentation required to see the freed space in tablespace level. If you would like Oracle to logically delete the column then use following statement:

Alter Table [Table_name] Set Unused ([col1], [col2], [col3], .....);

Note: To find out unused column, query DBA_UNUSED_COL_TABS

Next, let's list down what could cause Oracle column dropping to take so long:

1. Online redo log
2. Undo
3. Checkpoint
4. Table is update for each column even the ALTER TABLE DROP COLUMN is dropping multiple columns as 1 single statement
5. NOLOGGING still use UNDO tablespace
6. If UNDO dbf file is auto extend, then there are additional overhead to extend the dbf file

Next section going to discuss each option above

Tuning Online Redo Log

Several options to tune this:

1. Temporary set table to NOLOGGING before dropping column: alter table [table1] nologging;
2. Larger online redo log size to reduce number of slow checkpoints
3. Increase checkpoint size, e.g. 100,000 rows (still limit to redo log size in #2), in the drop column syntax: alter table drop (col1, col2) checkpoint 100000;
4. Move online redo log to NVMe SSD

Tuning Undo

1. Pre-allocate undo dbf size to prevent auto extend
2. Specify a small checkpoint size (but needs to maintain optimal speed with online redo log above): alter table drop (col1, col2) checkpoint 1000;
3. Move undo dbf to NVMe SSD

Checkpoint Tuning

1. Carefully check alert log for online redo log switching, and configure init LOG_CHECKPOINT_INTERVAL & LOG_CHECKPOINT_TIMEOUT
2. Oracle recommend 3 check points per hour, but adjust it to provide optimal performance when dropping columns (restore them back after dropping columns)
3. These parameters can be changed in real time without shutdown Oracle
4. Less frequent checkpoint will affect DB recovery, if it ever crash

Table Performance

1. Move tablespace used by the table to NVMe SSD
2. Set table to NOLOGGING temporary, and LOGGING after it is done
3. Take new DB backup after set NOLOGGING else DB is not restorable (table is not restorable)
4. Use multiple DB sessions to drop columns in parallel
5. ALTER TABLE DROP COLUMN can be interrupted as it has CHECKPOINT
6. It is possible to plan for multiple down time window to perform the housekeeping
7. It is possible to resume an interrupted column dropping for specific table using ALTER TABLE DROP COLUMNS CONTINUE CHECKPOINT [number]

Advance Performance Analysis

If you need to gather in depth performance statistics to get further understanding what could affect the specific column dropping performance, query following system views:

• v$mystat - join with v$statname
• v$statname - used with above
• v$rollstat
• v$enqueue_stat - lock statistics

Save the output to a static table before & after run ALTER TABLE DROP COLUMN, then study the statistics to understand where is the performance bottleneck.

Through out the period, ensure no contention in OS RAM, CPU, swap (must be 0 swap to be optimal).