Flush the Shared Pool and Buffer Cache with ALTER SYSTEM

calendar Aug 27, 2026 / · 9 min read · Oracle DBA ALTER SYSTEM Shared Pool Buffer Cache FLUSH SHARED_POOL FLUSH BUFFER_CACHE Oracle Administration SGA Cache Flushing Oracle Performance  ·
Share on: linkedin copy

Flush the Shared Pool and Buffer Cache with ALTER SYSTEM

Purpose

Where DBMS_SHARED_POOL.PURGE removes one cursor or one package from the library cache, ALTER SYSTEM FLUSH SHARED_POOL clears every parsed statement, stored procedure, function, package, and trigger cached in the shared pool at once — a blunt instrument standing next to a scalpel. FLUSH BUFFER_CACHE does the equivalent wipe on the other side of the SGA, discarding every cached data block across the KEEP, RECYCLE, and DEFAULT buffer pools in a single statement. Both clauses live under the same ALTER SYSTEM statement documented in the SQL Language Reference, and both exist for the same narrow reason: giving a DBA a clean, repeatable starting point when the question is "how does this actually perform from cold," not "how does it perform with yesterday's cache still warm."

The shared pool holds two things: cached data dictionary information, and shared SQL and PL/SQL areas — the parsed representations and execution plans for every statement, procedure, function, package, and trigger that has run recently. FLUSH SHARED_POOL clears both categories in one pass. It does not touch global application context information, and it does not clear shared SQL or PL/SQL areas for anything currently executing at the moment the flush runs — Oracle will not pull the rug out from under a statement mid-execution. The command can be issued regardless of whether the instance has the database mounted or dismounted, open or closed.

The buffer cache holds something different: data blocks read from disk for tables and indexes. FLUSH BUFFER_CACHE clears all of it — not one pool selectively, but the KEEP, RECYCLE, and DEFAULT pools together in the same statement. Oracle's own documentation is explicit that this clause is intended for use only on a test database, because every subsequent query after the flush has no cache hits, only misses, until the cache warms back up. This post covers the syntax for both clauses, the legitimate reasons a DBA reaches for them — execution-plan testing and cold-cache benchmarking chief among them — and why neither belongs anywhere near a busy production instance.

Code

 1-- Clear the shared pool: dictionary cache + shared SQL/PL/SQL areas
 2ALTER SYSTEM FLUSH SHARED_POOL;
 3
 4-- Clear the buffer cache: KEEP, RECYCLE, and DEFAULT pools together
 5ALTER SYSTEM FLUSH BUFFER_CACHE;
 6
 7-- Force a checkpoint first so dirty buffers are written out before the flush
 8ALTER SYSTEM CHECKPOINT;
 9ALTER SYSTEM FLUSH BUFFER_CACHE;
10
11-- Purge a single package instead of clearing the whole shared pool (11g+)
12EXEC sys.DBMS_SHARED_POOL.purge('MY_SCHEMA.MY_PACKAGE', 'P');
13
14-- See what is actually occupying the library cache before deciding to flush
15SELECT owner, namespace, type, name, sharable_mem
16FROM   v$db_object_cache
17ORDER  BY sharable_mem DESC;
18
19-- Identify a specific cursor to purge instead of flushing everything
20SELECT sql_id, address, hash_value, sql_text
21FROM   v$sqlarea
22WHERE  sql_text LIKE 'SELECT empno FROM emp WHERE job%';

Code Breakdown

ALTER SYSTEM FLUSH SHARED_POOL

A single statement, no arguments. It empties the library cache of parsed SQL, PL/SQL, and cached dictionary rows in one operation. The immediate cost is that every one of those statements has to be hard-parsed again the next time it runs, which is why the ALTER SYSTEM reference calls it "a really brutal thing to do" on a system carrying real load — the database has to rebuild the shared pool's working set of commonly used statements from scratch.

ALTER SYSTEM CHECKPOINT before FLUSH BUFFER_CACHE

Flushing the buffer cache discards data blocks, including any modified ("dirty") blocks that have not yet been written to disk. Running ALTER SYSTEM CHECKPOINT first forces Oracle to write every committed change out to the datafiles, so the flush that follows is a clean discard of clean copies rather than a race against pending writes.

DBMS_SHARED_POOL.PURGE for a single object

The PURGE procedure, added in Oracle 11g Release 1, targets one named object at a time instead of the whole pool. The 'P' flag purges procedures, functions, and packages; 'T' purges types; 'R' purges triggers; 'Q' purges sequences. Passing an ADDRESS,HASH_VALUE pair sourced from V$SQLAREA purges a single cursor rather than a named PL/SQL object — the closest thing Oracle offers to a scalpel where FLUSH SHARED_POOL is a sledgehammer.

V$DB_OBJECT_CACHE and V$SQLAREA as reconnaissance

Before flushing anything, both views answer "what is actually in here." V$DB_OBJECT_CACHE lists objects in the library cache by owner, namespace, type, and memory footprint, sorted so the largest consumers surface first. V$SQLAREA does the same for individual SQL statements and is the source for the address/hash-value pair a targeted purge needs.

