How to Invalidate a Cursor to Load a New Plan

If an SQL is frequently in use and has different SQL plans historically for execution, there might be a scenario that current execution plan in use is a bad one. In that case you might want to get rid of this current plan so that optimizer could possibly load a better plan for next executions. In this case, you would need to invalidate the current cursor in shared pool. Following is the way how we invalidate a SQL cursor from share pool.

Using DBMS_SHARED_POOL.PURGE

Once you find out SQL ID of the poorly performing SQL (with current bad plan), get the address and hash value from V$SQLAREA, and then pass as first argument to this procedure. 

Remember that you must kill the current sessions executing this SQL otherwise cursor might not get invalided.

SQL> select address,hash_value from v$sqlarea where sql_id='bqp2ps6zxqxvx';   ADDRESS                    HASH_VALUE ---------------- -------------------------------- 0000000F1EE4E2B0     3218831229   SQL> exec dbms_shared_pool.purge('0000000F1EE4E2B0,3218831229','C')   PL/SQL procedure successfully completed.

Once done, you can query v$sesison to find out the sessions executing this SQL, and the child cursor used by the sessions. From V$SQL you can find which execution plan is in use now.

SQL> select sid,serial#,machine,sql_child_number from v$session where sql_id='bqp2ps6zxqxvx';          SID    SERIAL# MACHINE                SQL_CHILD_NUMBER ---------- ---------- ------------------------ -------------------------------------         12      48689 MYAPPSERVER01                  1   SQL> select plan_hash_value,child_number,last_load_time,executions,cpu_time,(cpu_time/1000000)/executions CPU_SEC_PER_EXEC from v$sql where sql_id='bqp2ps6zxqxvx';   PLAN_HASH_VALUE CHILD_NUMBER     LAST_LOAD_TIME         EXECUTIONS     CPU_TIME              CPU_SEC_PER_EXEC --------------- ------------ --------------------------------------------------------- ---------- ---------- ----------------------------------------       394908553            0                             2018-05-04/17:47:16     1                         1981212                      1.981212