Performance

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

Found an error in alert log.
ORA-04031: unable to allocate 4216 bytes of shared memory (“shared pool”,”unknown object”,”sga heap”,”library cache”)

Causes

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:
  1. 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 the problem.

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

  2. 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 problem.

Solutions

  1. Flushing shared pool.
  2. 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 shortage. 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 error. You have to find out the root cause. And, the root cause usually points to badly written SQL statements or PL/SQL.

  3. Setting CURSOR_SHARING parameter to any other value beside EXACT.
  4. 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 as of Oracle 12.1 are:
    CURSOR_SHARING = { EXACT | FORCE }
    You may refer to Oracle 12.1 documentation for more details CURSOR_SHARING

    The available values before Oracle 12.1 are:
    CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
    This solution could save some space of shared pool, but transactions might be a little slower than before. So, it’s a trade-off.

  5. Using bind variables in SQL statements.
  6. 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 the problem.

    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

Leave a Reply

Your email address will not be published. Required fields are marked *