alter system flush"oracle的缓存"
下面3条语句,旨在刷新oracle的缓存。这里总结一下。
1)alter system flush global context
对于多层架构的,如上图:应用服务器和数据块服务器通过连接池进行通信,对于连接池的这些信息被保留在SGA中,这条语句便是把这些连接信息清空。
2)alter system flush shared_pool
将使library cache和data dictionary cache以前保存的sql执行计划全部清空,但不会清空共享sql区或者共享pl/sql区里面缓存的最近被执行的条目。刷新共享池可以帮助合并碎片(small chunks),释放少数共享池资源,暂时解决shared_pool中的碎片问题。但是,这种做法通常是不被推荐的。原因如下:
A)Flush Shared Pool会导致当前未使用的cursor被清除出共享池,如果这些SQL随后需要执行,那么数据库将经历大量的硬解析,系统将会经历严重的CPU争用,数据库将会产生激烈的Latch竞争。
B)·如果应用没有使用绑定变量,大量类似SQL不停执行,那么Flush Shared Pool可能只能带来短暂的改善,数据库很快就会回到原来的状态。
C)如果Shared Pool很大,并且系统非常繁忙,刷新Shared Pool可能会导致系统挂起,对于类似系统尽量在系统空闲时进行。
下面测试一下,刷新对共享池碎片的影响:
SQL> select count(*) from x$ksmsp; (
x$ksmsp是 shared pool中条目相关信息
COUNT(*)
----------
38705
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
5541
3)alter system flush buffer_cache
为了最小化cache对测试实验的影响,需要手动刷新buffer cache,以促使oracle重新执行物理访问(统计信息里面的:physical reads)。
测试环境
SQL> select count(*) from t7;
COUNT(*)
----------
72997
SQL> show user
USER is "SCOTT"
SQL> exec dbms_stats.gather_table_stats('SCOTT','T7');
PL/SQL procedure successfully completed.
SQL> select blocks,empty_blocks from user_tables where table_name='T7';
BLOCKS EMPTY_BLOCKS
---------- ------------
1065 0
(x$bh--buffer header 它记录了数据块头信息,比如这个块被touch了多少次(tch字段),被touch的越多就表示这个块越抢手。)
SQL> select count(*) from x$bh where state=0;
COUNT(*)
----------
17964
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from x$bh where state=0;
COUNT(*)
----------
22583
state=0表示buffer状态是free,flush cache后,所有的buffer都被标志为free
观察flush cache后,对查询的影响:
sql>set autot on statistics
sql>exec dbms_stats.gather_table_stats('SCOTT','T7);
sql>select count(*) from t7;
COUNT(*)
----------
72997
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1044 consistent gets
1040 physical reads
0 redo size
528 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
COUNT(*)
----------
72997
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1044 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush buffer_cache;
System altered
SQL> select count(*) from t7;
COUNT(*)
----------
72997
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1044 consistent gets
1040 physical reads
0 redo size
528 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed