LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon