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

EXEC dbms_stats.gather_table_stats(‘HR’,’EMPLOYEES’);

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


Hi Gaurav,

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



All Answers

Answers by Expert:

Ask Experts


Peter Choi


I am a senior Oracle DBA, PeopleSoft HCM specialist and Project Manager with 15+ years experience. I have been working with PeopleSoft (HRMS/HCM 5, 7, 7.x and 8.9, 9.x), Oracle RDBMS (7.3 - 11gR2) on various Unix and MS-Windows platforms, and some Oracle Application Server (9i - 11gR1). I also have experience with the configuration and administration of PeopleSoft's Internet Architecture (PIA) and the Oracle 11g Fusion Middleware and Oracle Business Intelligence (OBI).

©2016 All rights reserved.