From the documentation, NO_INVALIDATE is a Global statistics preference Parameter.
..controls the invalidation of dependent cursors of the tables for which statistics are being gathered. It does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have the system decide when to invalidate dependent cursors. This is the default.
My question is what is the meaning of dependent cursors of the table?
Best Answer
Simply put, they are the parsed SQL statements in the shared pool related to the table. When they are invalidated, they need to be hard parsed again the next time they run.
A typical use case for this: the database generates a suboptimal execution plan because of stale statistics on a table. The database fails to recognize this mistake on subsequent executions, but you notice it and decide to gather statistics. With
NO_INVALIDATE => true, this will not have any effect on the SQL with poor performance, the database will continue to use the old, suboptimal plan. That is why you should gather statistics with theNO_INVALIDATE => falseoption in such cases.