FAQ
All,

I'm trying to figure out high memory usage on one of the DB servers we have
and wondering if pga_aggregate_target could ever exceed from what's
allocated.

We have sga_target set to 16G and pga_a_t set to 2G, however from statspack,
i see pga usage to be close to 3G. Is that normal. I have posted relevant
sections from statspack for reference. Though i see only 59% of total
memory being used, vmstat reports 29G being used. Any idea what could be
causing it. This node is a dedicated DB server and only one DB runs on it.

Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 32,640.0 32,640.0
SGA use (MB): 16,315.7 16,315.7
PGA use (MB): 2,854.4 2,981.5
% Host Mem used for SGA+PGA: 58.7 59.1
-------------------------------------------------------------
Instance Activity Stats
workarea memory allocated 0 6,043

Statistic Total per Second per

Trans
--------------------------------- ------------------ --------------

workarea executions - onepass 0 0.0
0.0
workarea executions - optimal 7,635 2.1

0.0

--
Thanks,

Stalin

11gR2, Solaris 10

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • D'Hooge Freek at Aug 26, 2011 at 8:14 am
    Hi,

    Yes, pga_aggregate_target is kind of a soft limit.
    For certain memory requests it will have no other option to give memory to the process requesting it.

    You can see the current allocation and how many times oracle had to exceed the pga_aggregate_target limit in v$pgastat.

    Regards,

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer

    ---
    From: oracle-l-bounce_at_freelists.org On Behalf Of Stalin
    Sent: vrijdag 26 augustus 2011 10:02
    To: oracle-l
    Subject: High Memory Usage

    All,

    I'm trying to figure out high memory usage on one of the DB servers we have and wondering if pga_aggregate_target could ever exceed from what's allocated.

    We have sga_target set to 16G and pga_a_t set to 2G, however from statspack, i see pga usage to be close to 3G. Is that normal. I have posted relevant sections from statspack for reference.  Though i see only 59% of total memory being used, vmstat reports 29G being used. Any idea what could be causing it. This node is a dedicated DB server and only one DB runs on it.

    Memory Statistics                       Begin          End
    ~~~~~~~~~~~~~~~~~                ------------ ------------
    Host Mem (MB):     32,640.0     32,640.0
    SGA use (MB):     16,315.7     16,315.7
    PGA use (MB):      2,854.4      2,981.5
    % Host Mem used for SGA+PGA:         58.7         59.1
    -------------------------------------------------------------
    Instance Activity Stats
    workarea memory allocated                   0        6,043

    Statistic                                      Total     per Second    per Trans
    --------------------------------- ------------------ -------------- ------------
    workarea executions - onepass                      0            0.0          0.0
    workarea executions - optimal                  7,635            2.1          0.0

    --
    Thanks,

    Stalin

    11gR2, Solaris 10
    --
    http://www.freelists.org/webpage/oracle-l
  • Stalin at Aug 26, 2011 at 4:01 pm
    Thanks. Checking v$pgastat, maximum pga allocated has been around 3.5G,
    still that doesn't add up to 29G.

    Any other ideas?
    On Fri, Aug 26, 2011 at 1:14 AM, D'Hooge Freek wrote:

    Hi,

    Yes, pga_aggregate_target is kind of a soft limit.
    For certain memory requests it will have no other option to give memory to
    the process requesting it.

    You can see the current allocation and how many times oracle had to exceed
    the pga_aggregate_target limit in v$pgastat.


    Regards,


    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer
    ---
    --
    http://www.freelists.org/webpage/oracle-l
  • K R at Aug 30, 2011 at 2:23 am
    any chance filecache/pagecache taken by os
    On Fri, Aug 26, 2011 at 9:01 AM, Stalin wrote:

    Thanks. Checking v$pgastat, maximum pga allocated has been around 3.5G,
    still that doesn't add up to 29G.

    Any other ideas?

    On Fri, Aug 26, 2011 at 1:14 AM, D'Hooge Freek wrote:

    Hi,

    Yes, pga_aggregate_target is kind of a soft limit.
    For certain memory requests it will have no other option to give memory to
    the process requesting it.

    You can see the current allocation and how many times oracle had to exceed
    the pga_aggregate_target limit in v$pgastat.


    Regards,


    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer
    ---
    --
    http://www.freelists.org/webpage/oracle-l
  • LS Cheng at Aug 30, 2011 at 7:26 pm
    what sort of filesystem are you using?

    thanks

    --
    LSC
    On Fri, Aug 26, 2011 at 10:02 AM, Stalin wrote:

    All,

    I'm trying to figure out high memory usage on one of the DB servers we have
    and wondering if pga_aggregate_target could ever exceed from what's
    allocated.

    We have sga_target set to 16G and pga_a_t set to 2G, however from
    statspack, i see pga usage to be close to 3G. Is that normal. I have posted
    relevant sections from statspack for reference. Though i see only 59% of
    total memory being used, vmstat reports 29G being used. Any idea what could
    be causing it. This node is a dedicated DB server and only one DB runs on
    it.

    Memory Statistics Begin End
    ~~~~~~~~~~~~~~~~~ ------------ ------------
    Host Mem (MB): 32,640.0 32,640.0
    SGA use (MB): 16,315.7 16,315.7
    PGA use (MB): 2,854.4 2,981.5
    % Host Mem used for SGA+PGA: 58.7 59.1
    -------------------------------------------------------------
    Instance Activity Stats
    workarea memory allocated 0 6,043

    Statistic Total per Second per
    Trans
    --------------------------------- ------------------ --------------
    ------------
    workarea executions - onepass 0 0.0
    0.0
    workarea executions - optimal 7,635 2.1
    0.0

    --
    Thanks,

    Stalin

    11gR2, Solaris 10
    --
    http://www.freelists.org/webpage/oracle-l
  • Stalin at Aug 30, 2011 at 7:40 pm
  • LS Cheng at Aug 31, 2011 at 7:23 am
    hi

    since its solaris you could try run prstat -a, this will show you the memory
    consumed by each OS user

    thanks
    On Tue, Aug 30, 2011 at 9:40 PM, Stalin wrote:

    We are using UFS, however the FS where the datafiles are housed, are
    mounted with forcedirectIO and in DB we have filesystemio_options set to
    setall.
    On Tue, Aug 30, 2011 at 12:26 PM, LS Cheng wrote:

    what sort of filesystem are you using?

    thanks

    --
    LSC
    On Fri, Aug 26, 2011 at 10:02 AM, Stalin wrote:

    All,

    I'm trying to figure out high memory usage on one of the DB servers we
    have and wondering if pga_aggregate_target could ever exceed from what's
    allocated.

    We have sga_target set to 16G and pga_a_t set to 2G, however from
    statspack, i see pga usage to be close to 3G. Is that normal. I have posted
    relevant sections from statspack for reference. Though i see only 59% of
    total memory being used, vmstat reports 29G being used. Any idea what could
    be causing it. This node is a dedicated DB server and only one DB runs on
    it.

    Memory Statistics Begin End
    ~~~~~~~~~~~~~~~~~ ------------ ------------
    Host Mem (MB): 32,640.0 32,640.0
    SGA use (MB): 16,315.7 16,315.7
    PGA use (MB): 2,854.4 2,981.5
    % Host Mem used for SGA+PGA: 58.7 59.1
    -------------------------------------------------------------
    Instance Activity Stats
    workarea memory allocated 0 6,043

    Statistic Total per Second
    per Trans
    --------------------------------- ------------------ --------------
    ------------
    workarea executions - onepass 0 0.0
    0.0
    workarea executions - optimal 7,635 2.1
    0.0

    --
    Thanks,

    Stalin

    11gR2, Solaris 10

    --
    Thanks,

    Stalin
    --
    http://www.freelists.org/webpage/oracle-l
  • Adric Norris at Aug 31, 2011 at 1:42 pm

    On Wed, Aug 31, 2011 at 02:23, LS Cheng wrote:

    since its solaris you could try run prstat -a, this will show you the
    memory consumed by each OS user
    That brings back some fun memories. :) I once had a SysAdmin complain that
    Oracle was using 100% of the memory on a Sun box, which was equipped with 64
    GB of physical memory and configured for 8 GB of swap space. The "prstat
    -a" output, which he helpfully included in his email, clearly showed Oracle
    consuming 1.5 TB of memory.

    Needless to say, that command has absolutely no knowledge of shared memory.

    --
    "I'm too sexy for my code." -Awk Sed Fred

    --
    http://www.freelists.org/webpage/oracle-l
  • LS Cheng at Aug 31, 2011 at 4:07 pm
    what version of solaris?

    this prstat -a output from our server which has 192 GB physical memory

    NPROC USERNAME SWAP RSS MEMORY TIME CPU

    1420 rac10gr4 84G 80G 42% 287:44:04 15%

    ps -ef|grep rac10gr4|wc -l

    1423

    it takes into consideration shared memory
    On Wed, Aug 31, 2011 at 3:42 PM, Adric Norris wrote:
    On Wed, Aug 31, 2011 at 02:23, LS Cheng wrote:

    since its solaris you could try run prstat -a, this will show you the
    memory consumed by each OS user
    That brings back some fun memories. :) I once had a SysAdmin complain that
    Oracle was using 100% of the memory on a Sun box, which was equipped with 64
    GB of physical memory and configured for 8 GB of swap space. The "prstat
    -a" output, which he helpfully included in his email, clearly showed Oracle
    consuming 1.5 TB of memory.

    Needless to say, that command has absolutely no knowledge of shared memory.

    --
    "I'm too sexy for my code." -Awk Sed Fred
    --
    http://www.freelists.org/webpage/oracle-l
  • Adric Norris at Aug 31, 2011 at 4:39 pm
    It's been awhile, so it might have been a Solaris 8 box. I didn't think
    this behaviour had changed under Solaris 10, but could certainly be
    mistaken.
    On Wed, Aug 31, 2011 at 11:07, LS Cheng wrote:

    what version of solaris?

    this prstat -a output from our server which has 192 GB physical memory

    NPROC USERNAME SWAP RSS MEMORY TIME CPU
    1420 rac10gr4 84G 80G 42% 287:44:04 15%

    ps -ef|grep rac10gr4|wc -l
    1423

    it takes into consideration shared memory

    On Wed, Aug 31, 2011 at 3:42 PM, Adric Norris wrote:
    On Wed, Aug 31, 2011 at 02:23, LS Cheng wrote:

    since its solaris you could try run prstat -a, this will show you the
    memory consumed by each OS user
    That brings back some fun memories. :) I once had a SysAdmin complain
    that Oracle was using 100% of the memory on a Sun box, which was equipped
    with 64 GB of physical memory and configured for 8 GB of swap space. The
    "prstat -a" output, which he helpfully included in his email, clearly showed
    Oracle consuming 1.5 TB of memory.

    Needless to say, that command has absolutely no knowledge of shared
    memory.
    --
    "I'm too sexy for my code." -Awk Sed Fred

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 26, '11 at 8:02a
activeAug 31, '11 at 4:39p
posts10
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase