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>
