等待事件：acknowledge over PGA limit与ORA-04036
等待事件描述
- “acknowledge over PGA limit” 是oracle12.1开始与PGA_AGGREGATE_LIMIT参数一同引入的一个等待事件，此等待事件会将一个请求PGA的进程在总的PGA占用内存接近PGA_AGGREGATE_LIMIT值时，强制进入等待状态，以期其他进程可以释放PGA并且避免ORA-04036报错出现。
参数描述
- PGA_AGGREGATE_LIMIT 定义了一个实例上可供使用的所有PGA内存。其默认值，遵循一下规则，大于等于2GB,是PGA_AGGREGATE_LIMIT 的2倍，3MB* 最大进程数（12c），当其数值大于0.9*物理内存数-SGA大小 时，可以被设置为小于PGA_AGGREGATE_LIMIT 的2倍。
SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 2G pga_aggregate_target big integer 415M复制
参数调整方法
- 关闭该参数限制
#设置为0 ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SID='*' SCOPE=BOTH; SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 0 pga_aggregate_target big integer 1G复制
- 调整参数
#1. 调整PGA_AGGREGATE_TARGET = 2G 使 PGA_AGGREGATE_LIMIT 变为4G ALTER SYSTEM SET PGA_AGGREGATE_TARGET =2G SID='*' SCOPE=BOTH; ######## SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET =2G SID='*' SCOPE=BOTH; System altered. SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 4G pga_aggregate_target big integer 2G #2. 调整PROCESSES = 3000 使PGA_AGGREGATE_LIMIT 变为15000M #需要注意的是processes为静态参数，谨慎调整 ALTER SYSTEM SET processes =3000 SID='*' SCOPE=spfile; ###此处测试为19c,3000*5M=15000M SQL> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 2 global_txn_processes integer 1 job_queue_processes integer 80 log_archive_max_processes integer 4 processes integer 3000 SQL> SQL> SQL> SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 15000M pga_aggregate_target big integer 415M ### #3. 调整隐含参数"_pga_limit_target_perc"=400 当 PGA_AGGREGATE_TARGET=2G 时，PGA_AGGREGATE_LIMIT 变为8G #查看隐含参数 #sql col KSPPINM for a30 col KSPPSTVL for a40 col KSPPDESC for a60 SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND TRANSLATE (ksppinm, '_', '#') LIKE '_pga_limit_target_perc'; KSPPINM KSPPSTVL KSPPDESC ------------------------------ ---------------------------------------- ------------------------------------------------------------ _pga_limit_target_perc 200 default percent of pga_aggregate_target for pga_aggregate_li mit ALTER SYSTEM SET "_pga_limit_target_perc"=400 SID='*' SCOPE=spfile; ####通过隐含参数加大时，测试可以正常看到pga_aggregate_limit 变化为4G SQL> ALTER SYSTEM SET "_pga_limit_target_perc"=400 SID='*' SCOPE=both; System altered. SQL> SQL> SQL> SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 2G pga_aggregate_target big integer 415M SQL> SQL> SQL> SQL> alter system set pga_aggregate_target=1G SID='*' SCOPE=both; System altered. SQL> SQL> SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 4G pga_aggregate_target big integer 1G #进一步对调整隐含参数的方法进行验证 #将其回调时，发现 pga_aggregate_limit 并未实时修改，重启后发现已调整 SQL> ALTER SYSTEM SET "_pga_limit_target_perc"=200 SID='*' SCOPE=both; System altered. SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 4G pga_aggregate_target big integer 1G SQL> SQL> SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1308619176 bytes Fixed Size 9134504 bytes Variable Size 855638016 bytes Database Buffers 436207616 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 2G pga_aggregate_target big integer 1G #将其回调时，发现 pga_aggregate_limit 并未实施修改，重启后发现已调整 #设置_pga_limit_target_perc 为400 SQL> ALTER SYSTEM SET "_pga_limit_target_perc"=400 SID='*' SCOPE=both; System altered. #检查参数，发现未生效 SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 2G pga_aggregate_target big integer 1G #设置pga_aggregate_target 为1G 本身也是1G,相当于未修改 SQL> SQL> alter system set pga_aggregate_target=1G SID='*' SCOPE=both; System altered. SQL> #查看后发现 参数已经生效！ SQL> SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 4G pga_aggregate_target big integer 1G SQL> SQL> SQL> #回调验证 SQL> ALTER SYSTEM SET "_pga_limit_target_perc"=200 SID='*' SCOPE=both; System altered. #未生效 SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 4G pga_aggregate_target big integer 1G #重设pga_aggregate_target 参数 SQL> alter system set pga_aggregate_target=1G SID='*' SCOPE=both; System altered. #参数生效 SQL> show parameter PGA_AGGREGATE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 2G pga_aggregate_target big integer 1G SQL>复制
- 参数调整总结
- 方法1可实时生效，并且调整时不用重启数据库
- 方法2影响较大，由于调整processes还需考虑机器配置与业务结构，并且需重启生效，所以生产环境慎用。
- 方法3也可调整大小，但需要配合方法1中参数配合使用，特殊情况可能需要根据实际情况选用
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
