Skip to Main Content
  • Questions
  • Can Oracle allocate more PGA than PGA_AGGREGATE_TARGET parameter ?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Pierre.

Asked: December 30, 2006 - 2:11 pm UTC

Last updated: October 26, 2017 - 12:47 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Can Oracle allocate more PGA memory than PGA_AGGREGATE_TARGET parameter ?
If yes, is there a rule for that ?

Thanks,

Pierre

and Tom said...

absolutely - if you want to see examples of that - my latest book "Expert Oracle Database Architecture" shows some.

There are two types of pga memory - tunable (stuff Oracle can control the allocation size of - includes things like sort and hash areas) and untunable (stuff Oracle cannot control - like memory for plsql tables and the like).


Suppose you have 100 sessions connected. And further suppose you set the pga aggregate target to 100m.

Now, supposed each of those 100 sessions (using dedicated server is my assumption here) run some code that fills up a plsql table with lots of stuff, so the plsql table consumes 5mb of ram. You now have at least 500mb of PGA allocated.


Suppose you have 100 sessions connected, further suppose the pga is limited to 10mb (ten).

Suppose all 100 sessions open 10 cursors each that sort (so you have 1000 open sql statements). even if we used a tiny sort area of 64k - that would be 64000kb of memory - 62.5mb. We would exceed your setting.


Rating

  (28 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What's occupying the PGA?

Roger Andersson, January 02, 2007 - 1:10 pm UTC

I know that we can check v$process and/or v$sesstat for information about how much pga a process has allocated.

Is there any way we can check what's occupying the pga, for example how much memory is allocated to a process for sort, hash, plsql tables etc?

We have a system that allocates huge amounts of memory, 5-6 Gb, for one process according to v$process, and with a couple of such processes the physical memory on the server is exhausted and performance terrible. It would be great if it's possible to check what's occupying the pga, so we can help the developers change their code. A couple of issues, like bulk collect and for all inserts with millions of rows, has been changed already but there are still issues with huge allocation of memory in the pga.

/Roger
Tom Kyte
January 04, 2007 - 10:57 am UTC

v$sort*
v$temp*

will show you who is allocating large workareas - but in general, you would use pga_aggregate_target to have the system "auto tune" the work area sizes that it has control over - v$pgastat would be useful as well.

What about a single process?

Gareth Stephens, January 04, 2007 - 11:55 am UTC

Hi Tom,

Whilst I understand what you've said about the PGA management for a group of processes it was my understanding that the usage of the PGA would (typically and not for sure) be limited to around 5% of the total PGA Aggregate target. At this stage you would start to have a single pass or multi pass executions. I've arrived at this understanding from documents in the Oracle manual, including (but not limited to) this one:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i49320

Recently however on a system I was working on that had a significant PGA aggregate target (2.5Gb) a single process was able to consume far more than this, to the point where the system was paging out to disk. The amount of RAM the single process would attempt to consume was in excess of 9Gb.

A service request was logged (SR number available if you want it) as I did not believe, from my understanding, that this was appropriate, i.e. I believed that the query should have resorted to either a single- or multi-pass execution. The only 'odd' thing about the query was that it made use of CASE statements with a SUM on certain columns, meaning that the CASE statement also appeared in the GROUP BY part of the statement. As a result of this the Oracle support analyst basically said yep that's right, that's how it should be.

In saying this they referred to note 223730.1 which states that the over allocation count might be increased if there was not enough PGA allocatable to the query. This ties in with what you have said in response to the original post and my understanding. However it seems odd to me that a single query in a single session could consume as much as 9Gb of memory. How could you prevent this as it badly slowed down the system (unsurprisingly as it was paging like mad).

I would be interested to hear your views on this. I also asked, although did not have time to try it out, what might happen if we resorted to manual management of the parameters - would the one process have encountered an ORA-???? error?
Tom Kyte
January 05, 2007 - 9:12 am UTC

the aggregate target is a TARGET, it is very possible to exceed it. If you have access to my book Expert Oracle Database Architecture, I give two examples

a) plsql variable use - fill up big arrays in memory - we cannot stop you. You can easily exceed the pga target

b) open up lots of cursors that each need to sort/hash. They each need some NOMINAL allocation of memory, the sum of that could easily exceed your limit.

Very interesting..

Satya, January 04, 2007 - 3:33 pm UTC

That's interesting. Here, I'm wrestling with trying to maximize/optimally use PGA_AGGREGATE_TARGET for a very large system (24 CPU, 64 GB RAM HPUX box running 9.2.0.6) and have hit a wall.

I've been looking into various docs/forum messages for info on increasing my PGA cache_hit_percentage and reducing SQL workarea one-pass executions as much as possible (we have 0 multi-pass execs so that's good). We've got a massive pga_aggregate_target (12 GB and can go much higher) but can't get Oracle to use it. Our maximum PGA allocated has never exceeded 2 GB but we have thousands of one-pass executions (all of which start with 64mb <= PGA < 128mb).

I'm aware of the limits imposed by Oracle on the size of a single workarea - the memory allocated to a SQL operator for serial and parallel operations and have played around with "_pga_max_size", "_smm_max_size" and "_smm_px_max_size" with marginal results.

As the memory allocated to a single SQL operator is limited to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations and for parallel operations to min(30% PGA_AGGREGATE_TARGET/DOP, 5% PGA_AGGREGATE_TARGET, 100MB), the default 5% is not really true at all. It's always going to be 100 MB for any pga_aggregate_target over 2 GB. I believe that this 100 MB limit is in fact set by _pga_max_size/2 where _pga_max_size = 200MB by default.


Tom:

Not sure if I'm missing something. Any insight you can provide would be very much appreciated.
Thank you. Great site.

Happy new year.

- Satya
Tom Kyte
January 05, 2007 - 9:19 am UTC

do you have a batch oriented system here, or do you have ad-hoc users that vary in number over time.

automatic pga memory management is designed to be "fair" and is best suited for the second case there.

If you have large batch jobs, it could well be that manual memory management is what you desire.

do not use the "_" parameters - in 10g, the entire algorithm changed to be a little more "batch friendly" perhaps and by setting these now, you'll defeat the changes later.

A single query in a single session though...

Gareth Stephens, January 05, 2007 - 9:29 am UTC

Hi Tom,

Thanks for the follow up however I think you might have missed my point. You say:
a) plsql variable use - fill up big arrays in memory - we cannot stop you. You can easily exceed the pga target

b) open up lots of cursors that each need to sort/hash. They each need some NOMINAL allocation of memory, the sum of that could easily exceed your limit.

a) Yep sure hadn't realised this but understand what you are saying.
b) Absolutely understand this.

But what I was trying to say is that this is simply ONE SQL query in ONE session run from SQL*Plus, i.e. No PL/SQL (tables or code) and no cursors (other than perhaps however many SQL*Plus might open in the background).

I just get the feeling it is "wrong" and that this query should have been either single- or multi-pass executing, however it didn't in this particular instance.
Tom Kyte
January 05, 2007 - 10:47 am UTC

there is tunable memory - used for workareas, sorting and hashing

there are other memory areas that are NOT tunable, they definitely can and will do this.

I just gave two examples, there are others.

Excessive PGA

Jonathan Lewis, January 05, 2007 - 1:37 pm UTC

Gareth, I have seen a case in 10.2 where a single SQL query ran up a huge memory demand and ultimately crashed out with Oracle error ORA-04030 (out of memory).

This was actually a bug, and was not supposed to happen. YO may have come across something similar. This was on a client site, and we raised an SR which eventually turned into bug 5516917 (fixed in 10.2.0.4) with an association to bugs 5634601 and 5683741 (backport requests).

In our case the bug was a memory leak that appeared if you had a nested loop join driven through an indexed access path, using a tablescan on the inner table.

I have seen something similar where a filter subquery ((not) exists/in) used a hash group by - big memory leak, combined with big CPU usage.

Basically, though, what your seeing probably should not happen on a single pure SQL query. But do check that you don't have a pl/sql call hidden inside the query somewhere.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com


Citrus, January 07, 2007 - 7:11 am UTC

Why do we have to set the PGA_AGGREGATE_TARGET when it exceeds the given value? Let it manage itself or let me limit it as in case of Bufer Cache, Shared Pool etc..
Tom Kyte
January 07, 2007 - 8:19 pm UTC

that question doesn't make sense.

"Why do we have to set the PGA_AGGREGATE_TARGET when it exceeds the given value? "

what given value?

Re: Excessive PGA - Jonathon Lewis

Gareth Stephens, January 08, 2007 - 5:05 am UTC

Hi Jonathon,

Thanks for your follow-up to my question to Tom. Unfortunately I am no longer on the site that was seeing this (possible) problem so no longer have access to the full query or a database where I can reproduce the issue. Checking as much of the query I can see (saved HTML page from metalink) there are no PL/SQL function calls in there - which backs up what I had recalled from memory.

I also understand where Tom is coming from, I know from experience that many 'bugs' that people find aren't bugs with Oracle but poorly written code. What I still find hard to believe is that a query run from SQL*Plus containing only SUM and CASE statements could consume 9Gb!

Out of interest in the cases that you saw how did you arrive at the conclusion that the issue you were seeing was indeed a bug in Oracle and not the application logic? Generally speaking I am sufficiently good at 'bug spotting' that I can provide a simple test case to illustrate the bug and more often than not it turns out I was correct. In this instance I am still suspicious of a bug, but it would be nice to have been able to categorically prove or disprove the theory.

Regards and thanks for the follow-ups from yourself and Tom,

Gareth.
Tom Kyte
January 08, 2007 - 12:32 pm UTC

I saw Jonathan's example - he "proved" it by providing the classic test case - a single standalone query that when run in sqlplus exhibited the behaviour in a reproducible fashion.

pga_aggregate_target - not perfect

Christo Kutrovsky, February 07, 2007 - 2:18 pm UTC

Note as tom said, pga_aggregate_target is only a target and not a concrete limit.

The algorithm that controls this "target" is also not perfect. It is possible via simple test cases to go over the target with Oracle happily alocating more and more memory over the "target", without counting over-allocation.

Note also that there's a minimum amount of memory needed for sort/hash operations. The above behaviour is not related with hitting this minimum in a large number of sessions.

I've followed this behaviour since 9i all the way to 10.2.0.2. Haven't tried it in 10.2.0.3.


How to go about testing this

Tom, March 08, 2007 - 2:08 pm UTC

Tom,

I have a database which uses a connection pool to connect to it so the connections it uses are rarely closed. The PGA_AGGREGATE_TARGET is set to 500m (which is ample for the workload) but over time it grows and grows until it exceeds this (currently 1.2Gb taken by 5 connections) and never seems to shrink even when no-one is accessing the database.

The memory appears to be taken up solely by the UGA memory for the sessions connected via the connection pool.

Before I call this a bug and raise an SR, I would like to check it isn't the application which is leaking memory. Are there any views I can check to view the UGA breakdown i.e. 20M used by temporary LOBs, 20M used by open cursors, etc?

So, impossible to limit?

Albert Nelson A, March 29, 2007 - 7:20 am UTC

Hi Tom,

As you have mentioned that pga_aggregate_target is only a target, does that mean that there is no way to limit the pga memory used by sessions?

How to avoid sessions consuming all the available memory in the database server? Particularly if application server connection pool is used for getting database connections wherein the connections remain open?


Regards,

Albert Nelson A.
Tom Kyte
March 30, 2007 - 12:50 pm UTC

not really

we allocate what we have to - until it runs out. the pga aggregate target allows us to do that more effectively by backing off the amount of memory given for workareas as the usage goes up (as opposed to sort/hash area size)

You would "avoid" running out of memory by not doing things in your sessions that use lots of "untunable" memory (eg: filling a huge plsql table with lots of big things).

Aman...., September 06, 2008 - 9:54 am UTC

Sir,
Yesterday I ran into the same discussion with some guys that PGA target does increase after the allocation of the work areas consume more space than allocated within it. You have confirmed the same just now. My question is that where do we see over allocation that goes beyond the set value of PGA_target?
And in PT book of OU ,it is also mentioned that memory of PGA is returned back to the OS.So does this statement mean that after teh allocation goes beyond the set value and the work is complete, oracle would return it back to the OS?Is this understanding of mine is correct?
Thanks and regards
Aman....
Tom Kyte
September 08, 2008 - 3:46 pm UTC

"PT book of OU" - I can guess what OU is pretty well, not familar personally with PT.

review:
http://asktom.oracle.com/pls/ask/search?p_string=tunable+untunable+pga+memory

the pga allocated for temporary workareas (tunable pga memory) is allocated in a fashion that allows us to return it to the OS - the untunable memory probably is not - it is part of the heap of the process - it is not used and released like a workarea, it is managed in a heap.

Aman...., September 09, 2008 - 5:03 am UTC

Thanks sir and I am sorry for using abbreviation for Performance Tuning and Oracle Univ.I use it all the time so I thought its ok.Aplogies for it.
Sir you mentioned,tunable portion's memory comes back to the OS.The non-tunable portion doesn't come.So whichis the tunable portion?
I have read the _pga_max_size and _smm thread sir.I shall be doing some tests over it.What does these parameters denote?I know you don't recommend to play with them but its for information only I am asking.
Thans and regards
Tom Kyte
September 09, 2008 - 8:01 am UTC

that is the UNIVERSAL problem with acronyms/abbreviations. That is why we should avoid them.

PT - physical therapy is actually the first thing that popped into my head (honestly). I've never called the performance tuning guide the PT guide... Second in my head would be a PT boat...

Tunable pga memory is the stuff we allocate for sort area, hash area, bitmap merge areas - workareas.

Untunable is the stuff you allocate - memory for your plsql variables and the like.

Limit pga per session

