2017-04-24 DBA日记，触发BUG导致shared pool中的PRTMV memry剧增

背景：

继续进行“ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool",...)”问题分析

分析过程：

1.在上一篇文章中比较草率定义为是由于大量HARD PARSES造成shared pool空间不足，为什么说比较草率，因为在继续研究awr报告时，发现在[sga breaddown difference]中的shared pool有一项是PRTMV由原来的4G激增到26G.

SGA breakdown difference

• ordered by Pool, Name
• N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot

PoolNameBegin MBEnd MB% Diffjavafree memory1,024.001,024.000.00largefree memory2,048.002,048.000.00sharedKGLH04,114.843,729.01-9.38sharedPRTDS1,982.011,446.15-27.04sharedPRTMV4,273.7126,677.13524.21sharedSQLA547.35497.31-9.14sharedSQLP574.31517.21-9.94sharedfree memory12,304.4512,466.081.31streamsfree memory512.00512.000.00 buffer_cache21,632.00128.00-99.41 fixed_sga2.132.130.00 log_buffer20.7120.710.00

2. 于是根据这个情况，搜索support.oracle.com知识库，发现有多个bug与此相关，特摘录如下：

Bug 20635353 - High PRTMV Memory Allocations when Inserting into Partitioned Tables Simultaneously with Partition Maintenance Operations (文档 ID 20635353.8) 转到底部转到底部

Bug 20635353 High PRTMV Memory Allocations when Inserting into Partitioned Tables Simultaneously with Partition Maintenance Operations

This note gives a brief overview of bug 20635353.

The content was last updated on: 17-FEB-2017

Click here for details of each of the sections below.

Affects:

Product (Component) Oracle Server (Rdbms)

Range of versions believed to be affected Versions BELOW 12.2

Versions confirmed as being affected

12.1.0.2 (Server Patch Set)

11.2.0.4

11.2.0.3

Platforms affected Generic (all / most platforms affected)

Fixed:

The fix for 20635353 is first included in

12.2.0.1 (Base Release)

Interim patches may be available for earlier versions - click here to check.

Symptoms:

Related To:

Leak (Memory Leak / Growth)

Shared Pool Affected

Error May Occur

ORA-4031

Memory type "PRTMV"

Can affect SGA memory

Partitioned Tables

Description

This bug is only relevant when using Partitioned Tables

When using a shared pool with more than one subpool and inserting into partitioned

tables that are simultaneously having partition maintenance operations such as

ALTER TABLE ... MOVE PARTITION COMPRESS, if continually increasing allocations

of PRTMV memory are noticed, then this bug may be encountered.

Rediscovery Notes

Analysis of a shared pool heap dump shows many PRTMV allocations marked:

Flags=PIN/TIM/MVR/OBS/[00202801]

A count of these allocations can be done using:

egrep MVR\/ REFdbA_m000_8748.trc | sort | uniq -c | sort -rn

The PRTMV allocations are not released even with a shared pool flush, and have no references

to them from any session or other KGL object.

Workaround

Restart the instance

(1 is default; any value higher than 5 is treated the same as 5). This represents 'number

of attempts' that can be made to acquire a latch needed to unpin the PRTMV memory.

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

Bug:20635353 (This link will only work for PUBLISHED bugs)

Note:245840.1 Information on the sections in this article

--------------------------------------------------------------------------------------------------------

3. 询问用户在此期间作了什么操作，具体如下：

DECLARE

BEGIN

FOR V IN (SELECT TABLE_NAME,'ALTER TABLE '||TABLE_NAME||' DROP SUBPARTITION '||SUBPARTITION_NAME AS CMD

FROM (SELECT T.SUBPARTITION_NAME,

T.TABLE_NAME,

TO_NUMBER(SUBSTR(T.SUBPARTITION_NAME,

INSTR(T.SUBPARTITION_NAME, '_', 1, 3) + 1)) AS A

FROM USER_TAB_SUBPARTITIONS T

WHERE T.TABLE_NAME = 'PART'

AND LENGTH(T.SUBPARTITION_NAME) = 14)

WHERE A BETWEEN 201 AND 299)

LOOP

EXECUTE IMMEDIATE V.CMD;

INSERT INTO MOVE_PARTITION_LOG(TABLE_NAME,CMD) VALUES(V.TABLE_NAME,V.CMD);

COMMIT;

END LOOP;

END;

4. 测试，验证。使用alter session set events '10046 trace name context forever,level 26';跟踪。

解决方案：

1.打补丁，升级版本

2. 临时处理，在进行分区维护的时间点定在工作时间外，在完成分区操作后，执行alter system flushed shared_pool;

然后再通过awr报告和如下命令确认：

select * from SYS.V_$SGASTAT where pool='shared pool'

order by 3 desc;

3.如flushed无效，就重启数据库实例。