設定ORACLE自動記憶體管理

標籤：oracle automatic memory management

文章參考：ORACLE 11g官方文檔[Managing Memory]

前言：之前搭建資料庫叢集環境後，現場的維護人員不知何時改為了手動記憶體管理，且參數設定存在不合理，導致客戶plsql執行報錯，如下所示：

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M02/8E/B0/wKioL1jJJV6zwxJqAAAyZKB_JO4302.png-wh_500x0-wm_3-wmp_4-s_537196498.png" title="QQ圖片20170315192507.png" alt="wKioL1jJJV6zwxJqAAAyZKB_JO4302.png-wh_50" />

查看資料庫日誌，有如下提示：

Wed Mar 15 00:15:08 2017

Errors in file /app/oracle/diag/rdbms/resdb/resdb1/trace/resdb1_ora_12320832.trc  (incident=441054):

ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation

 callback")

Incident details in: /app/oracle/diag/rdbms/resdb/resdb1/incident/incdir_441054/resdb1_ora_12320832_i441054.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Wed Mar 15 16:11:30 2017

Errors in file /app/oracle/diag/rdbms/resdb/resdb1/trace/resdb1_ora_8126806.trc  (incident=440838):

ORA-04031: ?·¨·??2 ????2Э?′?"shared pool","SELECT TRIGGER_NAME, TRIGGER...","SQLA","tmp")

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /app/oracle/diag/rdbms/resdb/resdb1/trace/resdb1_m000_10879616.trc  (incident=442639):

ORA-04031: unable to allocate 536 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","SQLA^bbcee4f7"

,"qerixs : rixalo")

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Wed Mar 15 16:11:31 2017

Dumping diagnostic data in directory=[cdmp_20170315161131], requested by (instance=1, osid=10879616 (M000)), summary=[inc

ident=442638].

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Wed Mar 15 16:11:32 2017

Sweep [inc][442639]: completed

查看資料庫記憶體參數設定，並未是自動記憶體管理，設定自動記憶體管理員模式處理。

問題處理過程

記憶體管理：自動記憶體管理、手動記憶體管理

自動記憶體管理：oracle 11g後支援PGA和SGA的自動記憶體管理，只需設定執行個體的總記憶體大小，根據需要自動分配PGA和SGA大小；

手動記憶體管理：如果想要手動管理記憶體設定，這個就需要DBA根據環境需要進行適當設定了。

自動記憶體管理設定：只需修改初始化參數設定MEMORY_TARGET和選擇性參數設定MEMORY_MAX_TARGET

設定自動記憶體管理步驟：

1.以sysdba許可權的使用者登入資料庫

查看當前資料庫SGA_TARGET和PGA_AGGREGATE_TARGET的參數設定

SQL> show parameter sga_target;

NAME                                 TYPE        VALUE

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

sga_target                           big integer 0

SQL> show parameter pga_aggregate_target;

NAME                                 TYPE        VALUE

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

pga_aggregate_target                 big integer 160000M

執行如下SQL查詢語句，確認執行個體啟動時分配PGA的最大值

SQL> select value from v$pgastat where name=‘maximum PGA allocated‘;

     VALUE

----------

4620300288

根據如下公式計算其參數值：

memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)

2.初始化參數MEMORY_MAX_TARGET 

首先查看當前資料庫中MEMORY_MAX_TARGET參數

SQL> show parameter MEMORY_MAX_TARGET ;

NAME                                 TYPE        VALUE

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

memory_max_target                    big integer 0

SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET = 160000M SCOPE = SPFILE SID=‘*‘;

ALTER SYSTEM SET MEMORY_TARGET = 160000M SCOPE = SPFILE SID=‘*‘;

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE SID=‘*‘;

3.重啟資料庫

4.自動記憶體管理監控

The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

SQL> select * from v$memory_target_advice order by memory_size;

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M00/8E/B4/wKiom1jJdCLgUSq-AADO19DKzJ8568.jpg-wh_500x0-wm_3-wmp_4-s_1675737324.jpg" title="1.jpg" alt="wKiom1jJdCLgUSq-AADO19DKzJ8568.jpg-wh_50" />

本文出自 “Secdata Share Blog” 部落格，請務必保留此出處http://secdata.blog.51cto.com/11588948/1907057

設定ORACLE自動記憶體管理