Key Points

  • FLUSH BUFFER_CACHE clears all three pools at once — KEEP, RECYCLE, and DEFAULT — there is no ALTER SYSTEM option to flush just one of the three selectively.
  • FLUSH SHARED_POOL spares whatever is currently executing. Statements mid-run at the moment of the flush are not torn out from under the session running them.
  • Sequence caches lose their reserved range on a shared pool flush. A sequence's next-value cache lives in the shared pool; flushing it discards the unused portion of the cached range, so the following NEXTVAL calls skip ahead rather than continuing sequentially — a documented side effect worth knowing before assuming a gap in sequence numbers means something went wrong, per this flush demonstration.
  • Both clauses run regardless of database state for FLUSH SHARED_POOL — mounted or dismounted, open or closed — which makes it usable even during limited-availability maintenance windows.
  • Oracle's own reference names the buffer cache clause a test-only tool. The documentation states plainly that subsequent queries after a buffer cache flush see no hits, only misses, until the cache rewarms.
  • FLUSH GLOBAL CONTEXT is a separate, narrower clause. It clears only global application context information from the shared pool and leaves everything else — cached SQL, PL/SQL, and dictionary data — untouched.

Insights and Best Practices

Plan-testing is the legitimate case for FLUSH SHARED_POOL

When comparing two versions of a query or testing whether a hint changes the optimizer's chosen plan, a warm shared pool can mask the comparison — the second version might reuse cached statistics-gathering overhead or benefit from dictionary rows the first version already pulled into cache. Flushing the shared pool between test runs on a non-production instance removes that variable, so the comparison measures the query itself rather than residual cache state from the previous run.

Cold-cache benchmarking is the case for FLUSH BUFFER_CACHE

A benchmark demonstration against a duplicated dba_tables copy shows the effect directly: a first run against a table takes roughly two seconds to hard-parse the SQL and pull blocks from disk; the identical query run again immediately after takes a fraction of a second because both the parsed plan and the data blocks are already cached. Flushing the shared pool alone before a third run pushes the elapsed time back up only slightly, since the blocks are still cached. Flushing the buffer cache alone, leaving the shared pool warm, pushes it up substantially more, since every block now has to come from disk again. Flushing both together returns elapsed time close to the original cold-start baseline — confirmed in this worked benchmark. That pattern is exactly why both clauses exist together: measuring a rewritten query's true cost means starting from the same cold state every time, not from whatever the previous test run happened to leave behind.

Why neither belongs on a busy production instance

A production shared pool accumulates its working set of commonly executed statements over hours or days. Flushing it forces every one of those statements to hard-parse again, concentrating CPU and latching overhead into a short window right after the flush — a self-inflicted load spike on an instance that was otherwise running normally. A production buffer cache flush is worse in a different way: any session running a concurrent query against data that was in cache a moment ago now waits on physical I/O it did not have to wait on before, and a query running at the exact moment of the flush is not guaranteed a consistent result from a stable cache state. Oracle's documentation frames the buffer cache clause as a test-database tool for exactly this reason.

Reach for the surgical option first

DBMS_SHARED_POOL.PURGE targeting one named package, procedure, or cursor accomplishes what a full shared pool flush is often reached for — clearing a stale cached execution plan after a statistics change, for instance — without forcing every other cached statement in the instance to reparse. Querying V$DB_OBJECT_CACHE or V$SQLAREA first to confirm exactly which object needs clearing turns a system-wide flush into a targeted one.

When to Use This

  • Testing whether a rewritten query or a new index actually changes the optimizer's chosen execution plan, without a cached plan from the old version masking the comparison.
  • Cold-cache benchmarking a suite of queries from an identical starting point, so elapsed-time comparisons reflect the queries rather than leftover cache state.
  • Reproducing a first-execution hard-parse cost during query tuning work, on a test or development instance.
  • Measuring whether Database Smart Flash Cache or buffer pool sizing changes actually affect physical read counts, starting from a known-empty cache.
  • Clearing a single stale cached object with DBMS_SHARED_POOL.PURGE after a statistics or dependency change, without a full instance-wide flush.

Troubleshooting Common Issues

ORA-01031: insufficient privileges on either clause. Issuing FLUSH SHARED_POOL or FLUSH BUFFER_CACHE requires the ALTER SYSTEM system privilege. Confirm the connecting account holds it, or connect AS SYSDBA for a test-instance session.

Performance drops sharply right after a flush and stays down. This is the expected, documented behavior, not a bug — every subsequent query after FLUSH BUFFER_CACHE reads from disk until the cache rewarms, and every statement after FLUSH SHARED_POOL reparses on next execution. On production, this is the reason both clauses are reserved for test systems.

Sequence values jump unexpectedly after a shared pool flush. The sequence's cached range was discarded along with everything else in the shared pool. This is expected: the default sequence cache size means the flush can skip ahead by that many values, and it is not evidence of a corrupted sequence.

A single object needs clearing, not the whole pool. Reach for DBMS_SHARED_POOL.PURGE against the specific package, procedure, or cursor, sourced from V$DB_OBJECT_CACHE or V$SQLAREA, rather than a full FLUSH SHARED_POOL.

References

Posts in this series