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
Flushing 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;
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.
Setting CURSOR_SHARING 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.
Using bind variables in SQL statements.
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:
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