How to Resolve ORA-04031: unable to allocate 4216 bytes of shared memory

Causes of ORA-04031

Found ORA-04031 in the alert log.

ORA-04031: unable to allocate 4216 bytes of shared memory ("shared pool","unknown object","sga heap","library cache")

Several kinds of memory problem can result in ORA-04031. As you can see, the above error complained specifically about the shared pool. Therefore, your database could be in one of the following situations:

Small shared pool size

If your database had survived through the most critical pressure ever, the size should be sufficient already, just leave it unchanged. Otherwise, you can raise the shared pool related parameters, such as MEMORY_TARGET (under AMM), SGA_TARGET (under ASMM), or SHARED_POOL_SIZE manually to solve ORA-04031.

You may refer to Oracle 19c documentation for more details about memory management:

• AMM: Enabling Automatic Memory Management
• ASMM: Enabling Automatic Shared Memory Management
• Manually: Using Manual Shared Memory Management

Fragmented shared pool

This could be the most common consequences of high library cache during peak hours. There're too many small cursors generated and allocated scatteringly in the shared pool. In this post, I will focus on the solutions to this type of ORA-04031.

Solutions to ORA-04031

We solve the error by the following ways.

1. Flush SHARED_POOL
2. Increase MEMORY_TARGET
3. Set CURSOR_SHARING
4. Use Bind Variables

Flush SHARED_POOL

In urgent situations, you can flush shared pool to remove all unused cursors and make the database functional, you will have a peace time for several hours before next ORA-04031. Yes, just several hours, because the root cause is still there.

This action does no harm to your database, but it may take a while to complete. Please don't interrupt it, and just be patient with it.

SQL> alter system flush shared_pool;

System altered.

Flushing shared pool can ease your problem, but it's not the real cure to this repeated ORA-04031. You have to find out the root cause. And, the root cause usually points to badly written SQL statements or PL/SQL.

Increase MEMORY_TARGET

Suppose that your database is using AMM, we plan to increase MEMORY_TARGET to a higher value.

Let's see the current setting of MEMORY_TARGET.

SQL> show parameter memory_target

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 4096M

We changed the value of MEMORY_TARGET within SPFILE scope.

SQL> alter system set memory_target=5g scope=spfile;

System altered.

We should restart the database, then check the status.

SQL> show parameter memory_target

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 5120M

Set CURSOR_SHARING

We should set the parameter to any other value beside EXACT.

For example, SIMILAR can make similar statements in literals to share the same cursor in order to reduce the usage of shared pool.

The available values of Oracle 12.1 or later releases are:

The available values before Oracle 12.1 are:

This solution could save some space of shared pool, but transactions might be a little slower than ever. So, it's a trade-off. Besides, Oracle also warns that Do Not Use CURSOR_SHARING = FORCE as a Permanent Fix of ORA-04031.

Use Bind Variables

When Oracle executes a badly written application which does not use bind variables, slightly different statements in literals may cause enormous amount of small cursors, this makes shared pool tight and fragmented.

Using bind variables by rewriting SQL statements makes your applications (e.g. Stored procedures, Java objects) flexible and dynamic to do millions of execution with the same parsed SQLs, which forces the millions of executions to share one or little cursors and optimizes the shared pool more contiguous. This could be the real cure to ORA-04031.

For example, a parsed SQL statement with bind variables looks like this:

SQL_TEXT
-------------------------------------------------------
SELECT * FROM hr.data_table WHERE sn = :b1 and bn = :b2

In the above statement, :b1 and :b2 represent two compiled bind variables. For more information about binding variables in PL/SQL, please refer to: PL/SQL Dynamic SQL