A reader, February 17, 2009 - 4:43 am UTC

Hi Tom,

Can we limit the pga allocated per session if workaarea_policy is set to AUTO

Thanks in advance,
Tom Kyte
February 17, 2009 - 8:53 am UTC

only if you use a profile and limit private SGA memory and use shared server - so no, not really - because PGA is always process based and SGA is not in the process.


You can use ulimit on unix to limit a process heap size, but expect ora-4030's to happen.

the pga aggregate target is designed to let applications have as much memory as is SAFE (given the concurrent workload) but not to fail them if they exceed that

Limit max pga per session.

A reader, February 18, 2009 - 2:30 am UTC

Thanks Tom for the reply. In some situations where one or two memory intensive (or spinning) sessions consuming the entire OS memory, we would prefer to have an alternative to set a maximum limit for PGA per session. Can _pga_max_size parameter help us here?

Thanks in advance,
Tom Kyte
February 18, 2009 - 2:17 pm UTC

but - like I said - it will be somewhat self controlling - the two "spinning" (whatever that means) sessions will get "some of the pga allocated to the instance" - but not all. If more and more and more sessions come into play - they will get smaller and smaller allocations (after a WHILE, you have to be nearing capacity before we start backing off), and if the 'spinners' give up their workareas (they finish a sort), they too would get smaller allocations until memory used by other sessions is release.


That is, when system is not loaded up, you get big - but by far not so big as to consume everything - allocations. When the system gets stressed, your requests for memory will result in smaller allocations (to avoid swapping, to be 'fair'). When the stress goes away, the allocations go up again.


DO NOT set any "_" parameters, that one in particular. It is not a hard limit (none of the pga settings are hard limits, they are 'requests' we try to honor). Setting that would NOT bound someone to a limit, it would just make you different from the rest of the world and would lead to strange, inexplicable behavior.


What you are worried about was something that could happen using manual memory allocation - but automatic memory management works to avoid, don't undo what has been done, it does the *right thing* already.

ORA-04030: out of process memory

Ken, June 08, 2009 - 5:55 pm UTC

Tom:
I received this error from etl job maintained by etl app team to resolve.

ORA-04030: out of process memory when trying to allocate 13808 bytes (kxs-heap-p,rworalo:rwordops).

I did look at pl/sql code, they do not use any pl/sql tables or arrarys. But pl/sql code does row by row processing using inner cursor, outer cursor processing via open/close cursor. ETL team fired the same script from 10 different sqlplus sessions. They are consuming almost 100 GB of server configured memory. 

Questions:

1. Can too many pl/sql variables declaration occupy more memory. How to monitor the pl/sql variable memory usage alone ?

2. Can row-by-by inner/outer cursor processing with open/close cursor occupies more memory until they finally close the cursor ? How to view the cursor memory usage for each session ?

It appears to me that code needs to be rewritten to change to cursor row-by-row to bulk collect/for loop (instead of open cursor) so as to release the cursor.

How to justify this to app team to rewrite their code.


====Database server has 100 GB of physical memory and it is on solaris 10, T5240 box and oracle 10.2.0.4.
PGA_AGGREGATE_TARGET is set to 4GB.

When i looked at the v$process.PGA_USED_MEM, it seems that their jobs are occupying 100 GB of memory. 

SQL> select sum(PGA_USED_MEM)/1024/1024 , sum(PGA_ALLOC_MEM)/1024/1024, sum(PGA_FREEABLE_MEM)/1024/1024, sum(PGA_MAX_MEM)/10
24/1024 from v$process
  2  /

SUM(PGA_USED_MEM)/1024/1024 SUM(PGA_ALLOC_MEM)/1024/1024 SUM(PGA_FREEABLE_MEM)/1024/1024 SUM(PGA_MAX_MEM)/1024/1024
--------------------------- ---------------------------- ------------------------------- --------------------------
                  101514.09                   102379.981                        452.6875                 107199.106



SQL> select
  2  PGA_USED_MEM/1024/1024 PGA_USED_MEM_SIZE,
  3  PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MEM_SIZE,
  4  PGA_FREEABLE_MEM/1024/1024 PGA_FREEABLE_MEM_SIZE,
  5  PGA_MAX_MEM/1024/1024 PGA_MAX_MEM_SIZE,
  6  TO_CHAR(a.sid,'fm99999') SID,
  7  a.username,
  8  a.program
  9  from v$session a, v$process b
 10  where a.paddr=b.addr
 11  and   a.username='REFRESH'
 12  order by PGA_USED_MEM DESC
 13  /

PGA_USED_MEM_SIZE PGA_ALLOC_MEM_SIZE PGA_FREEABLE_MEM_SIZE PGA_MAX_MEM_SIZE SID    USERNAME   PROGRAM
----------------- ------------------ --------------------- ---------------- ------ ---------- ------------------------------
------------------
       11298.5154         11316.4041                     0       11316.4041 765    REFRESH    sqlplus@ftwldscidb202 (TNS V1-
V3)
       11292.3377         11310.2166                     0       11310.2166 572    REFRESH    sqlplus@ftwldscidb202 (TNS V1-
V3)
       11274.5535         11292.4041                     0       11292.4041 431    REFRESH    sqlplus@ftwldscidb202 (TNS V1-
V3)
       11245.8026         11263.8416                     0       11263.8416 1580   REFRESH    sqlplus@ftwldscidb202 (TNS V1-
V3)
       11202.6677         11220.4041                     0       11220.4041 1152   REFRESH    sqlplus@ftwldscidb202 (TNS V1-
V3)
       11159.9391         11177.8416                     0       11177.8416 396    REFRESH    sqlplus@ftwldscidb202 (TNS V1-
V3)
       11153.8835         11171.5291                     0       11171.5291 1388   REFRESH    sqlplus@ftwldscidb202 (TNS V1-
V3)
       11078.3188         11096.0916                     0       11096.0916 1583   REFRESH    sqlplus@ftwldscidb202 (TNS V1-
V3)
       11036.1824         11053.7791                  .125       11053.9041 1599   REFRESH    sqlplus@ftwldscidb202 (TNS V1-
V3)
....

Tom Kyte
June 08, 2009 - 6:10 pm UTC

cursors should not utilize copious amounts of memory - you do not need to worry about closing them "fast", the reasons to throw out the slow by slow (row by row) code are many - but that isn't one of them.

Jonathan Lewis has some useful queries for you here - to see what is SQL and what is PLSQL allocated memory

http://jonathanlewis.wordpress.com/2009/06/07/pga-leaks/

continuation of previous post -- ORA--4030

Ken, June 08, 2009 - 5:58 pm UTC

PL/SQL script looks like this:

