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
Parameter |
Description |
|---|---|
| tenant_list | Used to specify the tenant scope. When you specify the SQL_identifier and database_list parameters, you must specify tenant_list to limit the operation to the tenant scope.
NoteOnly the system tenant |
| SQL_identifier | Used to specify the SQL statement in the format of sql_id = 'xxx'. If this parameter is not specified, the plan cache for all SQL statements is cleared.
NoteUsed under the system tenant sys |
| database_list | Specifies the database. If this parameter is not specified, all plan caches are cleared.
NoteUsed under the sys tenant |
| GLOBAL | Optional. If this parameter is not specified, only the plan cache on the local server is cleared. If it is specified, the plan caches on all servers of the tenant are cleared. |
Examples
Clear all plan caches.
obclient> ALTER SYSTEM FLUSH PLAN CACHE; Query OK, 0 rows affectedClear the plan cache of the user tenant.
obclient> ALTER SYSTEM FLUSH PLAN CACHE TENANT = 'MySQL'; Query OK, 0 rows affectedDelete a specified plan cache under the sys tenant.
obclient> ALTER SYSTEM FLUSH PLAN CACHE sql_id='B601070DFC14CB85FDA3766A69A9E1B3' databases='myob1' tenant='tenant1' GLOBAL; Query OK, 0 rows affectedA regular tenant logs in to the database 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