RDBMS version: 11.2.0.4
Platform : Oracle Linux 6.4
In my production environment, I have a badly performing query. I have created an index on a table which might improve the performance of this query. So, now, I need to let the optimizer know of this newly created index by gathering fresh stats.
I have some questions on NO_INVALIDATE parameter in DBMS_STATS package:
Option1.
If I use NO_INVALIDATE => FALSE , it will invalidate all the cursors where this table is referred. All those queries have to be reparsed which requires some CPU and Memory. My production DB server has 256 GB RAM with 80 CPUs. So, even if I run NO_INVALIDATE => FALSE , this shouldn't cause a big impact in my production DB. I mean the SQLs which are currently being executed won't error out. The SQLs just have to reparse and re-execute internally. Am I right ?
Option2.
If I use NO_INVALIDATE => TRUE, the existing cursors will not be invalidated. But , any 'NEW queries' (queries run after I've created the index) will start using the index if optimizer is convinced that the data access via new index is less costlier. So, If my objective is just to see if the newly created index is being chosen by optimizer for new queries ,Option2 will be safer in a production environment . Right ?