What Is a Shared Pool In Oracle?

In today’s article, we will examine the Shared Pool and its subcomponents, which are involved in almost every event that occurs in the database.

Parsed SQL statements; SQL work plans are an important area where parsing and compiled PL/SQL codes are located.

Shared Pool has many subcomponents.

Library Cache

Stores parsed unique SQL sentences.

These SQL statements may be SQL statements executed by the user or application, or they may be recursive SQL sentences that are run internally in the background while a SQL statement is running.

In addition to SQL statements, it includes SQL execution plans, parse and compiled PL/SQL codes, and a private SQL field in a shared server architecture.

When statistics are collected for a table or index, the database by default deletes SQL statements that use the object whose statistics are collected from the common SQL area.

When deleted SQL sentences are run again, the database parses the SQL sentence again and saves it in the shared SQL area with new statistical values.

When the database name is changed, the common SQL space is completely emptied.

Additionally, the Shared Pool space is completely emptied with the following command.

Process Steps When Executing a SQL Statement

1. In the Shared SQL field, it is checked whether the SQL sentence is syntactically and semantically identical.

2. If the same sentence exists, the database uses this SQL sentence and the execution plan.

This process is called “soft parse” or “library cache hit”.

3. If the same sentence does not exist, it calculates the execution plan of the new SQL sentence and saves it in the common SQL area.

This operation is called “hard parse” or “library cache miss”.

Important issue: Since the ASCII codes of uppercase and lowercase letters are different, uppercase and lowercase letters are taken into account when calculating.

If there is no space left in the Library Cache, the oldest unused SQL sentence or PL/SQL code common SQL sentence is saved.

Data Dictionary Cache

This is the area where detailed information about the objects in our database is kept.

Columns of a table, tablespace information, who has access to the table, etc. Contains information.

Therefore, it is a frequently used field when parsing SQL statements.

In Data Dictionary Cache, data is kept as rows, so it is also known as “row cache”.

Server Result Cache

The database stores the results of the query or query parts in this area.

Not to be confused with Database Buffer Cache. In Database Buffer Cache, data is brought from data files.

In Server Result Cache, certain calculations are made on the incoming data.

For example, an application may constantly call the exchange rate calculation query.

In this case, rather than calculating the exchange rate each time and printing the result on the screen, it would be more efficient to save the result to the result cache and retrieve it from there in the next query.

We can use the RESULT_CACHE hint in queries as follows to store a specific query result in the result cache.

Large Pool

This is the area used for large operations such as taking backups, export/import and running parallel SQL statements.

Java Pool

If we are using JVM (Java Virtual Machine) and we have java codes running in our database, the most frequently used java codes are stored in this memory area.

Streams Pool

It is the area used by Oracle Streams where queue messages are recorded. By default its size is zero.