Performance issue on transient tables

Gathering STATS on PROCESS tables and maintaining the same a bit troublesome process.

Since the STATS gather to be done when the table is populated with the highest volume and that too differ with different jobs. Then the column statistics of key columns need to be deleted since those vary with each job.

Instead of that deleting the STATS and locking the same works consistently. In that Oracle uses dynamic sampling on the fly and gives the right plan.

The below steps are one time activity and no further maintenance is needed.

TRUNCATE TABLE PROCESSTAB;

EXEC dbms_stats.delete_table_stats('SCPOAPPG','PROCESSTAB');

EXEC dbms_stats.lock_table_stats('SCPOAPPG','PROCESSTAB');