ORA-4031診斷分析

語言: CN / TW / HK

1.Environment

11.2.0.4 RAC

2.Symptoms

rac的一節點alert日誌一直刷ORA-4031報錯,提示shared pool不足,二節點並沒有此報錯

Sat Oct 09 09:53:30 2021

Errors in file /data1/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_7537104.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","update sys.mon_mods$ set ins...","sga heap(1,0)","kglsim object batch")

Errors in file /data1/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_7537104.trc:

ORA-00604: error occurred at recursive SQL level 2

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","select ts#,file#,block#,cols...","sga heap(2,0)","kglsim object batch")

XXX:/data1/app/oracle$ oerr ora 4031

04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"

// *Cause: More shared memory is needed than was allocated in the shared

// pool or Streams pool.

// *Action: If the shared pool is out of memory, either use the

// DBMS_SHARED_POOL package to pin large packages,

// reduce your use of shared memory, or increase the amount of

// available shared memory by increasing the value of the

// initialization parameters SHARED_POOL_RESERVED_SIZE and

// SHARED_POOL_SIZE.

// If the large pool is out of memory, increase the initialization

// parameter LARGE_POOL_SIZE.

// If the error is issued from an Oracle Streams or XStream process,

// increase the initialization parameter STREAMS_POOL_SIZE or increase

// the capture or apply parameter MAX_SGA_SIZE.

XXXX:/data1/app/oracle$

3.Changes

巡檢時發現

4.Cause

[email protected]>show spparameter sga

SID NAME TYPE VALUE

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

* lock_sga boolean

* pre_page_sga boolean

* sga_max_size big integer 3G

orcl1 sga_target big integer 1472M

* sga_target big integer 3G

兩節點sga設定不一致,正常情況下,sga的設定所有節點生效,預設是*,此環境指定例項,指定例項的引數優先順序更高,雖然*的設定為3G,但是實際上1G的引數生效。

規範的做法是刪除指定例項的sga引數,需要重啟。為了縮小影響,且sga_target引數為動態引數,故重新設定為3G即可。

5.Solution

ALTER SYSTEM SET sga_target='3G' SCOPE=BOTH SID='orcl1';

[email protected]>show spparameter sga

SID NAME TYPE VALUE

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

* lock_sga boolean

* pre_page_sga boolean

* sga_max_size big integer 3G

orcl1 sga_target big integer 3G

* sga_target big integer 3G

[email protected]>

延續:主機收到告警

告警描述:#(系統+計算)記憶體使用率持續10分鐘超過95%,記憶體使用中會發生換頁空間切換,影響實際資料呼叫,可綜合考慮是否擴容

告警時間:2021.10.09 11:21:12

---------

orcl1:/data1/app/oracle$ ps aux | head -1 ; ps aux | sort -rn +3 | head -10

檢視確實是資料庫相關的程序佔用記憶體,進一步分析

8G主機記憶體,SGA 3G,PGA 1G,調整sga為2G,主機記憶體(nmon->m)從96%降為86%。

[email protected]>show parameter pga

NAME TYPE VALUE

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

pga_aggregate_target big integer 1000M

[email protected]>show parameter process

NAME TYPE VALUE

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

aq_tm_processes integer 1

cell_offload_processing boolean TRUE

db_writer_processes integer 4

gcs_server_processes integer 3

global_txn_processes integer 1

job_queue_processes integer 1000

log_archive_max_processes integer 4

processes integer 1000

processor_group_name string

[email protected]>show parameter session

NAME TYPE VALUE

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

java_max_sessionspace_size integer 0

java_soft_sessionspace_limit integer 0

license_max_sessions integer 0

license_sessions_warning integer 0

session_cached_cursors integer 50

session_max_open_files integer 10

sessions integer 1536

shared_server_sessions integer

6.References

Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools (Doc ID 146599.1)

參考中還有更加詳細的解釋和介紹。

http://blog.itpub.net/26736162/viewspace-2137064/