Oracle interview question on shared pool:
The shared pool is one of the most critical memory components particularly when it comes to how SQL executes.
The way you write SQL doesn’t just effect the individual SQL statement itself. The combination of all SQL that executes against the database has a tremendous effect on overall performance and scalability due to how it affects the shared pool.
Shared pool is where Oracle caches program data. Every SQL statement executed will have its parsed form stored in the shared pool. The area within the shared pool where statements are stored is called the library cache.
Even before any statement is parsed, Oracle will check the library cache to see if that same statement already exists there. If it does exist, then Oracle will retrieve and use the cached information instead of going through all the work to parse the same statement again. The same thing goes for any PL/SQL code you run.
The really nifty part is that no matter how many users may want to execute the same SQL statement, Oracle will typically only parse that statement once and share it among all users who want to use it. Maybe you can see where the shared pool gets its name.
SQL statements you write aren’t the only things stored in the shared pool. The system parameters Oracle uses will be stored in the shared pool as well. In an area called the dictionary cache, Oracle will also store information about all the database objects. In general, Oracle stores pretty much everything you could think of in the shared pool. As you can imagine, that makes the shared pool a very busy and important memory component.
Since the memory area allocated to the shared pool is finite, statements that originally get loaded may not stay there for very long as new statements are executed. A Least Recently Used (LRU) algorithm regulates how objects in the shared pool are managed. To borrow an accounting term, it’s similar to a FIFO (First In First Out) system. The basic idea is that statements that are used most frequently and most currently are what are retained. Unlike a straight FIFO method, how frequently the same statements are used will effect how long they remain in the shared pool.
If you execute a SELECT statement at 8 A.M.and then execute the same statement again at 4 P.M., the parsed version that was stored in the shared pool at 8 A.M. may not still be there. Depending on the overall size of the shared pool and how much activity it has between 8 A.M. and 4 P.M., as Oracle needs space to store the latest information throughout the day, it will simply reuse older areas and overlay newer information into them. But, if you execute a statement every few seconds throughout the day, the frequent reuse will cause Oracle to retain that information over something else that may have originally been stored later than your statement but hasn’t been executed frequently, or at all, since it was loaded.