Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

How to invalidate individual statement by sql_id

This short article will be about the way how to invalidate individaul statement in shared pool, to force hard parse and potential new plan generation.

There are wide range of known methodes to do that, like

  • Execute some DDL on the one of the objects used in statement. Like adding comment to the table.
  • Playing with statistics for some of the involved objects.
  • Granting/revoking some privileges to parsing user on involved object.

But all of these methods are not affordable, because they affect not only one statement but many others. On intensive OLTP system, doing one of these actions can lead to high peaks on performance graphs because same objects can be used in hundreds of other statements. I didnt listed here dbms_shared_pool.purge procedure because it is not about invalidation, it tries to purge the statement when it is not in use, which is nearly impossible on busy systems.

I want to share the way I do it. It will localize the invalidatation only on one particular SQL_ID.

The key is SQL Patch! We will use it to invalidate specific SQL_ID. Simple steps is to add dummy SQL Patch on statement and then drop it. Actually this action is harmless, that is why created SQLPatch can be left for a while until you will be sure that the cursor is invalidated.

Just execute this small peace of code and provide sql_id:

declare
  sql_text clob;
begin
  select sql_fulltext into sql_text from v$sqlarea where sql_id = '&SQL_ID' and rownum = 1;
  sys.dbms_sqldiag_internal.i_create_patch(sql_text,'INVALIDATE','invalidate_cursor');
  sys.dbms_sqldiag.drop_sql_patch('invalidate_cursor');
end;
/

After you will execute this code, statement will be invalidated and next execution will force hard parse. You can check it from v$sql dynamic view.

select sql_id, invalidations, executions, child_number, plan_hash_value, sql_patch from v$sql where sql_id='&SQL_ID';

NOTE: Solution tested and used on 12c.

 


(c) Aychin Gasimov, 09/2016, Munich, Germany