ORA 4031 error depth parsing

To fully understand the cause of the 4031 error is to understand the execution of the SQL statement and the structure of the Oracle Shared memory.

The communication process diagram between the client and Oracle

The client enters the SQL statement, and the SQL statement reaches the database instance through the network, and the server process accepts the SQL statement. Server process takes the SQL statement and parses the SQL statement into the execution plan before it can be executed.
It is important to note that the process of parsing SQL statements into execution plans consumes a lot of CPU resources and thus creates a problem, such as:
A user performs an SQL statement parsing for execution, and B users are likely to perform the same SQL. How can you make efficient use of CPU resources and not do repetitive parsing?
Thus, Shared pool is created, and Shared pool is an area of memory that is used to cache SQL statements in the SGA and to the execution plan that is parsed.

Oracle instance management
Here, a simple explanation of the process of the communication between the client and Oracle is explained in this diagram, which is managed by the Oracle instance.
1. First of all, the client will be in the same Oracle database established between many of the connection pool connection select a free connection transmission SQL statements (server process is the server process, can be connected to the Oracle instance, set up in the user session).
2. First, the Server Process will first go to the Shared pool to find out if there is already a cached execution plan corresponding to the SQL statement, and if there is a direct execution (the SQL parsing is also called soft parsing).If not, you generate the execution plan yourself and cache the execution (which is also called hard parsing).
3. Shared pool is part of the memory resources of the SGA (Shared global area), which is Shared by all server processes and background processes.
4. Buffer Cache is also an area in SGA to Cache Data extracted from Data files. If there is no Buffer Cache, I/O must be consumed every time the Data is accessed.
5. So when executing a SQL statement execution of corresponding plans to allow access to Data, the server process will first enter the buffer cache to find whether the Data you need, if you have removed directly return not to the Data files to get it into the first buffer cache, and modify the Data in it. Then I’ll go back to the Oracle data file through the physical I/O.
6. In the meantime, the data is modified and another memory area in the SGA is called Redolog Buffer to write the relevant log information. Then write back to the Oracle log file.
7. Return the returned data or information to the client via a connection.
In the above process, we can see that the Shared pool is a core content of SGA, and the classic Oracle4031 error also has a close relationship with this memory area.

Free Cache:
As the name implies, a free memory area in Shared pool.

Library Cache (Library Cache):
The main cache is SQL statements and execution plans that are parsed by SQL sentences.

Raw Cache (dictionary Cache):
The Oracle database’s own information is stored in a data dictionary (for example, how many tables are in the database, how many users are there, how many columns are in the table, and so on).

The general Free Cache and Library Cache in the main three Spaces of Shared Pool are more problematic. We can set the size of the Shared Pool size but not control the size of the Library Cache and Raw Cache.
It is important to understand that the Free Cache is not a contiguous chunk of memory space, but a chunk of memory that is linked by chain links as shown below.

The orange circle represents chunks of memory that are called chunks in Oracle, and these chunks will be grouped according to the size of the chain to be mounted on a chain, from the bottom up to the larger.

Here’s an example:
If there is an SQL statement parsing out of 10 k, then the chain of 8 k – 12 k memory to find such as finding a 11 k blocks then it will be one of the 10 k into the Library Cache, and the remaining 1 k chain again on the corresponding space. This is the memory organization of Free space.

The special reminder here is — when do you need to find the chunk in Free space? The answer is when hard parsing is performed.

So when there is a lot of hard parse, in addition to going to look for the chunk in the Free space will produce a large number of small pieces, so it is possible to produce this kind of situation, and has a large enough Free space but is divided into many small pieces, not suitable for the available memory block. This will result in an Oracle classic 4031 error.

Summarize the background conditions of Oracle’s 4031 error:
A lot of hard parsing produces many small pieces
2. After producing a large number of small fragments, a large SQL statement needs to be resolved.