Collect stats collects demographic data about the columns or indces of a table.This statistics is used by the PE to optimize the plan which can be viewed by explain command. When collect stats is executed,the statistics is stored in data for use of PE.
The Optimizer plans an execution strategy for every SQL query submitted to it. We have also seen that the execution strategy for any query may be subject to change depending on various factors. For the Optimizer to consistently choose the optimum strategy, it must be provided with reliable, complete, and current demographic information regarding all of these factors. The best way to assure that the Optimizer has all the information it needs to generate optimum execution strategies is to COLLECT STATISTICS.
Statistics tell the Optimizer how many rows/ value there are. The Optimizer uses statistics to plan the best way to access data. May improve performance of complex queries and joins. NUSI Bit Mapping requires collected statistics. Helpful in accessing a column or index with uneven value distribution. Stale statistics may mislead the Optimizer into poor decisions. Statistics remain valid across a reconfiguration of the system. COLLECT is resource intensive and should be done during off hours.