[20210315]acknowledge over PGA limit.txt
[20210315]acknowledge over PGA limit.txt
--//測試環境，18c我感覺執行sql語句很慢，看了一下awr報表發現如下：
1.環境:
SYS@aaa.bbb.ccc.ddd:1521/orcl> @ ver1
SYS@aaa.bbb.ccc.ddd:1521/orcl> @ prxx
==============================
BANNER : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.awr報表以及其它資訊:
--//awr報表：10-11點的情況
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Total Wait Avg % DB Wait
Event Waits Time (sec) Wait time Class
------------------------------ ----------- ---------- --------- ------ --------
log file sync 68,219 1616.8 23.70ms 50.3 Commit
acknowledge over PGA limit 40,608 1105.4 27.22ms 34.4 Schedule
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB CPU 419.2 13.0
cursor: pin S wait on X 33 35.4 1072.39ms 1.1 Concurre
db file sequential read 2,938 26.5 9.03ms .8 User I/O
control file sequential read 27,148 7.8 287.41us .2 System I
db file scattered read 291 3.7 12.82ms .1 User I/O
latch: shared pool 482 3.2 6.74ms .1 Concurre
library cache lock 5 1.6 327.57ms .1 Concurre
PGA memory operation 55,229 1.5 27.01us .0 Other
--//跑在虛擬機器上log file sync上有點慢，也許是正常的。另外我發現使用很多交換。
# free
total used free shared buff/cache available
Mem: 16167796 6423972 299116 5698288 9444708 4604848
Swap: 16773116 594604 16178512
SYS@aaa.bbb.ccc.ddd:1521/orcl> show parameter pga
NAME TYPE VALUE
---------------------- ------------- ------------
_pga_max_size big integer 2003140K
pga_aggregate_limit big integer 3158M
pga_aggregate_target big integer 1579M
--//嗯，有人定義_pga_max_size隱含引數，難道團隊其它人已經發現問題了。我看了alert沒看出問題。
SYS@aaa.bbb.ccc.ddd:1521/orcl> show parameter processes
NAME TYPE VALUE
------------------------------------ --------- -----------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 4000
log_archive_max_processes integer 4
processes integer 320
SYS@aaa.bbb.ccc.ddd:1521/orcl> @ ev_name "acknowledge over PGA limit"
SYS@aaa.bbb.ccc.ddd:1521/orcl> @ prxx
==============================
EVENT# : 5
EVENT_ID : 3767648750
NAME : acknowledge over PGA limit
PARAMETER1 : limit
PARAMETER2 : margin
PARAMETER3 : growth
WAIT_CLASS_ID : 2396326234
WAIT_CLASS# : 10
WAIT_CLASS : Scheduler
DISPLAY_NAME : acknowledge over PGA limit
CON_ID : 0
PL/SQL procedure successfully completed.
--//也就是PGA記憶體有點緊張，我自己很少遇到這類PGA相關問題。
SYS@aaa.bbb.ccc.ddd:1521/orcl> @ pga 10
NAME VALUE_MB
------------------------------ ----------
aggregate PGA target parameter 0
total PGA inuse 7.55566406
total PGA allocated 8.37695313
over allocation count 0
PROFILE CNT PERCENTAGE
--------------------------------- ---------- ----------
workarea executions - optimal 10504963 100
workarea executions - onepass 18 0
workarea executions - multipass 0 0
SPID SID SERIAL# MACHINE CLIENT_INFO Program PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------ ---------- ---------- -------- -------------------- -------------------------- ------------ ------------- -----------
3096 389 59054 xxxp2 oracle@xxxp2 (MMNL) 1926510227 2046005603 2046005603
3165 139 12465 xxxp2 oracle@xxxp2 (AQPC) 265719187 266965347 266965347
3168 268 15527 xxxp2 oracle@xxxp2 (W003) 110302571 110576875 115361003
4585 136 52897 xxxp2 oracle@xxxp2 (W006) 110197355 110380267 115295467
3162 15 51178 xxxp2 oracle@xxxp2 (W002) 109718307 110249195 111101163
3185 379 65424 xxxp2 oracle@xxxp2 (W004) 109544699 110511339 111363307
4596 263 35061 xxxp2 oracle@xxxp2 (W007) 109536611 111101163 111363307
3078 386 13911 xxxp2 oracle@xxxp2 (W000) 109500115 109987051 111559915
4581 25 59554 xxxp2 oracle@xxxp2 (W005) 109403451 110052587 111232235
3082 133 24578 xxxp2 oracle@xxxp2 (W001) 109148539 109921515 111690987
10 rows selected.
--//1926510227/1024/1024/1024 = 1.79420G,不知道後臺程式MMNL消耗為什麼這麼大。
3.如何解決：
--//查詢相關文件：
The Memory Monitor Light (MMNL) process is a new process in 10g which works with the Automatic Workload Repository new
features (AWR) to write out full statistics buffers to disk as needed.
SELECT indx,ksuprpnm,TO_CHAR(ksuprflg,'XXXXXXXXXXXXXXXX'),KSUPRPID
FROM x$ksupr
WHERE BITAND(ksuprflg,4) != 4 and KSUPRPID is not null and ksuprpnm like '%MMNL%'
ORDER BY indx ;
INDX KSUPRPNM TO_CHAR(KSUPRFLG,'XXXXXXXXXXXXXXXX KSUPRPID
---- ------------------- ---------------------------------- --------
35 oracle@xxxp2 (MMNL) 2 3096
--//理論這個程式可以kill掉，下午再看看。我在我的測試環境下測試kill應該沒有問題，不過保險起見我決定採用如下：
alter system enable restricted session;
alter system disable restricted session;
--//主要原因是安全，而且這臺機器平時很少人使用，可以這樣操作。
# ps -ef | egrep "ora_mmo[n]|ora_mmn[l]"
oracle 3092 1 0 2020 ? 04:35:22 ora_mmon_orclcdb
oracle 3096 1 0 2020 ? 04:02:35 ora_mmnl_orclcdb
SYS@aaa.bbb.ccc.ddd:1521/orclcdb> alter system enable restricted session;
System altered.
SYS@aaa.bbb.ccc.ddd:1521/orclcdb> alter system disable restricted session;
System altered.
--//注意一定要在cdb層面操作,開始在pdb下無效。
# ps -ef | egrep "ora_mmo[n]|ora_mmn[l]"
oracle 12980 1 0 15:42 ? 00:00:00 ora_mmnl_orclcdb
oracle 12982 1 13 15:42 ? 00:00:01 ora_mmon_orclcdb
--//你可以發現程式號已經發生了變化。
SYS@aaa.bbb.ccc.ddd:1521/orclcdb> @ pga 10
NAME VALUE_MB
------------------------------ ----------
aggregate PGA target parameter 1579
total PGA inuse 1119.34277
total PGA allocated 1224.88281
over allocation count 7546465
PROFILE CNT PERCENTAGE
-------------------------------- ---------- ----------
workarea executions - optimal 43834373 100
workarea executions - onepass 19 0
workarea executions - multipass 0 0
old 14: WHERE ROWNUM <= &1
new 14: WHERE ROWNUM <= 10
SPID SID SERIAL# MACHINE CLIENT_INFO Program PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------ ---------- ---------- ------------------------------ -------------------- -------------------------- ------------ ------------- -----------
3168 268 15527 xxxp2 oracle@xxxp2 (W003) 110322819 110642411 115361003
4585 136 52897 xxxp2 oracle@xxxp2 (W006) 110197355 110380267 115295467
3162 15 51178 xxxp2 oracle@xxxp2 (W002) 109718307 110249195 111101163
3185 379 65424 xxxp2 oracle@xxxp2 (W004) 109544699 110511339 111363307
4596 263 35061 xxxp2 oracle@xxxp2 (W007) 109536611 111101163 111363307
3078 386 13911 xxxp2 oracle@xxxp2 (W000) 109500115 109987051 111559915
4581 25 59554 xxxp2 oracle@xxxp2 (W005) 109403451 110052587 111232235
3082 133 24578 xxxp2 oracle@xxxp2 (W001) 109148539 109921515 111690987
3105 10 3779 xxxp2 oracle@xxxp2 (MARK) 63219083 63344995 63344995
12987 393 4436 xxxp2 oracle@xxxp2 (M000) 27610243 38307723 38307723
10 rows selected.
--//我還做了如下操作：
# swapoff -a
# swapon -a
# free
total used free shared buff/cache available
Mem: 16167796 4172052 2673308 5939344 9322436 6459588
Swap: 16773116 0 16773116
--//再測試基本感覺不到執行緩慢了。
--//實際上還有1個小問題，就是配置沒有使用HugePages。
# grep -i page /proc/meminfo
AnonPages: 2787088 kB
PageTables: 367088 kB
AnonHugePages: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
--//它導致使用交換檔案，這也許導致執行緩慢的原因。
4.附上pga指令碼：
--//指令碼我從網上找的。
$ cat pga.sql
column name format a30
column machine format a30
SELECT NAME, VALUE/1024/1024 VALUE_MB
FROM V$PGASTAT
WHERE NAME IN ( 'aggregate PGA target parameter',
'total PGA allocated',
'total PGA inuse')
union all
SELECT NAME, VALUE
FROM V$PGASTAT
WHERE NAME IN ('over allocation count');
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
FROM (SELECT name, value cnt, (sum(value) over ()) total
FROM V$SYSSTAT WHERE name like 'workarea exec%');
SELECT *
FROM ( SELECT p.spid,
s.sid,
s.serial#,
s.machine,
s.client_info,
DECODE (s.program, NULL, p.program, s.program) AS "Program",
p.pga_used_mem,
p.pga_alloc_mem,
p.pga_max_mem
FROM v$process p, v$session s
WHERE s.paddr = p.addr
ORDER BY p.pga_used_mem DESC)
WHERE ROWNUM <= &1;
5.相關連結：
--//找到如下連結：%20Database%20Products/2509409_1.html
High Memory Utilization Of MMNL Process (Doc ID 2509409.1)
Last updated on OCTOBER 23, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 19.3.0.0.0 [Release 12.2 to 18]
Information in this document applies to any platform.
Symptoms
Since upgrade to 12.2.0.1, the MMNL process keeps consuming PGA.
From MMNL trace file, the increase in memory is seen in "kgfnConnect2Int hsndef" or "kgfnConnect2Int" structure.
Example from MMNL trace:
30% 53 MB, 2785 chunks: "kgfnConnect2Int hsndef "
pga heap ds=0x7f1bdcfa2260 dsprt=(nil)
30% 53 MB, 2785 chunks: "kgfnConnect2Int hsndef "
pga heap ds=0x7f1bdcfa2260 dsprt=(nil)
A heapdump collected from MMNL trace will look like:
---> HEAP DUMP heap name="pga heap" desc=0x7f3e7ffe8260
Type Count Sum Average
~~~~ ~~~~~ ~~~ ~~~~~~~
perm 3193 187871832 58838.66
BreakDown
~~~~~~~~~
Type Count Sum Average
~~~ ~~~~~ ~~~ ~~~~~~~ ~~~~~~~
perm 3193 187871832 58838.66 91.11
Total = 206211288 bytes 201378.21k 196.66MB
Breakdown of CPRM Chunks (Commented Perm Chunks)
Type Count Sum Average
~~~~ ~~~~~ ~~~ ~~~~~~~
kgfnConnect2Int 7992 159392448 19944.00
Chunk 7f3e6b959020 sz= 65504 perm "perm "
alo=39944
13304 7f3e6b959048 sz= 19944 cprm "kgfnConnect2Int"
13305 7f3e6b95de30 sz= 19944 cprm "kgfnConnect2Int"
13306 7f3e6b962c18 sz= 16 cprm "CPM trailer "
Changes Upgrade to 12.2.0.1.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2762923/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Monitor RDBMS Session UGA and PGA Current And Maximum Usage Over TimeSession
- row_number() over，rank() over，dense_rank() over的區別
- sum()over()和count()over()分析函式函式
- over (partition by)
- Import OverImport
- 全面分析PGA
- pga_aggregate_target 相關總結 -- Oracle PGAOracle
- 認識PGA及PGA_AGGREGATE_TARGET [final]
- mysql自動排序函式dense_rank() over()、rank() over()、row_num() over()用法和區別MySql排序函式
- Oracle：PGA 簡介Oracle
- Oracle PGA詳解Oracle
- Oracle PGA管理（一）Oracle
- oracle PGA 構成Oracle
- 分析函式 over函式
- pga_aggregate_target和_pga_max_size都不能絕對限制實際PGA的使用
- SQL語句中的rank () over ， row_number() over ，rank_dense ()SQL
- [20220223]Index ITL Limit.txtIndexMIT
- 【PGA】通過10g新特性得到PGA的調整建議
- PGA基礎知識
- PGA 文件及個人理解
- oracle10g PGAOracle
- PGA學習筆記筆記
- 如何優化oracle pga優化Oracle
- OVER(PARTITION BY)函式用法函式
- Oracle RDBMS History Over the YearsOracle
- Rsync over SSH with No Password (Crontab)
- Oracle over()函式使用Oracle函式
- over partition簡單使用
- sqlserver2005資料庫映象 的switch over 和fail overSQLServer資料庫AI
- pga_aggregate_target和_pga_max_size都不能絕對限制實際PGA的使用--ora04030
- pga知識點總結
- PGA,sga命中sql查詢SQL
- Oracle PGA引數的管理Oracle
- ORACLE PGA程式全域性區Oracle
- v$pga_target_advice
- oracle PGA管理(演算法)Oracle演算法
- Oracle Automatic PGA Memory ManagementOracle
- 查詢SGA和PGA大小