ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT

有幸遇到这个错误了, 12c的PGA_AGGREGATE_LIMIT 限制PGA功能还是有用的:

Exception in thread “main” java.sql.SQLException: ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)

 

oracle@localhost:/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace$ oerr ora 4036
04036, 00000, “PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT”
// *Cause: Private memory across the instance exceeded the limit specified
// in the PGA_AGGREGATE_LIMIT initialization parameter. The largest
// sessions using Program Global Area (PGA) memory were interrupted
// to get under the limit.
// *Action: Increase the PGA_AGGREGATE_LIMIT initialization parameter or reduce
// memory usage.

 

 
pga_aggregate_limit
limit of aggregate PGA memory consumed by the instance
 


Posted

in

by

Tags:

Comments

One response to “ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT”

  1. Maclean Liu Avatar

    If the PGA_AGGREGATE_LIMIT value is exceeded, Oracle Database aborts or terminates the sessions or processes that are consuming the most PGA memory.
    Abortion and termination of sessions and processes are based on the following conditions:
    ■Sessions and processes consuming the most PGA memory are aborted.
    ■If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then sessions and processes consuming the most PGA memory are terminated.
    ■Parallel queries are treated as a single unit.
    ■User sessions in consumer groups are treated based on the percentage of PGA memory specified for the consumer group in the resource plan.
    ■SYS and other critical background processes are not subject to terminations.
    The V$PGASTAT view contains statistics about the number of sessions that are aborted or terminated due to excessive PGA memory usage since the database was started.

    1* select name,value,unit from v$pgastat
    SQL> /

    NAME VALUE UNIT
    —————————————————————- ———- ————
    aggregate PGA target parameter 218103808 bytes
    aggregate PGA auto target 77856768 bytes
    global memory bound 43620352 bytes
    total PGA inuse 131596288 bytes
    total PGA allocated 163753984 bytes
    maximum PGA allocated 1423985664 bytes
    total freeable PGA memory 20447232 bytes
    process count 39
    max processes count 43
    PGA memory freed back to OS 2360541184 bytes
    total PGA used for auto workareas 0 bytes
    maximum PGA used for auto workareas 2054144 bytes
    total PGA used for manual workareas 0 bytes
    maximum PGA used for manual workareas 0 bytes
    over allocation count 163
    bytes processed 414296064 bytes
    extra bytes read/written 0 bytes
    cache hit percentage 100 percent
    recompute count (total) 7889

    19 rows selected.

Leave a Reply

Your email address will not be published. Required fields are marked *