Friday, February 10, 2023

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.


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';
--------------- ------------ --------------------------------------------------------- ---------- ---------- ----------------------------------------
      394908553            0                             2018-05-04/17:47:16     1                         1981212                      1.981212

No comments:

Post a Comment

Popular Posts - All Times