Jump to Navigation

185 - alter system flush shared_pool, buffer_cache

This command lets you clear all data from the shared pool in the system global area (SGA).
The shared pool stores Cached data dictionary information and Shared SQL and PL/SQL areas for SQL statements,
stored procedures, function, packages, and triggers. This statement does not clear shared SQL and PL/SQL areas for items
that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted
or mounted, open or closed.

SQL> alter system flush shared_pool;


The FLUSH BUFFER_CACHE clause lets you clear all data from the buffer cache in the system global area (SGA).
This clause is intended for use only on a test database. Do not use this clause on a production database, because as a result of
this statement, subsequent queries will have no hits, only misses.

SQL> alter system flush buffer_cache;

* Check shared pool status :

SQL> select * from( select name, bytes/(1024*1024) MB from v$sgastat where pool ='shared pool' order by bytes desc ) where rownum < 20;

* Check shared pool free memory :

SQL> SELECT * FROM v$sgastat WHERE name = 'free memory' AND pool = 'shared pool';

* Check DB buffer cash status :

SQL> 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;

Main menu 2

Story | by Dr. Radut