Purpose
You can use this statement to clear the plan cache with specified database and SQL IDs.
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
| Parameter | Description |
|---|---|
| SQL_identifier | The SQL statement in the format of sql_id = 'xxx'. If you do not specify this parameter, all SQL plan caches are cleared. |
| database_list | The databases. If you do not specify this parameter, all plan caches are cleared. |
| tenant_list | The tenants. Be sure to specify tenant_list when you specify SQL_identifier and database_list to limit the operation to the specified tenants.Note: Only the sys tenant can specify tenant_list. Other tenants cannot specify this parameter, meaning that they can only clear their own plan caches. If the sys tenant does not specify tenant_list, the plan caches of all tenants are cleared. |
| GLOBAL | Optional. If you do not specify this parameter, plan caches of the specified server are cleared. Otherwise, plan caches of all servers associated to the tenant are cleared. |
Examples
Flush the plan cache.
obclient> ALTER SYSTEM FLUSH PLAN CACHE; Query OK, 0 rows affectedFlush the plan cache for MySQL tenants and Oracle tenants.
obclient> ALTER SYSTEM FLUSH PLAN CACHE TENANT = 'MySQL','Oracle'; Query OK, 0 rows affectedClear the specified plan cache in the sys tenant.
obclient> ALTER SYSTEM FLUSH PLAN CACHE sql_id='B601070DFC14CB85FDA3766A69A9E1B3' databases='myob1' tenant='tenant1' GLOBAL; Query OK, 0 rows affectedClear the plan cache of a user tenant that has logged on as the DBA.
obclient> ALTER SYSTEM FLUSH PLAN CACHE sql_id='B601070DFC14CB85FDA3766A69A9E1B3' databases='myob1' GLOBAL; Query OK, 0 rows affected