oracle - ORA-04036 : PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Question

当我的一个团队成员正在创建一个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

Answer 1

在我的情况下，它由于创建索引(或任何其他并行操作)时的并行度很高(需要太多 session 并增加了PGA)而失败(48)。
我们应该尝试减少并行性以避免这种情况。我尝试使用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.

为社区读者的利益发布答案，因为我目前还无法找到答案。请参阅 this进行进一步阅读...