Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Krishnaprasad.

Asked: September 03, 2017 - 7:14 am UTC

Last updated: September 05, 2017 - 6:26 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi ,

in our environment , from few days we are receiving ora-4030 for pga.

I just wanted to know how much pga maximum can use system memory ?

As far as my basic understanding , pga can use , full system memory including swap as well or it can consume 3*<no.of process value of database >

Additionally , how we can identified query causing ora-4030 ( iam assuming that most of time, it is caused by sql statement )

Regards,
Krishna

and Connor said...

I just wanted to know how much pga maximum can use system memory ?

It varies by platform and version, but typically you're looking at 2G or 4G for a single session as a ceiling, but several parameters will potentially shrink that down to a far lower size, because the common parameter (pga_aggreate_target) is what controls the allocation of entire pga consumption among sessions. pga_aggregate_limit is a new 12c parameter which sets a hard limit on the pga at the instance level.

You can see an event to dump out additional details whenever you get an ora-4030

alter system set events '4030 trace name errorstack level 3';

but rather than wait for the errors to occur, you can look at v$sesstat for statistics "session pga memory" and "session pga memory max" and keep an eye on what each session is consuming.

iam assuming that most of time, it is caused by sql statement

Unlikely, because we'll start using temporary segment space rather than run out of memory. Common candidates are plsql memory structures growing out of control (associative arrays etc)

Rating

  (1 rating)

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

Comments

A reader, September 04, 2017 - 2:06 pm UTC

Hi,

Can you please let know how to find historic session details ,consuming huge pga

Again in 11g does pga growth depends on number process *3 ,or double of pga size
Connor McDonald
September 05, 2017 - 6:26 am UTC

DBA_HIST_PGASTAT contains pga details per AWR snapshot, and DBA_HIST_ACTIVE_SESS_HISTORY for session level detail.

In 11g, we'll try keep to pga_aggregate_target, but if you go way over (eg with a giant PLSQL table) there's nothing we can do to stop it...and eventually you'll blow up

(Hence the new parameter in 12)

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database