Alter System Flush Shared_Pool and Alter System Flush Buffer_Cache

This example shows the effect of the alter system flush shared_pool and alter system flush buffer_cache commands. It was tested on Oracle 11.2. First create a table with some rows to count:

 

SQL> create table tab1

  2  as select * from dba_tables

  3  /

 

Table created.

 

SQL> begin

  2  for a in 1..5

  3  loop

  4  insert into tab1 select * from tab1;

  5  end loop;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL>

 

The shared pool contains the library cache. This stores the parsing details and execution plans for SQL statements which have been run. You can remove these details by flushing the shared pool:

 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL>

 

The buffer cache contains blocks which have been read from disk. You can flush the buffer cache as follows:

 

SQL> alter system flush buffer_cache

  2  /

 

System altered.

 

SQL>

 

Now you can get a baseline time (1.94 seconds) for parsing the SQL, creating an execution plan, getting the database blocks from disk and counting the rows:

 

SQL> set timing on

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

     97920

 

Elapsed: 00:00:01.94

SQL>

 

Count the rows again. This time the SQL is already in the library cache so it does not need to be reparsed. The database blocks are still in the buffer cache from the previous SQL statement so there is no need to get them from disk either. As a result, this statement is much quicker (0.04 seconds):

 

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

     97920

 

Elapsed: 00:00:00.04

SQL> set timing off

SQL>

 

Now flush the shared pool to remove the SQL from the library cache:

 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL>

 

Run the SQL again. Having to reparse the SQL and recreate the execution plan adds a fraction of a second to the elapsed time:

 

SQL> set timing on

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

     97920

 

Elapsed: 00:00:00.07

SQL> set timing off

SQL>

 

Flush the buffer cache to remove the database blocks:

 

SQL> alter system flush buffer_cache

  2  /

 

System altered.

 

SQL>

 

Count the rows in the table as before. Having to fetch the database blocks from disk again adds over a second to the elapsed time:

 

SQL> set timing on

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

     97920

 

Elapsed: 00:00:01.49

SQL> set timing off

SQL>

 

Finally, flush both the shared pool AND the buffer cache:

 

SQL> alter system flush shared_pool

  2  /

 

System altered.

 

SQL> alter system flush buffer_cache

  2  /

 

System altered.

 

SQL>

 

Count the rows in the table for the last time. This involves reparsing the SQL, recreating the execution plan and fetching the database blocks. This time the elapsed time is roughly the same as the baseline time at the start:

 

SQL> set timing on

SQL> select count(*) from tab1

  2  /

 

  COUNT(*)

----------

     97920

 

Elapsed: 00:00:01.85

SQL> set timing off

SQL>