Alter system flush "Oracle cache"

The following three statements are used to refresh the Oracle cache. Here is a summary.

 

1) alter system flush global context

Note:

For multi-tier architectures such as application servers and data block servers that communicate through the connection pool, the information about the connection pool is kept in SGA. This statement clears the connection information.

 

2) alter system flush shared_pool

All SQL Execution plans previously saved by library cache and data dictionary cache will be cleared, but the recently executed items cached in the shared SQL area or PL/SQL area will not be cleared. Refreshing the shared pool can help merge fragments (small chunks), release a few shared pool resources, and temporarily solve the shared_pool fragmentation problem. However, this approach is generally not recommended. The reason is as follows:

· Flush shared pool will cause unused cursor to be cleared out of the Shared Pool. If these SQL statements need to be executed subsequently, the database will experience a lot of hard parsing, the system will experience severe CPU contention, and the database will have fierce latch competition.
· If the application does not use Bound variables and a large number of similar SQL statements are not executed, the flush shared pool may only provide a short improvement and the database will return to the original state soon.
· If the shared pool is large and the system is very busy, refreshing the shared pool may cause the system to be suspended. For systems like this, try to do it when the system is idle.

The following tests the effect of refreshing on Shared Pool fragments:

SQL> select count (*) from x $ ksmsp; count (*) -------- 41637sql> alter system flush shared_pool; the system has changed. SQL> select count (*) from x $ ksmsp; count (*) ---------- 9276

3) alter system flush buffer_cache

To minimize the impact of the cache on the test, you need to manually refresh the buffer cache to urge Oracle to re-Execute physical access (physical reads in the statistics ).

Test Environment

SQL> select count (*) from TT; count (*) ---------- 1614133 SQL> show user; user is "HR" SQL> exec dbms_stats.gather_table_stats ('hr', 'TT '); the PL/SQL process is successfully completed. SQL> select blocks, empty_blocks from dba_tables where table_name = 'TT' and owner = 'hr'; blocks empty_blocks ---------- 22357 table TT has 22357 Blocks

Observe the state = 0 with x $ BH

SQL> select count (*) from x $ BH where State = 0; count (*) ---------- 0sql> alter system flush buffer_cache; the system has changed. SQL> select count (*) from x $ BH where State = 0; count (*) ---------- 40440

State = 0 indicates that the buffer State is free. After the cache is flush, all the buffers are marked as free.

Observe the impact of flush cache on queries:

SQL> set autot on statisticssql> select count (*) from TT; count (*) ---------- 1614112 statistical information limit 0 recursive cballs 0 dB block gets 22288 consistent gets 22277 physical reads 0 redo size 416 bytes sent via SQL * Net to client 385 bytes received via SQL * Net From Client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 1 rows processeds QL>/count (*) ---------- 1614112 statistical information limit 0 recursive cballs 0 dB block gets 22288 consistent gets 0 physical reads 0 redo size 416 bytes sent via SQL * Net to client 385 bytes received via SQL * Net From Client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 1 rows processedsql> alter system flush buffer_cache; the system has changed. SQL> select count (*) from TT; count (*) ---------- 1614112 statistical information limit 0 recursive cballs 0 dB block gets 22288 consistent gets 22277 physical reads 0 redo size 416 bytes sent via SQL * Net to client 385 bytes received via SQL * Net From Client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 1 rows processed