Quick Question: How to invalidate the cursor after statistics gathering

A long running job and you have identified that statistics of a table is causing sub optimal plan and you have gathered the statistics and asked teams to rerun the job. But still picking old plan or the statistics which should not, the reasons are and how to invalidate the cursor in library cache (SQL AREA)

1. when no_invalidate = false (default as of 9i)
Change in statistics makes all dependent cursors invalidated immediately.

2. when no_invalidate = true
Change in statistics makes no invalidation. New execution plan is generated only after the cursor is flushed out and reloaded.

3. when no_invalidate = dbms_stats.auto_invalidate (default as of 10g/11g)
The most interesting case. Change in statistics makes all dependent cursors invalidated after some specific period of time. This time is determined by the hidden parameter "_optimizer_invalidation_period". The default value of this parameter is 18000(s) == 5 hour.

The weird thing is that this does not make the dependent cursor "really invalidated". Instead, new child cursor is generated when the cursor is accessed after time has expired. v$sql_shared_cursor.roll_invalid_mismatch is 'Y' on those child cursors, Oracle does a hard parse when ROLL_INVALID_MISMATCH flag/bit is set. Like this.

According to Point 3, you have to wait 5 Hours to make your cursor invalidated,

So how to invalidate the cursor immediately,

1) SQL> alter system set "_optimizer_invalidation_period" = 60 scope=memory; (default is 18000 or 5 hours)
System altered.
Note: this is at system level so may give weird results

2) Quicker option is to comment any of the one of the column in the referenced table of query and comment is DDL, when DDL fires the statement will be invalidated, finally revert back the comment to null.

SQL> comment on table x 'test';
once done, revert
SQL> comment on table x null;

3) Append any hint so that its look like different sql statement (if you can change the query)
SQL> Select /*+ test */

Hope this helps.