The Oracle Database Server is made up of two main components; the instance and database. The instance consists of memory structures and background processes, whiles the database is a collection of physical database files. If we look at the Oracle Database architecture we can see that there is an organized layout for the memory structures, in which we can find the main memory structure and it’s sub-memory structures.
This blog is not intended to cover all aspects of the Oracle Database architecture so I will just jump straight to the point. Shared Pool is a memory structure within the instance. The shared pool contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures.. This make the shared pool a vital location in reference to it’s involvement during a SQL statement life cycle.
What’s The “BIG FUSS” – Clearing Shared Pool In Production Database?
The key issue with flushing the shared pool is that there will be a performance hit to the database because there are no parsed sql’s or execution plans optimized in memory anymore: they will deleted when you flush the shared pool. In turn the database has to perform a hard parse on sql’s. This is horrible because it is a cpu bound operation and can be very costly to the database. Anybody who knows anything about production systems knows that performance is key. Only in rare cases do we see flushing the shared pool being beneficial to performance.
Now with that being said, I’ve seen environments where DBA’s routinely flush the production database shared pool to gain a performance boost. Say whattttttttt ? ?
Yes, I agree…it is a crazy, barbaric, and uncivilized act to flush the shared pool of a production database simply because of performance issues. Flushing the shared pool should be a last resort that hopefully will never be utilized. Poorly designed OLTP systems often times fail to leverage BIND VARIABLES, AMM, ASMM, adequate memory allocation for SGA, and several other key factors that can help avoid performance issues.
Flushing the shared pool of a production system is not cool and should only be leveraged as a last resort option.
By the way, below is the command to flush your shared pool. Use responsibly.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
