Use of Oracle Table statistics

Recently developed the process of processing ECIF related projects, using Stored procedures to run batch processing data. Run batches of data regularly, each time the stored procedure runs using temporary tables for data collation, and then join this temporary table to put the data into the target table, and finally empty the temporary table.

Issue: Execution is slow in join processing, sometimes not a result set for half a day.

Resolution: Update database table statistics + Use multi-Process Execution

Many processes do not speak much here. Each time the data is inserted into the temporary table, the data reaches millions of, and the table is cleared after the stored procedure, which causes the database to mistakenly assume that the temporary table data is 0 consistent because the database table statistics are not real-time. Updating table statistics is a good way to estimate statistics (especially for larger partitioned tables) and to get better statistical results, resulting in a faster SQL execution plan.

is to have the database update the staging table statistics before the join operation is processed.

Dbms_stats.gather_table_stats (ownname=> ' Your user name ',tabname=> ' Your table name ', degree=>8,cascade=>true);

For more information about Oracle statistics, see: Collecting Oracle Statistics

Use of Oracle Table statistics