[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.
[20210315]acknowledge over PGA limit.txt
来源：这里教程网
时间：2026-03-03 16:31:27
作者：
编辑推荐：
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
![[oracle] 索引低效，导致read by other session等待事件](https://www.herecours.com/d/file/efpub/2026/03-03/20260303151531841710.jpg "[oracle] 索引低效，导致read by other session等待事件")
热文推荐
- Oracle sqlldr工具功能测试
Oracle sqlldr工具功能测试
26-03-03 - SQLServer 2012复制订阅数据订阅过程
SQLServer 2012复制订阅数据订阅过程
26-03-03 - [oracle] 索引低效，导致read by other session等待事件
- 【SQL】SQL表连接方法方式介绍（Oracle/Postgresql）
【SQL】SQL表连接方法方式介绍（Oracle/Postgresql）
26-03-03 - Oracle 19c数据库体系结构-2
Oracle 19c数据库体系结构-2
26-03-03 - 数据库无法注册至监听服务解决办法
数据库无法注册至监听服务解决办法
26-03-03 - Oracle数据库启动过程及状态详解
Oracle数据库启动过程及状态详解
26-03-03 - 数据库常用的事务隔离级别都有哪些？都是什么原理？
数据库常用的事务隔离级别都有哪些？都是什么原理？
26-03-03 - 检查联机后的43 号文件信息：alter database datafile 43 online;
- DG Broker学习5（管理数据保护模式）
DG Broker学习5（管理数据保护模式）
26-03-03
