Oracle Database Discussions

We are using 11.1.0.7

Recently there was a problem with a query, we came to know about the stale stats but since the application has a process to gather stats, we let app admin run the stats gathering process but still the performance was really bad for this SQL.

Later the vendor added a parameter to the stats gathering which enhanced the performance of the SQL.

That parameter was NO_INVALIDATE => FALSE.

After researching a bit, I got to know few things about this parameter -

Before 10g when new statistics where generated all the exisiting SQL that referenced the tables that were changed was invalidated
With 10g, there is a new parameter "NO_INVALIDATE" added to the DBMS_STATS
There are three options/values for this parameter -

1. FALSE - If set to false, it works just like it used to pre-10g. That means procedure invalidates the dependent cursors immediately if set to FALSE.
2. TRUE - All the SQL cursors will not get invalidated. Procedure does not invalidate the dependent cursors if set to TRUE
3. AUTO_INVALIDATE - This is default and this let Oracle decide when to invalidate dependent cursors. Cursors will remain active for 5 Hrs by default.

Question - How can changing the behaviour of gathering stats, so that the procedure invalidates the dependent SQL cursors immediately, helps in the performance of the query.

Thanks!