#################################################################Loading reporting table cmd="
set timing on feed on
Whenever sqlerror exit sql.sqlcode;
set serveroutput on
DECLARE
/************************************DEFINE LOCAL VARIABLES****************************************/
FLAG BOOLEAN :=TRUE;
EXIST BOOLEAN :=FALSE;
counter NUMBER := 0;
/************************************************************/
V_GL_EFFECTIVE_DT_1 DATE;
V_GL_EFFECTIVE_DT_2 DATE;
V_GL_EFFECTIVE_DT_3 DATE;
V_DOCUMENT_STATUS_CD VARCHAR2(1);
V_DOCUMENT_TYPE_CD VARCHAR2(3);
V_ORPH_RJCT_IND VARCHAR2(3);
V_RNDRD_UNRD_IND VARCHAR2(1);
V_PREMIUM_LOSS_CD VARCHAR2(1);
V_NM_CONTRACT_NO NUMBER(10) ;
V_ORPHAN_STATUS_CD VARCHAR2(1);
V_INCOMPLT_TREATY_IN VARCHAR2(1);
V_MACH_NM_ORIG_CD VARCHAR2(1) ;
V_MIS013_COUNTRY_CD NUMBER(5) ;
V_KIND_CD VARCHAR2(2) ;
V_HOLD_IN VARCHAR2(1) ;
V_REIN_PROC_CTGRY_CD VARCHAR2(3) ;
V_REIN_DIRECTION_CD VARCHAR2(3) ;
V_REIN_RNDRG_FREQ_CD NUMBER(22) ;
V_REIN_NO VARCHAR2(20);
V_REIN_UW_YR_NO NUMBER(5) ;
V_REIN_EFCTV_DT DATE ;
V_REIN_KEY_CD VARCHAR2(6) ;
V_FAC_CERTIFICATE_NO VARCHAR2(20);
V_XTRNL_REIN_REF_NO VARCHAR2(50);
V_CAPTIVE_IN VARCHAR2(1) ;
V_ASCO_MIS012_CUR_CD NUMBER(22) ;
V_NM_ADJUSTMENT_CD NUMBER(5) ;
V_SOURCE_SYSTEM_ID VARCHAR2(25);
V_MIS780_ASCO_CODE VARCHAR2(2) ;
V_POLICY_XPIRTN_DT DATE ;
V_INSURED_NM VARCHAR2(90);
V_POLICY_NO VARCHAR2(10);
V_POLICY_EFCTV_DT DATE ;
V_MIS780_COMP_CODE NUMBER(22) ;
V_MACH_NM VARCHAR2(2);
V_REIN_NO_LAYER_NO VARCHAR2(2);
V_REIN_NO_SECTION_NO VARCHAR2(2);
V_REIN_NO_FOREIGN_DOM_CD VARCHAR2(1);
V_RELTNSHP_TYP_CD VARCHAR2(3);
V_SOURCE_REIN_NO VARCHAR2(20);
V_SRC_REIN_EFCTV_DT DATE ;
V_SRC_REIN_UW_YR_NO NUMBER(5) ;
V_SRC_REIN_KEY_CD VARCHAR2(6);
V_SRC_REIN_NO_LAYER_NO VARCHAR2(2);
V_SRC_REIN_NO_SECTION_NO VARCHAR2(2);
V_SRC_REIN_NO_FOREIGN_DOM_CD VARCHAR2(1);
V_PUC_DSP_DIV NUMBER(5) ;
V_PUC_DSP_PUC NUMBER(5) ;
V_PUC_DSP_SEC NUMBER(5) ;
V_LOSS_DT DATE ;
V_DEP_DDSP_DEPT NUMBER(5) ;
V_TERM_CONDITION_CD VARCHAR2(3);
V_MATRIX_CD VARCHAR2(3);
V_MIS404_TAX_ST_CD NUMBER(22) ;
V_CREDITED_BRANCH_NO NUMBER(22) ;
V_WORKING_BRANCH_NO NUMBER(22) ;
V_MIS020_CATAS_CD NUMBER(5) ;
V_MIS020_CATAS_YR_NO NUMBER(5) ;
V_CLAIM_BRANCH_NO NUMBER(5) ;
V_CLAIM_CASE_NO NUMBER(10) ;
V_KEY_BRANCH_NO VARCHAR2(3);
V_KEY_CASE_NO VARCHAR2(6);
V_CLA_ID VARCHAR2(22);
V_MIS028_MAJ_CLASS NUMBER(5) ;
V_MIS028_STMT_LINE VARCHAR2(3);
V_TRANSACTION_TYP_CD NUMBER(5) ;
V_TRANS_SUB_TYP_CD NUMBER(5) ;
V_GEN_LEDGER_ACCT_NO NUMBER(10) ;
V_REINSURER_NO VARCHAR2(6);
V_REINSURER_BRNCH_CD VARCHAR2(3);
V_INTERMEDIARY_NO VARCHAR2(6);
V_INTMDRY_BRNCH_CD VARCHAR2(3);
V_PARTICIPANT_TYP_CD VARCHAR2(3);
V_BALSHT_GL_ACCT_NO NUMBER(22) ;
/************************************END OF DEFINING LOCAL VARIABLES*******************************/
CURSOR OUTER_CURSOR IS
SELECT /*+ index(A I_ENDG_RSRVS_MCH_01) */ A.*,ROWID FROM ETLI.ENDG_RSRVS_MCH PARTITION ($1) A WHERE process_ind<>'Y'; --FOR UPDATE OF process_ind; /*****************************************************************************************/
CURSOR INNER_CURSOR IS
SELECT /*+ index(A MNTH_RSRVS_IDX) */ a.*,ROWID FROM ETL.MNTHLY_RSRVS A WHERE
A.GL_EFFECTIVE_DT IN (V_GL_EFFECTIVE_DT_1,V_GL_EFFECTIVE_DT_2,V_GL_EFFECTIVE_DT_3) AND
A.DOCUMENT_STATUS_CD = V_DOCUMENT_STATUS_CD AND
A.DOCUMENT_TYPE_CD = V_DOCUMENT_TYPE_CD AND
A.ORPH_RJCT_IND = V_ORPH_RJCT_IND AND
A.RNDRD_UNRD_IND = V_RNDRD_UNRD_IND AND
A.PREMIUM_LOSS_CD = V_PREMIUM_LOSS_CD AND
A.NM_CONTRACT_NO = V_NM_CONTRACT_NO AND
A.ORPHAN_STATUS_CD = V_ORPHAN_STATUS_CD AND
A.INCOMPLT_TREATY_IN = V_INCOMPLT_TREATY_IN AND
A.MACH_NM_ORIG_CD = V_MACH_NM_ORIG_CD AND
A.MIS013_COUNTRY_CD = V_MIS013_COUNTRY_CD AND
A.KIND_CD = V_KIND_CD AND
A.HOLD_IN = V_HOLD_IN AND
A.REIN_PROC_CTGRY_CD = V_REIN_PROC_CTGRY_CD AND
A.REIN_DIRECTION_CD = V_REIN_DIRECTION_CD AND
A.REIN_RNDRG_FREQ_CD = V_REIN_RNDRG_FREQ_CD AND
A.REIN_NO = V_REIN_NO AND
A.REIN_UW_YR_NO = V_REIN_UW_YR_NO AND
A.REIN_EFCTV_DT = V_REIN_EFCTV_DT AND
A.REIN_KEY_CD = V_REIN_KEY_CD AND
A.FAC_CERTIFICATE_NO = V_FAC_CERTIFICATE_NO AND
A.XTRNL_REIN_REF_NO = V_XTRNL_REIN_REF_NO AND
A.CAPTIVE_IN = V_CAPTIVE_IN AND
A.ASCO_MIS012_CUR_CD = V_ASCO_MIS012_CUR_CD AND
A.NM_ADJUSTMENT_CD = V_NM_ADJUSTMENT_CD AND
A.SOURCE_SYSTEM_ID = V_SOURCE_SYSTEM_ID AND
A.MIS780_ASCO_CODE = V_MIS780_ASCO_CODE AND
A.POLICY_XPIRTN_DT = V_POLICY_XPIRTN_DT AND
A.INSURED_NM = V_INSURED_NM AND
A.POLICY_NO = V_POLICY_NO AND
A.POLICY_EFCTV_DT = V_POLICY_EFCTV_DT AND
A.MIS780_COMP_CODE = V_MIS780_COMP_CODE AND
A.MACH_NM = V_MACH_NM AND
A.REIN_NO_LAYER_NO = V_REIN_NO_LAYER_NO AND
A.REIN_NO_SECTION_NO = V_REIN_NO_SECTION_NO AND
A.REIN_NO_FOREIGN_DOM_CD = V_REIN_NO_FOREIGN_DOM_CD AND
A.RELTNSHP_TYP_CD = V_RELTNSHP_TYP_CD AND
A.SOURCE_REIN_NO = V_SOURCE_REIN_NO AND
A.SRC_REIN_EFCTV_DT = V_SRC_REIN_EFCTV_DT AND
A.SRC_REIN_UW_YR_NO = V_SRC_REIN_UW_YR_NO AND
A.SRC_REIN_KEY_CD = V_SRC_REIN_KEY_CD AND
A.SRC_REIN_NO_LAYER_NO = V_SRC_REIN_NO_LAYER_NO AND
A.SRC_REIN_NO_SECTION_NO = V_SRC_REIN_NO_SECTION_NO AND
A.SRC_REIN_NO_FOREIGN_DOM_CD = V_SRC_REIN_NO_FOREIGN_DOM_CD AND
A.PUC_DSP_DIV = V_PUC_DSP_DIV AND
A.PUC_DSP_PUC = V_PUC_DSP_PUC AND
A.PUC_DSP_SEC = V_PUC_DSP_SEC AND
A.LOSS_DT = V_LOSS_DT AND
A.DEP_DDSP_DEPT = V_DEP_DDSP_DEPT AND
A.TERM_CONDITION_CD = V_TERM_CONDITION_CD AND
A.MATRIX_CD = V_MATRIX_CD AND
A.MIS404_TAX_ST_CD = V_MIS404_TAX_ST_CD AND
A.CREDITED_BRANCH_NO = V_CREDITED_BRANCH_NO AND
A.WORKING_BRANCH_NO = V_WORKING_BRANCH_NO AND
A.MIS020_CATAS_CD = V_MIS020_CATAS_CD AND
A.MIS020_CATAS_YR_NO = V_MIS020_CATAS_YR_NO AND
A.CLAIM_BRANCH_NO = V_CLAIM_BRANCH_NO AND
A.CLAIM_CASE_NO = V_CLAIM_CASE_NO AND
A.KEY_BRANCH_NO = V_KEY_BRANCH_NO AND
A.KEY_CASE_NO = V_KEY_CASE_NO AND
A.CLA_ID = V_CLA_ID AND
A.MIS028_MAJ_CLASS = V_MIS028_MAJ_CLASS AND
A.MIS028_STMT_LINE = V_MIS028_STMT_LINE AND
A.TRANSACTION_TYP_CD = V_TRANSACTION_TYP_CD AND
A.TRANS_SUB_TYP_CD = V_TRANS_SUB_TYP_CD AND
A.GEN_LEDGER_ACCT_NO = V_GEN_LEDGER_ACCT_NO AND
A.REINSURER_NO = V_REINSURER_NO AND
A.REINSURER_BRNCH_CD = V_REINSURER_BRNCH_CD AND
A.INTERMEDIARY_NO = V_INTERMEDIARY_NO AND
A.INTMDRY_BRNCH_CD = V_INTMDRY_BRNCH_CD AND
A.PARTICIPANT_TYP_CD = V_PARTICIPANT_TYP_CD AND
A.BALSHT_GL_ACCT_NO = V_BALSHT_GL_ACCT_NO

