This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jan 24, 2013 9:06 PM by 975148 RSS

SGA & PGA concept

975148 Newbie
Currently Being Moderated
We have a production Oracle 11g DB in which SGA_TARGET is 0 but ASSM is enabled but the documentation on Oracle says that if SGA_TARGET is 0 means that ASSM is disabled. Also, PGA_AGGREGATE_TARGET is 0 but Maximum PGA allocated is around 1 GB.

I hope, my question is clear that concept seems to be confusing where SGA_TARGET is 0 but ASSM is enabled and also PGA_AGGREGATE_TARGET is 0 but maximum PGA allocated is 1 GB.

Please revert with the reply to my query.

Regards
  • 1. Re: SGA & PGA concept
    karan Pro
    Currently Being Moderated
    ASMM is disabled if sga_target = 0 and PGA_AGGREGATE_TARGET will be managed according to the ram available in your server, PGA is auto tuned when MEMORY_TARGET is enabled, PGA_MAX_MEM is the max a process had allocated which is not the same as the present memory allocated, if memory is short it would be taken from OS for PGA.

    Regards
    Karan
  • 2. Re: SGA & PGA concept
    Niket Kumar Pro
    Currently Being Moderated
    automatic tunning of sga and pga will started if you have enable MEMORY_TARGET and MEMORY_MAX_TARGET .if you set value of MEMORY_TARGET to a non zero value then sga target and pga target auto tuning starts automatically whether its zero or not and depends on memory target....

    check below documentation.....
    http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm

    http://oracle-online-help.blogspot.in/2008/03/memory-parameters-in-11g-memorytarget.html

    Edited by: Niket Kumar on Jan 18, 2013 5:19 PM
  • 3. Re: SGA & PGA concept
    Aman.... Oracle ACE
    Currently Being Moderated
    And how did you arrive at the conclusion that the ASMM is enabled when the parameter sga_target is set to zero? What's the basis of this assumption in the first place? Or are you getting confused between ASMM and AMM? Show us the value of the parameter memory target and post the output here.

    Aman....
  • 4. Re: SGA & PGA concept
    975148 Newbie
    Currently Being Moderated
    I checked from OEM that ASMM is enabled and sga_target from values of initialization parameters through OEM.

    Please find the output of show parameter target,

    SQL> show parameter target

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    archive_lag_target integer 0
    db_flashback_retention_target integer 1440
    fast_start_io_target integer 0
    fast_start_mttr_target integer 0
    memory_max_target big integer 6448M
    memory_target big integer 6448M
    parallel_servers_target integer 64
    pga_aggregate_target big integer 0
    sga_target big integer 0

    Regards
  • 5. Re: SGA & PGA concept
    975148 Newbie
    Currently Being Moderated
    thanks and the Oracle article in the link http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm is very good which says memory_max_target is the max value of RAM that can be taken by the Oracle DB.

    Aman..-> If you can please go through this article, it is very good.

    Thanks.
  • 6. Re: SGA & PGA concept
    Aman.... Oracle ACE
    Currently Being Moderated
    I don't think there is a need to look at the article. As I expected, you are not having ASMM but AMM enabled as the parameter memory target is set to a non zero value. Spend some time reading Oracle documentation as it contains a choke full of useful information.

    Aman....
  • 7. Re: SGA & PGA concept
    975148 Newbie
    Currently Being Moderated
    Thanks for your reply but there is some confusion. ASMM is automatic shared memory management which seems to be similar to AMM which is automatic memory management. ASMM can be seen from OEM under Database Configuration->Memory Advisors. I guess, there is ASM which is automatic storage management to do with reduntant copies of data to provide fault tolerance.
  • 8. Re: SGA & PGA concept
    karan Pro
    Currently Being Moderated
    ASM is completely related to storage alltogether while ASMM and AMM are related to RAM, AMM auto tunes SGA and PGA while ASMM only sga but that too auto components, like i said ASMM and ASMM are different in the sense that the latter would auto tune PGA as well but the former would not. Please read oracle doc which aman said, it would help you a lot. Please see http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm

    Regards
    Karan
  • 9. Re: SGA & PGA concept
    Aman.... Oracle ACE
    Currently Being Moderated
    972145 wrote:
    Thanks for your reply but there is some confusion. ASMM is automatic shared memory management which seems to be similar to AMM which is automatic memory management. ASMM can be seen from OEM under Database Configuration->Memory Advisors. I guess, there is ASM which is automatic storage management to do with reduntant copies of data to provide fault tolerance.
    ASMM - 10g setting for the automatic SGA using the parameter sga_target .
    AMM - 11g setting for both SGA and PGA, uses the parameter memory_target
    ASM - Automatic Storage Management, nothing to do with SGA or PGA
    ASSM - Automatic Segment Space Management, for the tables and nothing to do with the PGA and SGA

    Aman....

    Edited by: Aman.... on Jan 21, 2013 3:53 PM corrected the typo
  • 10. Re: SGA & PGA concept
    975148 Newbie
    Currently Being Moderated
    Thanks, I think a typo mistake in your reply. The 3rd line should read ASM and not AMM (Automatic Storage Management).
  • 11. Re: SGA & PGA concept
    karan Pro
    Currently Being Moderated
    yes thats a typo, its ASM
  • 12. Re: SGA & PGA concept
    Aman.... Oracle ACE
    Currently Being Moderated
    Thanks, I have just corrected it!

    Aman....
  • 13. Re: SGA & PGA concept
    Dave Rabone Journeyer
    Currently Being Moderated
    It's a really unfortunate collection of acronyms.

    Do we blame the techies or the marketeers?
  • 14. Re: SGA & PGA concept
    Aman.... Oracle ACE
    Currently Being Moderated
    Dave Rabone wrote:
    It's a really unfortunate collection of acronyms.
    It really is and trust me, I still get a slip of tongue even when I am mentioning them :) .
    Do we blame the techies or the marketeers?
    Hmm I guess marketeers but it won't help whom we blame , does it :) ?

    Aman....
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points