Basics of the Shared Pool in Oracle 12c - dummies

Basics of the Shared Pool in Oracle 12c

By Chris Ruel, Michael Wessler

Certain objects and devices in Oracle 12c are used frequently. Therefore, it makes sense to have them ready each time you want to do an operation. Furthermore, data in the shared pool is never written to disk.

The shared pool itself is made up of four main areas:

  • Library cache

  • Dictionary cache

  • Server Result cache

  • Reserved Pool

A cache is a temporary area in memory created for a quick fetch of information that might otherwise take longer to retrieve. For example, the caches mentioned in the preceding list contain precomputed information. Instead of a user having to compute values every time, the user can access the information in a cache.

The library cache in Oracle 12c

The library cache is just like what it’s called: a library. More specifically, it is a library of ready-to-go SQL statements.

Each time you execute a SQL statement, a lot happens in the background. This background activity is called parsing. Parsing can be quite expensive in terms of processing power.

During parsing, some of these things happen:

  • The statement syntax is checked to make sure you typed everything correctly.

  • The objects you’re referring to are checked. For example, if you’re trying to access a table called EMPLOYEE, Oracle makes sure it exists in the database.

  • Oracle makes sure that you have permission to do what you’re trying to do.

  • The code is converted into a database-ready format. The format is called byte-code or p-code.

  • Oracle determines the optimum path or plan. This is by far the most expensive part.

Every time you execute a statement, the information is stored in the library cache. That way, the next time you execute the statement not much has to occur (such as checking permissions).

The dictionary cache in Oracle 12c

The dictionary cache is also frequently used for parsing when you execute SQL. You can think of it as a collection of information about you and the database’s objects. It can check background-type information.

The dictionary cache is also governed by the rules of the Least Recently Used (LRU) algorithm: If it’s not the right size, information can be evicted. Not having enough room for the dictionary cache can impact disk usage.

Because the definitions of objects and permission-based information are stored in database files, Oracle has to read disks to reload that information into the dictionary cache. This is more time-consuming than getting it from the memory cache. Imagine a system with thousands of users constantly executing SQL . . . an improperly sized dictionary cache can really hamper performance.

Like the library cache, you can’t control the size of the dictionary cache directly. As the overall shared pool changes in size, so does the dictionary cache.

The server result cache in Oracle 12c

The server result cache has two parts:

  • SQL result cache: This cache lets Oracle see that the requested data — requested by a recently executed SQL statement — might be stored in memory. This situation lets Oracle skip the execution part of the, er, execution, for lack of a better term, and go directly to the result set if it exists.

    The SQL result cache works best on relatively static data (like the description of an item on an e-commerce site).

    Should you worry about the result cache returning incorrect data? Not at all. Oracle automatically invalidates data stored in the result cache if any of the underlying components are modified.

  • PL/SQL function result cache: The PL/SQL function result cache stores the results of a computation. For example, say you have a function that calculates the value of the dollar based on the exchange rate of the Euro. You might not want to store that actual value since it changes constantly.

    Instead, you have a function that calls on a daily or hourly rate to determine the value of the dollar. In a financial application, this call could happen thousands of times an hour. Therefore, instead of the function executing, it goes directly to the PL/SQL result cache to get the data between the rate updates. If the rate does change, Oracle re-executes the function and updates the result cache.

The reserved pool in Oracle 12c

When Oracle needs to allocate a large chunk (over 5 KB) of contiguous memory in the shared pool, it allocates the memory in the reserved pool. Dedicating the reserved pool to handle large memory allocations improves performance and reduces memory fragementation.

Least recently used algorithm in Oracle 12c

If the library cache is short on space, objects are thrown out. Statements that are used the most stay in the library cache the longest. The more often they’re used, the less chance they have of being evicted if the library cache is short on space.

The library cache eviction process is based on what is called the Least Recently Used (LRU) algorithm. If your desk is cluttered, what do you put away first? The stuff you use the least.

You can’t change the size of the library cache yourself. The shared pool’s overall size determines that. If you think too many statements are being evicted, you can boost the overall shared pool size if you’re tuning it yourself. If you’re letting Oracle do the tuning, it grabs free memory from elsewhere.