11 Replies Latest reply on Aug 2, 2009 3:40 AM by mohitanchlia

    PGA and SGA sizing on linux

    mohitanchlia
      Oracle 11.0.1.7 on linux 64bit:

      Just a basic question about PGA and SGA. Our SGA_MAX is 9GB and PGA_AGGREGATE_TARGET is 4800M so I expected the output of "ipcs -a" to be 9GB + 4800M. But it actually is just 9GB. When I query V$PGASTAT it shows that 4800M was allocated. So does it mean that shared memory is not used for this purpose? Everything is in RAM? I am trying to calculate what my SGA and PGA should be on 16GB RAM where Oracle is the only dominant process. PGA_ADVICE says 3600M is optimal. So is it ok to use 11G + 3600M and leave just 1GB for OS?

      I am also looking at enabling HugePages as I see lots of pages are being paged out.
        • 1. Re: PGA and SGA sizing on linux
          sb92075
          post results from following SQL

          select * from v$SGA_TARGET_ADVICE ;

          Remember, bigger does not necessarily mean better/faster.
          • 2. Re: PGA and SGA sizing on linux
            mohitanchlia
            Here is what I get.
            SGA_SIZE     SGA_SIZE_FACTOR     ESTD_DB_TIME     ESTD_DB_TIME_FACTOR     ESTD_PHYSICAL_READS
            1     9216     1     16033     1     1716246
            2     3456     0.375     25694     1.6026     4104917
            3     4608     0.5     21438     1.3371     3052344
            4     5760     0.625     19437     1.2123     2557721
            5     6912     0.75     17888     1.1157     2174999
            6     8064     0.875     16804     1.0481     1906921
            7     10368     1.125     15514     0.9676     1588042
            8     11520     1.25     15283     0.9532     1530720
            9     12672     1.375     15238     0.9504     1519564
            10     13824     1.5     15236     0.9503     1519221
            11     14976     1.625     15236     0.9503     1519221
            12     16128     1.75     15236     0.9503     1519221
            13     17280     1.875     15236     0.9503     1519221
            14     18432     2     15236     0.9503     1519221
            In general is 1GB left for OS enough. Also, I had other questions about PGA. Is PGA allocated in RAM and shared memory is not used? Also, PGA_AGGREGATE_TARGET means that much memory is pre-reserved?
            • 3. Re: PGA and SGA sizing on linux
              JustinCave
              SGA is shared memory, so it is allocated when the database is started. PGA, on the other hand, is memory that is allocated to particular processes, so it isn't allocated until there is a process that needs it. If there is little or no activity in your database at a particular instant, you would expect that little or none of the PGA would actually be allocated at the operating system level.

              Justin
              • 4. Re: PGA and SGA sizing on linux
                sb92075
                Thanks!
                My assessment is that the SGA needs to be no larger than 1.5GB or 2GB MAX.

                Post the results from the following SQL

                select * from v$PGA_TARGET_ADVICE
                • 5. Re: PGA and SGA sizing on linux
                  mohitanchlia
                  What does PGA_AGGREGATE_TARGET really mean? I thought it means that pre-allocate that much RAM. At least that's what it looked like when I read about this parameter on META LINK.

                  I am not sure why SGA_TARGET_ADVICE shows only 2GB, it's so low for the given workload. When I set it to this low I know DB gives really bad performace. Are results from that view really accurate?

                  PGA_ADVICE says
                          PGA_TARGET_FOR_ESTIMATE     PGA_TARGET_FACTOR     ADVICE_STATUS     BYTES_PROCESSED     ESTD_TIME     ESTD_EXTRA_BYTES_RW     ESTD_PGA_CACHE_HIT_PERCENTAGE     ESTD_OVERALLOC_COUNT
                  1     629145600     0.125     ON     2040248320     1833294     9601024     100     40
                  2     1258291200     0.25     ON     2040248320     1833294     9601024     100     39
                  3     2516582400     0.5     ON     2040248320     1824707     0     100     7
                  4     3774873600     0.75     ON     2040248320     1824707     0     100     0
                  5     5033164800     1     ON     2040248320     1824707     0     100     0
                  6     6039797760     1.2     ON     2040248320     1824707     0     100     0
                  7     7046430720     1.4     ON     2040248320     1824707     0     100     0
                  8     8053063680     1.6     ON     2040248320     1824707     0     100     0
                  9     9059696640     1.8     ON     2040248320     1824707     0     100     0
                  10     10066329600     2     ON     2040248320     1824707     0     100     0
                  11     15099494400     3     ON     2040248320     1824707     0     100     0
                  12     20132659200     4     ON     2040248320     1824707     0     100     0
                  13     30198988800     6     ON     2040248320     1824707     0     100     0
                  14     40265318400     8     ON     2040248320     1824707     0     100     0
                  Edited by: user628400 on Aug 1, 2009 7:29 PM
                  • 6. Re: PGA and SGA sizing on linux
                    JustinCave
                    PGA_AGGREGATE_TARGET tells Oracle to try not to allow the total RAM allocated to the various user processes to exceed a particular threshold. In your case, you are telling Oracle that you want to limit the total SGA allocated at any instant to 4.8 GB or less. If there is no activity, that "or less" could be as low as 0. Since PGA (the Process Global Area) is private memory allocated to individual processes, it can't be preallocated.

                    Justin
                    1 person found this helpful
                    • 7. Re: PGA and SGA sizing on linux
                      mohitanchlia
                      Thanks! I am not sure why SGA_ADVICE view is reporting such a low number. I know that number is not correct. Is there something else I need to look at to determine correct size?

                      Also, in your experience is 1GB enough for OS given that only oracle process is running on that box?
                      • 8. Re: PGA and SGA sizing on linux
                        JustinCave
                        The SGA_ADVICE column is based on actual activity, not going out to the operating system to figure out how much RAM is available and backing in to a reasonable SGA and PGA allocation. If your database hasn't been under a lot of load yet, SGA_ADVICE will tell you that you could radically reduce the size of the SGA without substantial effects on performance. Of course, when you add load/ add data volume, that advice will generally change pretty quickly.

                        Justin
                        • 9. Re: PGA and SGA sizing on linux
                          sb92075
                          I know that number is not correct.
                          Based upon which metric(s) & what value(s) lead you to the conclusion above?

                          What algorithm do you proposed to produce a value that you deem correct?
                          • 10. Re: PGA and SGA sizing on linux
                            mohitanchlia
                            Since I know behaviour of system I also know that with that SGA system is going to crawl to it's knees.
                            • 11. Re: PGA and SGA sizing on linux
                              mohitanchlia
                              This is another reason. For the same time when I took the advice I also pulled ADDM report. ADDM report is suggesting a increase but SGA_ADVICE says something different.
                              Finding 4: Undersized SGA
                              Impact is .61 active sessions, 14% of total activity.
                              -----------------------------------------------------
                              The SGA was inadequately sized, causing additional I/O or hard parses.
                              The value of parameter "sga_target" was "9216 M" during the analysis period.
                              
                                 Recommendation 1: Database Configuration
                                 Estimated benefit is .26 active sessions, 5.91% of total activity.
                                 ------------------------------------------------------------------
                                 Action
                                    Increase the size of the SGA by setting the parameter "sga_target" to
                                    10368 M.
                              
                                 Symptoms That Led to the Finding:
                                 ---------------------------------
                                    Wait class "User I/O" was consuming significant database time.
                                    Impact is 1.5 active sessions, 34.57% of total activity.