alter system flush 刷新缓冲区的信息

os: centos 7.4
db: oracle 12.1.0.2

alter system flush

生产环境上慎用,有可能执行后,服务器短时间内的cpu,io会遇到瓶颈.

SQL> alter system flush ;
alter system flush 
*
ERROR at line 1:
ORA-02000: missing SHARED_POOL/BUFFER_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

alter system flush shared_pool

将使library cache 和 data dictionary cache 以前保存的 sql 执行计划全部清空，但不会清空共享sql区或者共享pl/sql区里面缓存的最近被执行的条目.

如果Shared Pool很大，并且系统非常繁忙，刷新Shared Pool可能会导致系统挂起，对于类似系统尽量在系统空闲时进行

The FLUSH SHARED_POOL clause lets you clear data from the shared pool in the system global area (SGA).
The shared pool stores:

1) Cached data dictionary information and

2) Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.

This statement does not clear global application context information, nor does it clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

alter system flush buffer_pool

SQL> alter system flush buffer_pool;
alter system flush buffer_pool
*
ERROR at line 1:
ORA-02000: missing
DEFAULT/KEEP/RECYCLE/POOL_2K/POOL_4K/POOL_8K/POOL_16K/POOL_32K/ALL keyword

SQL> alter system flush buffer_pool default;
SQL> alter system flush buffer_pool keep;
SQL> alter system flush buffer_pool recycle;
SQL> alter system flush buffer_pool all;

可以单独清空某个cache

alter system flush buffer_cache

The FLUSH BUFFER_CACHE clause lets you clear all data from the buffer cache in the system global area (SGA), including the KEEP, RECYCLE, and DEFAULT buffer pools.

Caution:

This clause is intended for use only on a test database. Do not use this clause on a production database, because as a result of this statement, subsequent queries will have no hits, only misses.
This clause is useful if you need to measure the performance of rewritten queries or a suite of queries from identical starting points.

官方特意强调生产环境禁用

alter system flush global context

通过连接池进行通信，对于连接池的这些信息被保留在SGA中，这条语句便是把这些连接信息清空。

这个理解不是很深刻,后面理解清楚后再补充

参考:

https://docs.oracle.com/database/121/SQLRF/statements_2017.htm#SQLRF00902