Skip to Main Content
  • Questions

  • Checking maximum usage for SGA and PGA

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, vaibhav.

Asked: November 22, 2021 - 3:48 pm UTC

Last updated: February 28, 2023 - 1:57 am UTC

Version: 19.3 , 12.2, 11.2

Viewed 50K+ times! This question is

You Asked

Hello Tom,

Is there a way to find out the maximum SGA and PGA ever used in an instance ?

We need to do some strict assessment where we need to check whether the SGA and PGA assigned by us is being completely utilised or not. If not 100% utilised then how to find it ? so that we can released the unused memory back to the OS ?

In a nutshell how to find the maximum SGA and PGA ever used by an instance ? Similar to checking maximum temp ever used, is it possible to get those details for SGA and PGA ?

Thanks,
Vaibhav

and Connor said...

A better way would be to use the advisors which will tell you sizing details for the SGA/PGA plus heuristics to tell you what benefit/degradation you would see should be alter their size.

Check out

V$SGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE

and these are also captured in AWR so you can look at historical snapshots.

For example, on my laptop here

SQL> select * from V$SGA_TARGET_ADVICE;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
      1536              .5        77194              4.0654           166723785
      2304             .75        20784              1.0946           104265809
      3072               1        18988                   1            99518764
      3840            1.25        18792               .9897            99200304
      4608             1.5        18667               .9831            99100785
      5376            1.75        18623               .9808            99041074
      6144               2        18612               .9802            99031122


I can see that things are pretty much fine unless I drop to 1500 at which point things are going to go south very quickly.

Rating

  (4 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Thanks a lot for your response on this.

vaibhav kansara, November 29, 2021 - 10:32 am UTC

Hello,

Thanks for your response on this.

I'm a little bit new to Oracle so could you please tell me what does columns from the table SGA_TARGET_ADVICE signify ?

And how does that help me determine how much sga size I shoud assign to my DB ?

Below is the sample output from one of my database : 

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE     CON_ID
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ----------
     18560            .125      4797623              8.8786            82376952                  13824                  2560          0
     27840           .1875       600986              1.1122            82376952                  13824                 10752          0
     37120             .25       554461              1.0261            25397336                  27648                  7680          0
     46400           .3125       553327               1.024            22865771                  41472                  4608          0
     55680            .375       552408              1.0223            22865771                  41472                  9216          0
     64960           .4375       547275              1.0128            16544948                  55296                  7680          0
     74240              .5       540790              1.0008             8088875                  69120                  6144          0
     83520           .5625       540358                   1             8088875                  69120                  7680          0
     92800            .625       540358                   1             8088066                  82944                  7680          0
    102080           .6875       540358                   1             8088066                  96768                  7680          0
    111360             .75       540358                   1             8088066                  96768                  7680          0
    120640           .8125       540358                   1             8088066                 110592                  7680          0


Connor McDonald
December 01, 2021 - 3:02 am UTC

The two ones easiest to focus on are:

ESTD_DB_TIME_FACTOR
ESTD_PHYSICAL_READS

The advisory is saying that with an sga size factor of 0.5625 (ie almost half of your existing) will still result in a DB Time of 1, ie, you would not see any degradation in DB time (ie active work on the database) even if the SGA was ~50% of its current size.

Similarly with physical reads. We're saying that you won't see a jump in physical reads (from its current 8088066 ) until you shrink the SGA to 43% (0.4375) of its current size.

So in a nutshell - your SGA is plenty big enough, and you could even release up to half of it to other services on that server without getting impacted.

But remember - these are advisories - in a production scenario, you would slowly ease back on the allocation and monitor again. Similarly, if nothing else on the server needs that memory, it wont really hurt to have an excess.

The advisor knows too.

Andrew, November 29, 2021 - 9:06 pm UTC

I find this script also to be helpful in getting an idea of how much SGA the database wants. This script is easily modified for PGA, as well.
COLUMN finding_time      FOR a20    HEAD 'Finding Time'
COLUMN current_target_mb FOR 999,999 HEAD 'Current SGA|Target (MB)'
COLUMN advised_target_mb FOR 999,999 HEAD 'Advised SGA|Target (MB)'

BREAK ON REPORT
COMPUTE maximum LABEL 'Recommended Min. SGA' OF advised_target_mb ON report

SELECT TO_CHAR( al.execution_start, 'dd Mon yyyy HH24:MI:SS') AS finding_time,
       aa.num_attr1/1048576 AS current_target_mb,
       aa.num_attr2/1048576 AS advised_target_mb
  FROM dba_advisor_actions  aa,
       dba_advisor_findings af,
       dba_advisor_log      al
 WHERE 1=1
   AND al.owner          = af.owner
   AND al.task_name      = af.task_name
   AND aa.owner          = af.owner
   AND aa.task_name      = af.task_name
   AND aa.execution_name = af.execution_name
   AND af.finding_name   = 'Undersized SGA'
   AND aa.attr1          = 'sga_target'
 ORDER
    BY al.execution_start
;
clear computes

Connor McDonald
December 01, 2021 - 3:03 am UTC

Nice input!

great response

vaibhav kansara, December 01, 2021 - 6:54 am UTC

Thanks guys,

I'm glad I learned something new today on this topic. I appreciate you taking your time to answer this.

Connor McDonald
December 01, 2021 - 8:09 am UTC

glad we could help

Which one give correct PGA in usage for using alerting through OEM

ArunB, February 09, 2023 - 10:00 pm UTC

total PGA inuse

Indicates how much PGA memory is currently consumed by work areas. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-PGASTAT.html#GUID-4666F72E-1E2F-4FFF-89C7-E8144657F78A

An Example DB gives:
select round(sum(pga_used_mem)/1024/1024,0) as TOTAL_PGA_USAGE_MB from v$process;

7381

SELECT round(sum(VALUE)/1024/1024,0) as TOTAL_PGA_USAGE_MB FROM  V$PGASTAT where NAME in ('total PGA inuse');

9429

Why the difference and which one we should use?

Connor McDonald
February 28, 2023 - 1:57 am UTC

I've asked around internally without much success (and also not managed to reproduce this on any of my own databases)

I think the best way forward is log an SR
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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