ORA-4031診斷分析

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
sys@PES1DB2>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′;

sys@PES1DB2>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
sys@PES1DB2>

延續:主機收到告警
告警描述:#(系統+計算)內存使用率持續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%。

sys@PES1DB1>show parameter pga

NAME TYPE VALUE
———————————— ———————- ——————————
pga_aggregate_target big integer 1000M

sys@PES1DB1>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
sys@PES1DB1>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)
參考中還有更加詳細的解釋和介紹。
//blog.itpub.net/26736162/viewspace-2137064/