altersystemflush “oracle cache”

alter system flush “oracle cache”
the
The following three statements are designed to refresh the oracle cache. Here is a summary.
the
1) alter system flush global context
  The illustration below: www.2cto.com  

For a multi-tier architecture, as shown in the figure above: the application server and the data block server communicate through the connection pool, and the information about the connection pool is kept in the SGA. This statement clears the connection information.
the
2) alter system flush shared_pool
Will clear all the SQL execution plans previously saved in the library cache and data dictionary cache, but will not clear the recently executed entries cached in the shared sql area or the shared pl/sql area. Refreshing the shared pool can help merge small chunks, release a few shared pool resources, and temporarily solve the fragmentation problem in shared_pool. However, this practice is generally not recommended. The reasons are as follows:
Flush Shared Pool will cause the currently unused cursor to be cleared out of the shared pool. If these SQLs need to be executed later, the database will undergo a lot of hard parsing, the system will experience severe CPU contention, and the database will generate intense Latch compete.
· If the application does not use bind variables, and a large number of similar SQLs are executed continuously, then the Flush Shared Pool may only bring short-term improvement, and the database will soon return to its original state.
· If the Shared Pool is very large and the system is very busy, refreshing the Shared Pool may cause the system to hang. For similar systems, try to do it when the system is idle.
Let’s test the impact of refreshing on shared pool fragments:
[sql]
SQL> select count(*) from x$ksmsp;
the
COUNT(*)
———- 
41637
www.2cto.com
SQL> alter system flush shared_pool;
the
The system has changed. the
the
SQL> select count(*) from x$ksmsp;
the
COUNT(*)
———- 
9276
the
3) alter system flush buffer_cache
In order to minimize the impact of the cache on the test experiment, it is necessary to manually refresh the buffer cache to prompt Oracle to re-execute the physical access (in the statistical information: physical reads).
test environment
[sql]
SQL> select count(*) from tt;
the
COUNT(*)
———- 
1614112
the
SQL> show user;
USER is “HR”
SQL> exec dbms_stats.gather_table_stats('HR','TT');
the
PL/SQL procedure completed successfully. the
the
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
the
With the help of x$bh, observe the situation of state=0
[sql]
SQL> select count(*) from x$bh where state=0;
the
COUNT(*)
———- 
    0  
the
SQL> alter system flush buffer_cache;
the
The system has changed. the
the
SQL> select count(*) from x$bh where state=0;
the
COUNT(*)
———- 
40440
the
state=0 indicates that the buffer state is free, after flush cache, all buffers are marked as free
After observing the flush cache, the impact on the query:
[sql]
SQL> set autot on statistics
SQL> select count(*) from tt;
www.2cto.com
COUNT(*)
———- 
1614112
the
Statistics
————————————————– ——– 
      0 recursive calls  
      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)  
                             
the
SQL> /
www.2cto.com
COUNT(*)
———- 
1614112
the
Statistics
———————————————————- 
      0 recursive calls  
      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)  
                             
the
SQL> alter system flush buffer_cache;
the
The system has changed. the
the
SQL> select count(*) from tt;
www.2cto.com
COUNT(*)
———- 
1614112
the
Statistics
————————————————– ——– 
      0 recursive calls  
      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)  
                             
the
the
Author linwaterbin