ORDER BY
A.GL_EFFECTIVE_DT ;
/**************************************************************************************************/
OUTER_RECORD OUTER_CURSOR%ROWTYPE;
INNER_RECORD INNER_CURSOR%ROWTYPE;
GRET_RECORD INNER_CURSOR%ROWTYPE;
EQU_RECORD INNER_CURSOR%ROWTYPE;
PROP_RECORD INNER_CURSOR%ROWTYPE;
/***********************************END OF CURSOR DECLARALATION***********************************/
BEGIN
OPEN OUTER_CURSOR;
LOOP
FETCH OUTER_CURSOR INTO OUTER_RECORD;
EXIT WHEN OUTER_CURSOR%NOTFOUND;
--DBMS_OUTPUT.PUT_LINE ('inside the outer loop ==>' ); --DBMS_OUTPUT.PUT_LINE ('flag ==>' || FLAG );
V_GL_EFFECTIVE_DT_1 := ADD_MONTHS(OUTER_RECORD.GL_EFFECTIVE_DT,-1);
V_GL_EFFECTIVE_DT_2 := OUTER_RECORD.GL_EFFECTIVE_DT;
V_GL_EFFECTIVE_DT_3 := ADD_MONTHS(OUTER_RECORD.GL_EFFECTIVE_DT,1);
V_DOCUMENT_STATUS_CD := OUTER_RECORD.DOCUMENT_STATUS_CD ;
V_DOCUMENT_TYPE_CD := OUTER_RECORD.DOCUMENT_TYPE_CD ;
V_ORPH_RJCT_IND := OUTER_RECORD.ORPH_RJCT_IND ;
V_RNDRD_UNRD_IND := OUTER_RECORD.RNDRD_UNRD_IND ;
V_PREMIUM_LOSS_CD := OUTER_RECORD.PREMIUM_LOSS_CD ;
V_NM_CONTRACT_NO := OUTER_RECORD.NM_CONTRACT_NO ;
V_ORPHAN_STATUS_CD := OUTER_RECORD.ORPHAN_STATUS_CD ;
V_INCOMPLT_TREATY_IN := OUTER_RECORD.INCOMPLT_TREATY_IN ;
V_MACH_NM_ORIG_CD := OUTER_RECORD.MACH_NM_ORIG_CD ;
V_MIS013_COUNTRY_CD := OUTER_RECORD.MIS013_COUNTRY_CD ;
V_KIND_CD := OUTER_RECORD.KIND_CD ;
V_HOLD_IN := OUTER_RECORD.HOLD_IN ;
V_REIN_PROC_CTGRY_CD := OUTER_RECORD.REIN_PROC_CTGRY_CD ;
V_REIN_DIRECTION_CD := OUTER_RECORD.REIN_DIRECTION_CD ;
V_REIN_RNDRG_FREQ_CD := OUTER_RECORD.REIN_RNDRG_FREQ_CD ;
V_REIN_NO := OUTER_RECORD.REIN_NO ;
V_REIN_UW_YR_NO := OUTER_RECORD.REIN_UW_YR_NO ;
V_REIN_EFCTV_DT := OUTER_RECORD.REIN_EFCTV_DT ;
V_REIN_KEY_CD := OUTER_RECORD.REIN_KEY_CD ;
V_FAC_CERTIFICATE_NO := OUTER_RECORD.FAC_CERTIFICATE_NO ;
V_XTRNL_REIN_REF_NO := OUTER_RECORD.XTRNL_REIN_REF_NO ;
V_CAPTIVE_IN := OUTER_RECORD.CAPTIVE_IN ;
V_ASCO_MIS012_CUR_CD := OUTER_RECORD.ASCO_MIS012_CUR_CD ;
V_NM_ADJUSTMENT_CD := OUTER_RECORD.NM_ADJUSTMENT_CD ;
V_SOURCE_SYSTEM_ID := OUTER_RECORD.SOURCE_SYSTEM_ID ;
V_MIS780_ASCO_CODE := OUTER_RECORD.MIS780_ASCO_CODE ;
V_POLICY_XPIRTN_DT := OUTER_RECORD.POLICY_XPIRTN_DT ;
V_INSURED_NM := OUTER_RECORD.INSURED_NM ;
V_POLICY_NO := OUTER_RECORD.POLICY_NO ;
V_POLICY_EFCTV_DT := OUTER_RECORD.POLICY_EFCTV_DT ;
V_MIS780_COMP_CODE := OUTER_RECORD.MIS780_COMP_CODE ;
V_MACH_NM := OUTER_RECORD.MACH_NM ;
V_REIN_NO_LAYER_NO := OUTER_RECORD.REIN_NO_LAYER_NO ;
V_REIN_NO_SECTION_NO := OUTER_RECORD.REIN_NO_SECTION_NO ;
V_REIN_NO_FOREIGN_DOM_CD := OUTER_RECORD.REIN_NO_FOREIGN_DOM_CD ;
V_RELTNSHP_TYP_CD := OUTER_RECORD.RELTNSHP_TYP_CD ;
V_SOURCE_REIN_NO := OUTER_RECORD.SOURCE_REIN_NO ;
V_SRC_REIN_EFCTV_DT := OUTER_RECORD.SRC_REIN_EFCTV_DT ;
V_SRC_REIN_UW_YR_NO := OUTER_RECORD.SRC_REIN_UW_YR_NO ;
V_SRC_REIN_KEY_CD := OUTER_RECORD.SRC_REIN_KEY_CD ;
V_SRC_REIN_NO_LAYER_NO := OUTER_RECORD.SRC_REIN_NO_LAYER_NO ;
V_SRC_REIN_NO_SECTION_NO := OUTER_RECORD.SRC_REIN_NO_SECTION_NO ;
V_SRC_REIN_NO_FOREIGN_DOM_CD := OUTER_RECORD.SRC_REIN_NO_FOREIGN_DOM_CD ;
V_PUC_DSP_DIV := OUTER_RECORD.PUC_DSP_DIV ;
V_PUC_DSP_PUC := OUTER_RECORD.PUC_DSP_PUC ;
V_PUC_DSP_SEC := OUTER_RECORD.PUC_DSP_SEC ;
V_LOSS_DT := OUTER_RECORD.LOSS_DT ;
V_DEP_DDSP_DEPT := OUTER_RECORD.DEP_DDSP_DEPT ;
V_TERM_CONDITION_CD := OUTER_RECORD.TERM_CONDITION_CD ;
V_MATRIX_CD := OUTER_RECORD.MATRIX_CD ;
V_MIS404_TAX_ST_CD := OUTER_RECORD.MIS404_TAX_ST_CD ;
V_CREDITED_BRANCH_NO := OUTER_RECORD.CREDITED_BRANCH_NO ;
V_WORKING_BRANCH_NO := OUTER_RECORD.WORKING_BRANCH_NO ;
V_MIS020_CATAS_CD := OUTER_RECORD.MIS020_CATAS_CD ;
V_MIS020_CATAS_YR_NO := OUTER_RECORD.MIS020_CATAS_YR_NO ;
V_CLAIM_BRANCH_NO := OUTER_RECORD.CLAIM_BRANCH_NO ;
V_CLAIM_CASE_NO := OUTER_RECORD.CLAIM_CASE_NO ;
V_KEY_BRANCH_NO := OUTER_RECORD.KEY_BRANCH_NO ;
V_KEY_CASE_NO := OUTER_RECORD.KEY_CASE_NO ;
V_CLA_ID := OUTER_RECORD.CLA_ID ;
V_MIS028_MAJ_CLASS := OUTER_RECORD.MIS028_MAJ_CLASS ;
V_MIS028_STMT_LINE := OUTER_RECORD.MIS028_STMT_LINE ;
V_TRANSACTION_TYP_CD := OUTER_RECORD.TRANSACTION_TYP_CD ;
V_TRANS_SUB_TYP_CD := OUTER_RECORD.TRANS_SUB_TYP_CD ;
V_GEN_LEDGER_ACCT_NO := OUTER_RECORD.GEN_LEDGER_ACCT_NO ;
V_REINSURER_NO := OUTER_RECORD.REINSURER_NO ;
V_REINSURER_BRNCH_CD := OUTER_RECORD.REINSURER_BRNCH_CD ;
V_INTERMEDIARY_NO := OUTER_RECORD.INTERMEDIARY_NO ;
V_INTMDRY_BRNCH_CD := OUTER_RECORD.INTMDRY_BRNCH_CD ;
V_PARTICIPANT_TYP_CD := OUTER_RECORD.PARTICIPANT_TYP_CD ;
V_BALSHT_GL_ACCT_NO := OUTER_RECORD.BALSHT_GL_ACCT_NO ;

