oracle ORA-04036: 实例使用的 PGA 内存超过 PGA_AGGREGATE_LIMIT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42855051/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
提问by pahariayogi
I came across ORA-04036 (on 12c) while one of my team members was creating an oracle text index.
当我的一名团队成员正在创建 Oracle 文本索引时，我遇到了 ORA-04036（在 12c 上）。
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
回答by pahariayogi
in my case, It failed due to a very high parallel degree 48 (requiring too many session and increased PGA) while creating an index (or any other parallel operation). We should try to reduce parallelism to avoid this. I tried with DOP 8 script ran successfully.
在我的情况下，它由于在创建索引（或任何其他并行操作）时的并行度非常高 48（需要太多会话和增加 PGA）而失败。我们应该尽量减少并行度以避免这种情况。我试过用 DOP 8 脚本成功运行。
In Oracle Database 12c, a new parameter called PGA_AGGREGATE_LIMIT sets a hard limit on the amount of PGA taken up by an instance. When the overall PGA occupied by all the sessions in that instance exceed the limit, Oracle kills the session holding the most untunable PGA memory, releasing all PGA memory held by that session. That killed session gets this message.
Note the new Oracle error ORA-4036. This is an extremely useful feature to control runaway PGA consumption by sessions issuing untuned queries. The parameter pga_aggregate_limit, if not explicitly defined, defaults to the greater of these three numbers: 2 GB 3 MB times the value of the processes parameter 2 times the value of the parameter pga_aggregate_target But this limit does not exceed the 1.2 times the total physical memory after taking away the total SGA size. Don't like this new feature and want to go back to the previous (pre-12c) behavior? Sure; just set the value of pga_aggregate_limit to 0 and the parameter will not have any impact.
在 Oracle Database 12c 中，一个名为 PGA_AGGREGATE_LIMIT 的新参数对实例占用的 PGA 数量设置了硬限制。当该实例中所有会话占用的总 PGA 超过限制时，Oracle 会终止持有最不可调 PGA 内存的会话，释放该会话持有的所有 PGA 内存。那个被杀死的会话会收到这条消息。
请注意新的 Oracle 错误 ORA-4036。这是一个非常有用的功能，可以通过会话发出未调整的查询来控制失控的 PGA 消耗。参数pga_aggregate_limit，如果没有明确定义，默认为这三个数字中的较大者： 2 GB 3 MB 乘以进程参数的值 2 乘以参数 pga_aggregate_target 的值 但这个限制不超过总物理内存的 1.2 倍去掉总 SGA 大小后。不喜欢这个新功能并想回到以前（12c 之前）的行为？当然; 只需将 pga_aggregate_limit 的值设置为 0，该参数不会产生任何影响。
Posting answer in the interest of community readers as i could not find it on SO yet. Pls see thisfor further reading...
为了社区读者的兴趣发布答案，因为我还没有在 SO 上找到它。请参阅此内容以进一步阅读...
回答by John
I had a similar issue and resolved it by doing the following.
我遇到了类似的问题，并通过执行以下操作解决了它。
See what the current pga_aggregate_limit is (in case you want to revert your changes) using this:
使用以下命令查看当前的 pga_aggregate_limit 是什么（以防您想恢复更改）：
show parameter pga_aggregate_limit;
Set the pga_aggregate_limit to 0 using this:
使用以下命令将 pga_aggregate_limit 设置为 0：
alter system set pga_aggregate_limit = 0;
More information is available here:
更多信息请点击这里：
http://www.dba-oracle.com/t_pga_aggregate_limit.htm
http://www.dba-oracle.com/t_pga_aggregate_limit.htm
https://docs.oracle.com/database/121/REFRN/GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3.htm#REFRN10328
https://docs.oracle.com/database/121/REFRN/GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3.htm#REFRN10328
