FLUSH PLAN CACHE

Purpose

This statement is used to clear the plan cache for a specified database and SQL ID.

Syntax

ALTER SYSTEM FLUSH PLAN CACHE
     [
      [SQL_identifier] [database_list] tenant_list
      ] [GLOBAL]

database_list:
     databases ='database_name, database_name...'

tenant_list:
     TENANT = 'tenant_name, tenant_name....'

Parameters

Examples

• Clear the plan cache for all databases.

  obclient> ALTER SYSTEM FLUSH PLAN CACHE;
  Query OK, 0 rows affected
  

• Clear the plan cache for MySQL and Oracle tenants.

  obclient> ALTER SYSTEM FLUSH PLAN CACHE TENANT = 'MySQL','Oracle';
  Query OK, 0 rows affected
  

• Clear the plan cache for a specified SQL statement in the sys tenant.

  obclient> ALTER SYSTEM FLUSH PLAN CACHE sql_id='B601070DFC14CB85FDA3766A69A9E1B3'
        databases='myob1' tenant='tenant1' GLOBAL;
  Query OK, 0 rows affected
  

• A regular tenant logs in as a DBA to clear its own plan cache.

  obclient> ALTER SYSTEM FLUSH PLAN CACHE sql_id='B601070DFC14CB85FDA3766A69A9E1B3'
         databases='myob1' GLOBAL;
  Query OK, 0 rows affected