PROP_RECORD.END_ASCO_FINANCIAL_AM :=0;
GRET_RECORD:=NULL;

OPEN INNER_CURSOR;
LOOP
FETCH INNER_CURSOR INTO INNER_RECORD;

EXIT WHEN INNER_CURSOR%NOTFOUND; -- OR FLAG=FALSE; --OR INNER_CURSOR%NOTFOUND IS NULL;

/*******************************************CASE WHEN GL_EFFECTIVE_DT'S ARE EQUAL***************************/
IF(INNER_RECORD.GL_EFFECTIVE_DT<OUTER_RECORD.GL_EFFECTIVE_DT)
THEN

PROP_RECORD:=INNER_RECORD;
ELSIF(INNER_RECORD.GL_EFFECTIVE_DT=OUTER_RECORD.GL_EFFECTIVE_DT)
THEN

EQU_RECORD:=INNER_RECORD;
EXIST:=TRUE;
ELSIF(INNER_RECORD.GL_EFFECTIVE_DT>OUTER_RECORD.GL_EFFECTIVE_DT)
THEN

GRET_RECORD:=INNER_RECORD;
FLAG:=FALSE;
END IF;
END LOOP;
CLOSE INNER_CURSOR;
/**************************CASE WHEN THE INCOMING RECORD IS ALREADY PRESENT*******************************/
IF EXIST=TRUE
THEN
/**************************CASE WHEN COUNT_KEYS ARE EQUAL IN BOTH TABLES***************************/
IF EQU_RECORD.COUNT_KEYS=OUTER_RECORD.COUNT_KEYS
THEN
DELETE FROM ETL.MNTHLY_RSRVS WHERE ROWID = EQU_RECORD.ROWID;

