My Oracle Support Banner

ORA-4031 And Large Allocation Of "mvobj part des" Memory While Truncating Partitions (Doc ID 1500356.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.

Symptoms

While performing partition maintenance operations in an environment where the shared pool uses multiple subpools, ORA-4031 errors are encountered, such as:

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","INDEX_ORDER_STATEID","partitioning d","kglhin: temp")
ORA-04031: unable to allocate 296 bytes of shared memory ("shared pool","INDEX_ORDER_STATEID","partitioning d","table partition description")
ORA-04031: unable to allocate 296 bytes of shared memory ("shared pool","WRH$_FILESTATXS","partitioning d","table partition description")
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","WRH$_FILESTATXS","partitioning d","kglhin: temp")
ORA-04031: unable to allocate ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","WRH$_FILESTATXS","partitioning d","kglhin: temp")
ORA-04031: unable to allocate 48 bytes of shared memory ("shared pool","SUBRELATIONSHIP","mvobj part des","keyPP : kkpodBuildKeyValsArr"

The following conditions are met:

  1. The shared pool is comprised of multiple subpools
  2. Unusual large allocations of the "mv obj desc" heap chunks are found

A simple testcase can confirm this behaviour:

CREATE TABLE abc (
        sid NUMBER not null,
        phonenum VARCHAR2(20) not null,
        partitionvalue VARCHAR2(80) not null
    )
    PARTITION BY LIST (partitionvalue) (
        PARTITION par_1 VALUES ('PAR_1')
    );

REM ################
REM Add some partitions
REM ################
BEGIN
    FOR i IN 2..12000     LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE abc ADD PARTITION PAR_' || i ||
            ' VALUES (''' || 'PAR_' || i || ''')';
    END LOOP;
    COMMIT;
END;
/

REM ################
REM Insert some data
REM ################
BEGIN
    FOR i IN 1..12000
    LOOP
        FOR j IN 1..200
        LOOP
            INSERT INTO abc(sid, phonenum, partitionvalue)
                VALUES(j+200*(i-1), 99990000, 'PAR_'||i);
        END LOOP;
    END LOOP;
    COMMIT;
END;
/

REM ################
REM Truncate the partitions
REM ################
BEGIN
    FOR i IN 1..6000
    LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE abc TRUNCATE PARTITION PAR_' || i;
    END LOOP;
END;
/


Then check the SGA statistics:

CONNECT / AS SYSDBA
SELECT * FROM (SELECT * FROM v$sgastat ORDER BY bytes DESC) WHERE rownum < 10;
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  mvobj part des               62422032
shared pool  free memory                  29277664
shared pool  PL/SQL DIANA                 18584456
             buffer_cache                 16777216


Please note that the allocation of "mvobj part des" memory is expected while performing partition maintenance operations. Only when the memory remains allocated after the operations are finished, or when the memory allocations are excessively large, then action should be taken. During partition maintenance operations this allocation type increases and stabilizes over time. If it keeps increasing or stabilizes at a very large value, you are probably hitting the issue as described in this document.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.