Monitoring the Oracle shared pool
Queries to check free memory in the Oracle shared pool, flush it and inspect its chunks.
The shared pool is part of the SGA. It caches parsed SQL, PL/SQL and data dictionary information. The queries below check its free memory, flush it and inspect its chunks.
Check the free memory
SELECT * FROM v$sgastat WHERE name = 'free memory';
Flush the shared pool
This removes all information from the shared pool:
alter system flush shared_pool;
Inspect the shared pool chunks
select * from x$ksmsp;
References
- https://hiteshgondalia.wordpress.com/2014/07/04/monitoring-shared-pool-usage/
- http://vsbabu.org/oracle/sect13.html