1 2 Previous Next 16 Replies Latest reply: Jan 24, 2013 11:06 PM by 975148 RSS

    SGA & PGA concept

    975148
      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
          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
            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....
              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
                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
                  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....
                    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
                      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
                        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....
                          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
                            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
                              yes thats a typo, its ASM
                              • 12. Re: SGA & PGA concept
                                Aman....
                                Thanks, I have just corrected it!

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

                                  Do we blame the techies or the marketeers?
                                  • 14. Re: SGA & PGA concept
                                    Aman....
                                    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