Home » RDBMS Server » Server Administration » PGA memory exceeds PGA_AGGREGATE_LIMIT (oracle 12c Windows 2012)
PGA memory exceeds PGA_AGGREGATE_LIMIT [message #666584] Wed, 15 November 2017 08:30 Go to next message
muthukrish104
Messages: 82
Registered: November 2017
Member
Hi All,

We are facing this problem in one of our Database.

Quote:
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
This error occurs while executing the query of:

DECLARE
   TYPE t_bulk_collect_test_tab IS TABLE OF <table_name>%ROWTYPE;

   l_tab t_bulk_collect_test_tab := t_bulk_collect_test_tab ();
BEGIN
   SELECT *
   BULK COLLECT INTO l_tab
     FROM <table_name>
    WHERE nstm_expr_dt >= v_dt_fr
      AND nstm_trd_dt <= v_dt_fr;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('BULK FO_NSE_TMTRADES ' || SQLERRM);
      RETURN;
END;

Out Memory Setting is:
---------------------

memory_max_target big integer 50G
memory_target big integer 45G
pga_aggregate_target big integer 0
pga_aggregate_limit big integer 0
sga_target big integer 0
sga_max_size big integer 29960M

The characteristics of that table is below:
-------------------------------------------

Size --- 38GB
Extents ---- 791
No of rows --- 157654675

The table structure is here:
---------------------------

CREATE TABLE <name>
(
  SNMT_TRD_NO          NUMBER(16)               NOT NULL,
  SNMT_TRD_STAT        NUMBER(2),
  SNMT_ISNT_TYP        VARCHAR2(6 BYTE)         NOT NULL,
  SNMT_SYMBOL          VARCHAR2(10 BYTE)        NOT NULL,
  SNMT_STRK_PRICE      NUMBER(12,2)             DEFAULT 0,
  SNMT_OPTN_TYP        VARCHAR2(2 BYTE),
  SNMT_SEC_NAME        VARCHAR2(25 BYTE),
  SNMT_B_TYP           VARCHAR2(2 BYTE),
  SNMT_B_TYP_NAME      VARCHAR2(3 BYTE),
  SNMT_MKT_TYP         VARCHAR2(1 BYTE),
  SNMT_USR_ID          NUMBER(5),
  SNMT_BATCH_NO        VARCHAR2(2 BYTE),
  SNMT_BUY_SELL        NUMBER(1)                NOT NULL,
  SNMT_TRD_QTY         NUMBER(10),
  SNMT_TRD_RT          NUMBER(12,2)             DEFAULT 0,
  SNMT_PROP_CLNT       NUMBER(1),
  SNMT_CLNT_CD         VARCHAR2(12 BYTE)        NOT NULL,
  SNMT_MT_CD           VARCHAR2(12 BYTE)        NOT NULL,
  SNMT_OPEN_CLOSE      VARCHAR2(5 BYTE),
  SNMT_COV_UNC_IND     VARCHAR2(8 BYTE),
  SNMT_TRD_DTMT        VARCHAR2(20 BYTE),
  SNMT_MOD_DTMT        VARCHAR2(20 BYTE),
  SNMT_ORDR_NO         NUMBER(16)               NOT NULL,
  SNMT_OPP_BROK_ID     VARCHAR2(5 BYTE),
  SNMT_USER_ID         VARCHAR2(10 BYTE),
  SNMT_LDTI            VARCHAR2(20 BYTE),
  SNMT_USED            VARCHAR2(1 BYTE),
  SNMT_TYPE            VARCHAR2(1 BYTE),
  SNMT_REG_CONTRA      VARCHAR2(1 BYTE),
  SNMT_CONTRA_TYP      VARCHAR2(1 BYTE),
  SNMT_TRD_DT          DATE                     NOT NULL,
  SNMT_EXPR_DT         DATE,
  SNMT_PROD_SYS_CD     NUMBER(10),
  SNMT_MIN_LOT         NUMBER(10),
  SNMT_TRD_TYPE        CHAR(1 BYTE),
  SNMT_BROK_CD         VARCHAR2(20 BYTE),
  SNMT_ACC_CD          VARCHAR2(20 BYTE),
  SNMT_TER_CD          VARCHAR2(20 BYTE),
  SNMT_ORG_STRK_PRICE  NUMBER(18,2),
  SNMT_ORG_TRD_QTY     NUMBER(10),
  SNMT_ORG_TRD_RT      NUMBER(18,2),
  SNMT_ORG_EXPR_DT     DATE,
  SNMT_ORG_MIN_LOT     NUMBER(10),
  SNMT_TRD_NO_ORG      NUMBER(20)
)
TABLESPACE <name>
PCTUSED    0
PCTFREE    10
INITRASN   1
MAXTRASN   255
STORAGE    (
            INITIAL          2560K
            NEXT             512K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


Thanks and regards
Muthu Krish
Re: PGA memory exceeds PGA_AGGREGATE_LIMIT [message #666585 is a reply to message #666584] Wed, 15 November 2017 08:44 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you actually need to put all that data in the array?
What are you doing with the array once it's populated?
Re: PGA memory exceeds PGA_AGGREGATE_LIMIT [message #666587 is a reply to message #666585] Wed, 15 November 2017 08:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And how many rows are you putting in the array in one go? Judging by the code it's not everything and it's the array that takes up pga, not the table. So how big the table is is only relevant to working out the array size.
Re: PGA memory exceeds PGA_AGGREGATE_LIMIT [message #666588 is a reply to message #666584] Wed, 15 November 2017 08:54 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need to disable automatic memory management and set sga_target and pga_aggregate_target explicitly. Then you can set pga_aggregate_limit=0 and your problem is solved.

However, your DBA and SA will hate you: PGA will balloon out until your server is swapping itself to death. Better to use less memory intensive approach, such as a global temporary table.
Re: PGA memory exceeds PGA_AGGREGATE_LIMIT [message #666589 is a reply to message #666584] Wed, 15 November 2017 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
explain why you are loading table data into PGA; which consumes disk, CPU, & RAM resources.
What does this action accomplish beside wasting system resources?
Re: PGA memory exceeds PGA_AGGREGATE_LIMIT [message #666591 is a reply to message #666589] Wed, 15 November 2017 09:29 Go to previous messageGo to next message
muthukrish104
Messages: 82
Registered: November 2017
Member
Hi All,

Thanks for the replies, I will check out this.

And I am not the developer, am the DBA.

Regards and Thanks
M.Krish
Re: PGA memory exceeds PGA_AGGREGATE_LIMIT [message #666594 is a reply to message #666591] Wed, 15 November 2017 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
muthukrish104 wrote on Wed, 15 November 2017 07:29
Hi All,

Thanks for the replies, I will check out this.

And I am not the developer, am the DBA.

Regards and Thanks
M.Krish
what business problem are you trying to solve?
Re: PGA memory exceeds PGA_AGGREGATE_LIMIT [message #666595 is a reply to message #666591] Wed, 15 November 2017 10:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
muthukrish104 wrote on Wed, 15 November 2017 15:29
Hi All,

Thanks for the replies, I will check out this.

And I am not the developer, am the DBA.

Regards and Thanks
M.Krish
Ah! In that case, go straight to your developer and tell him to stop writing code that destroys the database. This is yet another example of why developers should always submit their code to the DBA for checking. Developers hate code reviews, because they know that their DBA will tell them it is rubbish Sad

(guess whether I work as dev or dba...)
Re: PGA memory exceeds PGA_AGGREGATE_LIMIT [message #666601 is a reply to message #666595] Thu, 16 November 2017 03:19 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've encountered DBA's who wouldn't spot stuff like that.
Re: PGA memory exceeds PGA_AGGREGATE_LIMIT [message #666605 is a reply to message #666601] Thu, 16 November 2017 04:38 Go to previous message
muthukrish104
Messages: 82
Registered: November 2017
Member
Hi,

Thanks all for the input,

Now we have resolved it by removing that bulk method and used Global Temp table to insert directly.

Thanks and regards
M.Krish
Previous Topic: LRM-00101: unknown parameter name 'target'
Next Topic: PDB upgrade and exclusion lists
Goto Forum:
  


Current Time: Thu Mar 28 04:26:39 CDT 2024