Alter system flush & quot; oracle cache & quot;

The following three statements under alter system flush "oracle cache" are designed to refresh the oracle cache. Here is a summary. 1) alter system flush global context description: www.2cto.com for multi-layer architecture, such as: the application server and data block server communicate through the connection pool, the information for the connection pool is kept in SGA, this statement clears the connection information. 2) alter system flush shared_pool will clear all the SQL Execution plans previously saved in library cache and data dictionary cache, however, recently executed entries cached in the shared SQL area or pl/SQL area are not 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 sharded pool fragments: [SQL] SQL> select count (*) from x $ ksmsp; COUNT (*) ---------- 41637 www.2cto.com SQL> 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 cache on test experiments, You need to manually refresh the buffer cache, to urge oracle to re-Execute physical access (physical reads in the statistics ). Test Environment [SQL] SQL> select count (*) from tt; COUNT (*) ---------- 1614112 SQL> show user; USER: "HR" SQL> exec dbms_stats.gather_table_stats ('hr ', 'TT'); PL/SQL process completed successfully. SQL> select blocks, empty_blocks from dba_tables where table_name = 'TT' and owner = 'hr'; www.2cto.com BLOCKS EMPTY_BLOCKS ---------- 22357 0 table TT has a total of 22357 blocks using x $ bh, observe the state = 0 situation [SQL] SQL> select count (*) from x $ bh where state = 0; COUNT (*) ---------- 0 SQL> alter system flush buffer_cache; the system has been changed. SQL> select count (*) from x $ bh where state = 0; COUNT (*) ---------- 40440 state = 0 indicates that the buffer state is free. After flush cache, all buffers are marked as free to observe the impact of flush cache on queries: [SQL] SQL> set autot on statistics SQL> select count (*) from tt; www.2cto.com COUNT (*) ---------- 1614112 statistical information -------------------------------------------------------------- 0 recursive call0 db block gets 22288 consistent gets 22277 physical reads 0 re Do size 416 bytes sent via SQL * Net to client 385 bytes provisioned ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>/www.2cto.com 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 Client 385 bytes provisioned ed 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; the system has been changed. SQL> select count (*) from tt; www.2cto.com 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 by linwaterbin