Performance Issue after Truncate and Load of a table in Oracle One of our clients has migrated from old to new (larger size/resources) servers. A process (X) is taking 4 hours extra whenever we are doing a truncate and full load of a specific table. If we do not truncate-load this table X doesn't take long to run. In process X we are inserting, updating and reading data from this table into numerous temp tables and then using these temp tables further.

I would like to know:


Best practices whenever a truncate and full load is performed.
What steps can we take from our side on this specific table?
Does the database migration to another server bring such issues?
What debugging should I do? I am gathering stats with cascade after the full load on this table, then running the process X.
Is it recommended to update any Oracle parameters on moving from Oracle 12c from Oracle 11g or 10?


Sorry for being very generic. The exact usage of the table in process X is complex and difficult to mention here. Please advise.

Other details:
Table Structure is as follows: - Around 55 columns -PCT_FREE    10 PCT_USED INI_TRANS   1 MAX_TRANS 255 INITIAL_EXTENT  65536 NEXT_EXTENT   1048576 MIN_EXTENTS 1 MAX_EXTENTS   2147483645 PCT_INCREASE FREELISTS   FREELIST_GROUPS LOGGING YES BACKED_UP   N NUM_ROWS 4064125

Typical weekly full load inserts around 2-3Million records

I am doing gather stats to the table and other related tables as well, with following options: dbms_stats.gather_table_stats(ownname=>'schema_x', tabname => 'Table', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', cascade=>TRUE, DEGREE=>7)
Table has two Normal Unique index and one Normal Non-Unique index defined
Loading Truncated Table

Assume staging table is X_SOURCE, and target table to load is X_TARGET, and any calculations performed when loading X_TARGET can be done via SQL functions or user-defined functions.  Here we load col_a as is, inserting current date into col_b, col_c is multiplied by 2 times, and col_d as is.

alter table x_target nologging;
alter session enable parallel dml;
-- possibly drop indexes

insert /*+ append parallel(6) */ into X_TARGET 
   (select col_a, sysdate, col_c * 2, col_d from X_SOURCE);
-- recreate indexes if they were dropped
alter table x_target logging;

-- then invoke RMAN commands to back up the table


The above has the following features:  


Turn off logging on the target table x_target to avoid logging
overhead 
The /*+ append */ hint appends data to the end of the table
and bypasses several steps done during normal inserts 
Dropping and recreating the indexes can be much quicker than doing index
maintenance during the load.
Don't forget to turn logging back on
Ensure a backup is done after the load


New Server Issues

If the new server has more CPU/memory/etc. and you are seeing longer run time, I would definitely run the DBMS_STATS.GATHER_SYSTEM_STATS('START', ...) procedure, (after creating a table to store the stats being gathered), run your truncate/load process, then run DBMS_STATS.GATHER_SYSTEM_STATS('STOP')  and then query your stats table, and use DBMS_STATS.SET_SYSTEM_STATS to set values that need to be adusted -- see Performance Tuning Guide for details.  If you are on Oracle 12, also look at DBMS_STATS.GATHER_PROCESSING_RATE -- run it too while your process is running.