/* UPDATE ETL.MNTHLY_RSRVS
SET REAL_IN='W',
LOAD_DATE=SYSDATE
WHERE ROWID = EQU_RECORD.ROWID;*/


....
counter := counter + 1;
IF counter = 1000 THEN
COMMIT;
counter := 0;
END IF;
EXIST:=FALSE;
FLAG:=TRUE;
-- DBMS_OUTPUT.PUT_LINE ('setting the flag and exist as false in the end==>' );

END LOOP;

--COMMIT;
CLOSE OUTER_CURSOR;
COMMIT;
END ;
/
"

echo "---------------------------------------------------" >> ${LOG} echo "SQL script being executed:" >> ${LOG} echo "Negation of Mnthly_Rsrvs table " >> ${LOG}

fnRunSql
rc=$?

echo "---------------------------------------------------" >> ${LOG} printf "Oracle response:" >> ${LOG} printf "$output\n" >> ${LOG} echo "---------------------------------------------------" >> ${LOG}

if (( ${rc} != 0 )) then
fnUpdateBH_Fail
exit 1
fi

fnUpdateBH_Scs

exit 0
===
Tom Kyte
June 08, 2009 - 6:11 pm UTC

haven't these people heard of a JOIN?

this code should be replaced by a SINGLE sql statement.

ORA-4030: process out of memory

Ken, June 08, 2009 - 10:53 pm UTC

Tom:
I ran Jonathan's script to monitor pga memory usag(thanks Jonathan for the script). Please note that the script is still running. In the current script, there is no 'forall and bulkcollect' construct.

Here is the output. I see that 5.6 GB of memory is used for SQL category. What does this mean and how to reduce the memory consumption and allocation of this SQL ? Same for other category also. Thanks.


SQL> @pgamem_usage
Enter value for 1: 1337
old   9: and    ss.sid = &m_sid
new   9: and    ss.sid = 1337

NAME                                                VALUE
---------------------------------------- ----------------
session uga memory                          2,778,674,248
session uga memory max                      4,294,913,480
session pga memory                          2,793,103,080
session pga memory max                      4,294,926,056

old  16:                                where   sid = &m_sid
new  16:                                where   sid = 1337

CATEGORY          ALLOCATED             USED    MAX_ALLOCATED
---------- ---------------- ---------------- ----------------
SQL           5,600,974,592    5,554,064,928    5,600,974,592
PL/SQL               64,912           59,776           64,912
Other         1,486,906,017                     1,486,906,017

old  12:                where   sid = &m_sid
new  12:                where   sid = 1337

    PGA_USED_MEM    PGA_ALLOC_MEM PGA_FREEABLE_MEM      PGA_MAX_MEM
---------------- ---------------- ---------------- ----------------
   7,076,770,361    7,087,945,521                0    7,087,945,521

Tom Kyte
June 09, 2009 - 10:12 am UTC

that would look like a leak - please utilize support and reference note 822527.1 which they can use to help track down where this is allocated from.

ORA-4030: process out of memory

Ken, June 09, 2009 - 11:52 am UTC

Thanks Tom. I will open SR with support.

Is note 822527.1 internal note because metalink search could not find this note ?
Tom Kyte
June 09, 2009 - 12:23 pm UTC

right, that is why I said to utilize support and reference that note. They can see it, you cannot. I could have been more clear I see in hindsight...

PGA_AGGREGATE_TARGET

Jitender, July 30, 2009 - 5:07 am UTC

Hi Tom,

This complete discussion on PGA target is very informative and one can run the script to where his PGA is going on.

But I am wondering, when we have defined the PGA_aggregrate_target with some value then why Oracle let PGA to cross this limit, I was thinking Oracle will return the error when reached to the limit.

As you said "PGA TARGET is target, and possibly can exceed" then what is the main advantage of having it?






Tom Kyte
August 03, 2009 - 5:24 pm UTC

... when we have defined the PGA_aggregrate_target with some
value then why Oracle let PGA to cross this limit, I was thinking Oracle will
return the error when reached to the limit.
...

because - as stated - it is not a limit, it is a target, a goal.

ctl-f for untunable on this page - there is memory we cannot, do not control - that YOU do. We look at what you've done with your memory allocations and take that into consideration - but we cannot stop you from overallocating memory.


The main advantage - it tries to adapt and tries to stay under that target. It tries. the old static method with sort/hash/bitmap area sizes - it was static, it was pure "math". sort area size of 10mb and 10 sorts - you have 100mb of memory, now 1,000 people do it - and you have 10,000mb of memory. If you used pga_aggregate target and said "try to keep it under 5,000mb of memory as that is all we have left over after the SGA is allocated", we would have backed off the size of the sort areas to TRY and stay under the TARGET.

PGA_AGGREGRATE_TARGET

Jitender, July 30, 2009 - 5:58 am UTC

I would like to one thing here.... as per going through the conversation, what I understand is we can limit the Tunable PGA with this parameter but non-tunable parameter we can't and because of only this PGA allocation can exceed.

Hope my understanding is right on it.?

Thanks in advance.
Tom Kyte
August 03, 2009 - 5:25 pm UTC

the non-tunable is in your control - you write the programs that allocate it.

pga in 11.2

Pierre, October 16, 2009 - 5:08 am UTC

Hi Tom,

Could you please confirm that tunable and untunable PGA still exist in 11.2 and that PGA can also exceed MEMORY_TARGET parameter in case of large untunable PGA in 11.2 ?

I've read on Tanel Poder blog http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/ that on Linux Oracle 11.1 is able to move memory from SGA to PGA thanks to dynamic shared memory segments. Is it also the case on Solaris, HP-UX and AIX ?

Thanks.


Re: pga in 11.2

Amardeep Sidhu, October 18, 2010 - 12:00 am UTC

Metalink note MEMORY_MAX_TARGET CAN BE EXCEEDED [ID 1206466.1] talks about that.

