Flushing Pools in Oracle 10

Martin Zahn, Akadia AG
Information Technology, CH-3672 Oberdiessbach

15.09.2007

Overview

Using ALTER SYSTEM you can clear all data from the SGA (system global area). This is useful to clear existing data and re-load fresh data. In Oracle 10, it becomes possible for users to flush even the cache buffer.

Flush Buffer Cache

Use the following statement to flush the buffer cache.

SELECT o.owner,
       o.object_type,
       substr(o.object_name,1,10)
       objname,
       b.objd,
       b.status,
       count(b.objd)
FROM v$bh b, dba_objects o
WHERE b.objd = o.data_object_id
   AND o.owner not in ('SYS','SYSTEM','SYSMAN')
GROUP BY o.owner,
         o.object_type,
         o.object_name,
         b.objd,
         b.status;

OWNER        OBJECT_TYPE         OBJNAME          OBJD STATUS COUNT(B.OBJD)
------------ ------------------- ---------- ---------- ------- -------------
GEO          LOB                 SYS_LOB000     126293 cr                  5
GEO          LOB                 SYS_LOB000     126457 cr                  5
GEO          LOB                 SYS_LOB000     126666 cr                  5
GEO          LOB                 SYS_LOB000     126531 cr                  5
GEO          LOB                 SYS_LOB000     126537 cr                  5

SQL> alter system flush buffer_cache;

SELECT o.owner,
       o.object_type,
       substr(o.object_name,1,10)
       objname,
       b.objd,
       b.status,
       count(b.objd)
FROM v$bh b, dba_objects o
WHERE b.objd = o.data_object_id
   AND o.owner not in ('SYS','SYSTEM','SYSMAN')
GROUP BY o.owner,
         o.object_type,
         o.object_name,
         b.objd,
         b.status;

OWNER        OBJECT_TYPE         OBJNAME          OBJD STATUS COUNT(B.OBJD)
------------ ------------------- ---------- ---------- ------- -------------
GEO          LOB                 SYS_LOB000     126293 free                1
GEO          LOB                 SYS_LOB000     126457 free                1
GEO          LOB                 SYS_LOB000     126666 free                1
GEO          LOB                 SYS_LOB000     126531 free                1
GEO          LOB                 SYS_LOB000     126537 free                1

However, note that this clause is intended for use only on a test database. It is not advisable to use this clause on a production database, because subsequent queries will have no hits, only misses.

Flush Shared Pool

The FLUSH SHARED POOL clause of ALTER SYSTEM lets you clear all data from the shared pool in the SGA (system global area). This is a useful feature to clear existing data and re-load fresh data.

SQL> alter system flush buffer_pool;

Initiate Checkpoint

A checkpoint performs the following three operations:

Every dirty block in the buffer cache is written to the data files.
That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk.
It's the DBWR that writes all modified database blocks back to the datafiles.

The latest SCN is written (updated) into the datafile header.

The latest SCN is also written to the controlfiles.

The following events trigger a checkpoint.

Redo log switch

LOG_CHECKPOINT_TIMEOUT has expired

LOG_CHECKPOINT_INTERVAL has been reached

DBA requires so (alter system checkpoint)

Additionally, if a tablespace is hot backuped, a checkpoint for the tablespace in question is taking place. While redo log switches cause a checkpoint, checkpoints don't cause a log switch.

SQL> alter system checkpoint;

Switch Logfile

A log switch occurs when one online redo log has been filled and the next online redo log is going to be filled. A log switch always triggers a checkpoint.

SQL> alter system switch logfile;