My question is…
Will oracle create and use a new execution plan every time (or compare with old execution plan in SGA) Whenever we take table/index stats regular ?
Suppose already one plan is present in SGA for a particular query, after sometime I will run the below statement
Now this will generate new stats, will oracle again create a new execution plan for the same query? Already same query plan is present in SGA? If yes then what is the use of hard/soft parsing?
Please share some example
Starting with Oracle 9i, Oracle recommends that the database optimizer be set to COST Based (the alternate was Rule base which since 10gR2 is no longer recommended). The COST based optimizer uses database statistics to generate the execution plan. However the statistics need to be refreshed regularly so that the optimizer can choose the most appropriate execution plan.
In your particular case, once you run your command to update the HR and EMPLOYEE tables, the new stats will be used by the optimizer to determine the execution plan. Be advised if you had the statistics populated and you only insert one new row into HR and/or EMPPLOYEES, the optimizer would not change (if at all). If however you had a builk load of data to either HR and/or EMPLOYEES table, then updating the statistics would be updated and the optimizer would generate a better optimized execution plan.
I am enclosing a white paper that oracle published some information about its optimzer and statistics:
I hope this helps