[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.相关链接：
--//找到如下链接：https://support.oracle.com/knowledge/Oracle%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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2762923/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