Regards,
Amardeep Sidhu

Detecting session which caused high PGA usage

Ashish, December 06, 2010 - 2:22 pm UTC

As it is expected, we see Oracle process allocating a lot more PGA than the PGA_AGGREGATE_TARGET parameter. In fact it is so high that the host is running out of memory.
Is there a way to find the session which caused the high PGA usage after it has already happened. There are V$ view to detect this in real time but that information is lost after session exits and I am not able find any DBA_HIST% views which contain this information. Please help.
Tom Kyte
December 07, 2010 - 9:59 am UTC

I'm not aware of anywhere we store that by session. In ASH (active session history) the focus is on wait events and SQL executed, not metrics like this.

PGA_AGGREGATE_SIZE 9.2.0.8

DJ, December 08, 2010 - 4:47 am UTC

HI Tom.

In you previous posts you mentioned that if optimizer mode is set to RBO it does not look at the PGA_AGGREGATE_SIZE

or sort_area_size.

Currently my init.ora has the following settings

1) PGA_AGGREGATE_SIZE 0
2) DB version 9.2.0.8
3) WORK_AREA_POLICY MANUAL
4) SGA 6787234960
5) SORT_AREA_SIZE 32000000
6) sort_area_retained_size 32000000
7) hash_area_sizze 64000000
8) create_bitmap_area_size 8388608
9) bitmap_merge_area_size 1048576

My instance is running in Dedicated server mode.

I queried from from v$pgastat and got

Total PGA in USE 192568320
Total PGA Allocated 321187840
Maximum PGA Allocated 6113020928

*** all the sizes are mentioned in Bytes

I request to you to help me in understanding where does this Maximum Pga allocated come from?

Am i missing any parameter which shows pga_max allocated if so where can i find it ?

I know in 10g the PGA allocated will be defaulted to 20% of SGA size or 10 Mb which ever is greater,is there any default value in 9i as well ?

In certain posts i saw that for a dss system the PGA allocated will be ((physical_ram)*0.8)*.5) in that case my oracle is installed in solaris 10box having 64gb of RAM. In solaris10 it is mentioned if max_shm_memory is not set for oracle project it will use is 1/4th of the RAM so in that case 16GB is allocated to my instance. If i apply the above formula (((16*1024*1024*1024)*0.8)*0.5) this would be around 6871947673.6 bytes showing a difference of 700 Mb when compared to Maximum PGA Allocated.

Thanks for your help in advance.

Tom Kyte
December 08, 2010 - 9:42 am UTC

"it" in this context is the optimizer. When using the RBO, *rules* are used to optimizer - and none of the optimizer related parameters are consulted. You get the same plan for the same query regardless of any init.ora settings.


All queries will USE pga memory, the comment would have been regarding the query optimization process.

The system itself would use the pga_aggregate_target to allocate workspace memory - but the RBO would NOT use it to optimize the query, to come up with a plan.


the default in 9i is 0 - which effectively disables it
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1158.htm#REFRN10165



PGA memory is allocated AS NEEDED, not initially - you should never expect the maximum pga allocated to be at or even really near the pga_aggregate_target - it would be totally a coincidence if they were the same.

pga profile

Amir Riaz, December 08, 2010 - 11:15 am UTC

Hi tom,

Pga work area is free able. means its memory being allocated and then free. how is that memory being allocated, want to know the formula or its based on some percentage. During some research, i have found that there are some profile in library cache which keeps the track of how much memory is being used for previous time the same sql has been executed on open the cursor we allocate the memory according to that profile and if our memory requirements increase or decrease that change is being kept in those profiles.
Tom Kyte
December 08, 2010 - 12:19 pm UTC

pga memory - PROCESS global area - process memory - is allocated in various ways depending on the OS and features being used. In its simpliest form - it is just an malloc()/realloc()/free() call in C.

The 'formula' varies from release to release - version to version - and is not really documented (if you want the rudiments behind it, I have written it up in my book Expert Oracle Database Architecture - in general - a single session will be able to use up to 5% of the pga_aggregate_target (or less if lots of users are on) and will typically only use at most 50% of that 5% for the first statement that it does that needs memory and then less and less as it opens more and more statements that need memory).


PGA usage

Milind, April 20, 2014 - 3:38 am UTC

Dear Tom,

Please confirm whether my undersatnding is correct :
When we go for sort-merge join or hash join, sorting takes place in PGA. Space occupied in PGA will depend on selected columns only in SELECT list and selected rows based on WHERE clause. Space occupied in PGA does not depend on actual (size of) records in table.

How to find why Oracle has selected Nested loops or hash join?

Thanks in Advance.

12c has a PGA limiter

Duty Calls, July 10, 2017 - 4:00 pm UTC

pga_aggregate_target and memory_max_target

INDRANIL, October 25, 2017 - 7:45 am UTC

Hi Tom
Thank you so much for such an excellent explanation about PGA. Here is the issue - we are currently facing in our UK Production DB Server,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. 1st, End-Users reported overall slowness in the Database Response.
2. When checked in the related AWRs for that- we found below wait event - direct path read temp in "Top 10 Foreground Events by Total Wait Time" section with huge values like - 22,442,805 Under Waits Column.
3. When checked in "SQL ordered by Elapsed Time", we found a SQL is taking like - Elapsed Time per Exec (s) = 12,780.68 sec i.e 213 mins = 4 hrs(approx) and this query is associated with HUGE Sort operation like - "Group By" clause with Parallel Hint.
4. When checked found - memory_max_target and memory_target are both 5G with sga_max_size=5G & sga_target=4992mb. where as workarea_size_policy = AUTO but pga_aggregate_target = 0.
=>It looks like DB is running in Automatic Memory Management (AMM) mode but somehow pga_aggregate_target value is set to 0.
5. Lastly, when checked in "PGA Memory Advisory", Estd PGA Overalloc Count will become 0 if we at least set it ( pga_aggregate_target ) to 384 mb and Ideally to 1024mb(-1Gb) to minimize Estd Extra W/A MB Read/ Written to Disk to its lowest value.
Conclusion: we suggested customer the following recommendations -
pga_aggregate_target is not getting enough room to grow when it actually requires. Since value of sga_target is close to memory target so pga_aggregate is not getting required memory

current value
--------------
memory_max_target =5G
memory_target =5G
sga_max_size=5G
sga_target=4992M
Therefore We would recommend to increase memory_max_target and memory_target from 5gb to 8960 MB( as per AWR, SGA Target Advisory ).
@Hi Tom Are we in Right Track ?
Regards
INDRANIL
Connor McDonald
October 26, 2017 - 12:47 am UTC

memory_target is basically aimed at sites running a basic/small Oracle instance with no management intervention planned.

For a larger installation I would recommend using

sga_target
pga_aggregate_target

for more control.

You didn't mention the RAM on your server, so we can't really advise what you should set things to. And don't forget that SQL tuning is often a more effective weapon than memory tuning :-)