interval partition自動新增分割槽引起的shared pool 4031錯誤

某exdata下8節點RAC大型資料倉儲，11.2.0.3.23版本 ，其中一節點監控警報狀態異常，檢視資料庫後臺alert log最開始報
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DBMS_LOGSTDBY","PLMCD^e481b9fe","kglHeapInitialize:temp")
往後報：ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KKSSP^50","kglseshtSegs")
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DBMS_LOGSTDBY","PLMCD^e481b9fe","kglHeapInitialize:temp")
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^78faa4dd","kglHeapInitialize:temp")
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","EV_EVENT","PRTMV^18f08f0b","Allocate kctph[]/ckyph[]/ckyprt[] array")
.....
等等一系列的4031，shared pool無法分配。

進入sqlplus，發現sqlplus裡執行sql 都返回 4031錯誤。

遇到shared pool 4031錯誤，導致大部分sql無法解析的情況，一般都是由於shared pool 碎片化嚴重引起。

應急方法是 重啟資料庫或執行重新整理shared pool 。

我們採取重啟暫時來解決故障，做了幾次  checkpoint  和 切換日誌 之後 ，使用shutdown immediate 方式關閉資料庫，但但發現oracle 一直停不下來，檢視後臺日誌 發現一直卡在MMON程式上，
Stopping background process MMON
Fri Jan 08 19:44:50 2016
Background process MMON not dead after 30 seconds
Killing background process MMON
License high water mark = 248

最後 還是重開 視窗 直接 shutdown abort， 重啟之後，資料庫狀態恢復正常。


於是 便開始 分析 trace檔案，查詢導致該故障的原因。實際當時幾個人分析了好久一直無法定位，最後還是透過SR來解決問題，現在來闡述最後的分析結果。

從alert 日誌上看，                                                                                                                                                                                            
 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DBMS_LOGSTDBY","PLMCD^e481b9fe","kglHeapInitialize:temp")
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^e481b9fe","kglHeapInitialize:temp")
 ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","EV_EVENT","PRTMV^18f08f0b","Allocate kctph[]/ckyph[]/ckyprt[] array")
出現的頻率較多。

再檢視報錯之前的資料庫是否存在什麼操作，實際上這一點很容易讓人忽視，但往往是觸發某些錯誤的源頭，資料庫一直有出現   ADDED INTERVAL PARTITION的操作出現，而SR最後也將這個作為導致問題的原因。                                                                                  
                                                                                                                        
Fri Jan 08 07:49:18 2016
TABLE IDL.DM_COR_DPS_AC_BAL_DTL_FALL_D_P: ADDED INTERVAL PARTITION SYS_P298027 (42375) VALUES LESS THAN (TO_DATE(' 2016-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
Fri Jan 08 07:49:32 2016
TABLE IDL.DM_COR_DPS_CU_BAL_DTL_FALL_D_P: ADDED INTERVAL PARTITION SYS_P298028 (42375) VALUES LESS THAN (TO_DATE(' 2016-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
Fri Jan 08 07:58:51 2016
TABLE IDL_RDM.BACA_INDIV_APPL: ADDED INTERVAL PARTITION SYS_P298029 (42375) VALUES LESS THAN (TO_DATE(' 2016-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
Fri Jan 08 09:03:19 2016

從trace 檔案中呈現當時存在 大量的  SGA: allocation forcing component growth等待事件。

分析個subpool的使用情況：
Memory Utilization of Subpool 1
================================
      Allocation Name            Size    
___________________________  ____________
"free memory              "      83432448


Memory Utilization of Subpool 2
================================
      Allocation Name            Size    
___________________________  ____________
"free memory              "     132365824


Memory Utilization of Subpool 3
================================
      Allocation Name            Size    
___________________________  ____________
"free memory              "      78452768


==============================
Memory Utilization of Subpool 4
================================
      Allocation Name            Size    
___________________________  ____________
"free memory              "      63742208

Memory Utilization of Subpool 5
================================
      Allocation Name            Size    
___________________________  ____________
"free memory              "     144281784


Memory Utilization of Subpool 6
================================
      Allocation Name            Size    
___________________________  ____________
"free memory              "     105139176


Memory Utilization of Subpool 7
================================
      Allocation Name            Size    
___________________________  ____________
"free memory              "     230405048


在第三subpool 發現了一個異常大小的元件，這個竟然差不多到了12G。                      

"PRTMV                    "   12293877592                                   
                 

而該資料庫採用手工記憶體管理，shared pool的大小為7G，sga max 為20G。

直接在MOS上搜PRTMV會發現因PRTMV分配過高而導致4031的問題非常多，結合該庫上時常有  ADDED INTERVAL PARTITION的DDL操作，                                                               

最終oracle給出的說法是就是由於在間隔分割槽表上同時執行DML和 DDL導致給shared pool  的PRTMV  過多的記憶體導致的問題。                                                                                                                      
 Bug 19461270 - high PRTMV allocations in shared pool executing concurrent DML and DDLs on interval partitioned tables (文件 ID 19461270.8)        


給出的建議為：             

1.升級到11.2.0.3.28，再打上  patch 19461270。              
or 
2. 升級到11.2.0.4.0版本，再打上  patch 19461270。  
or 
3. Use the following workaround: Flush the shared pool 

You could periodically monitor the utilization of heap 'PRTMV'. 
In particular after partitioned table maintenance were done. 

In case 'PRTMV' would increase to an large extend (for example > 200 MB) 
you could flush the shared pool to avoid ORA-4031. 

SQL> select name,bytes from v$sgastat where name='PRTMV';