1 2 Previous Next 19 Replies Latest reply on Jul 7, 2010 4:38 PM by 734787

    V$PGASTAT question about PGA used vs allocated

    614789
      Hi,


      I have a 64G system that has about 24G set to pga_aggregate_target. I ran a huge query on about 500M rows of data and this is the outout of V$PGASTAT. Why is it that I see so little like 3G only allocated for "total PGA allocated" and so little "total PGA inuse". Since none of the other connections are doing anything I would expect oracle to use more PGA for sorting and has-joins right? Could some one please explain, thanks.
      SQL> select * from V$PGASTAT;

      NAME                                                         VALUE UNIT
      ---------------------------------------- ------------------------- ------------
      aggregate PGA target parameter                         25769803776 bytes
      aggregate PGA auto target                              22901907456 bytes
      global memory bound                                     1073741824 bytes
      total PGA inuse                                         3491868672 bytes
      total PGA allocated                                     3630533632 bytes
      maximum PGA allocated                                   3648694272 bytes
      total freeable PGA memory                                 31850496 bytes
      process count                                                   42
      max processes count                                             98
      PGA memory freed back to OS                            28158984192 bytes
      total PGA used for auto workareas                       3170180096 bytes

      NAME                                                         VALUE UNIT
      ---------------------------------------- ------------------------- ------------
      maximum PGA used for auto workareas                     3435089920 bytes
      total PGA used for manual workareas                              0 bytes
      maximum PGA used for manual workareas                       531456 bytes
      over allocation count                                            0
      bytes processed                                       104064153600 bytes
      extra bytes read/written                               66399246336 bytes
      cache hit percentage                                            61 percent
      recompute count (total)                                      32614

      19 rows selected.
        • 1. Re: V$PGASTAT question about PGA used vs allocated
          247514
          your total PGA in use is about 3G, depends on your average row size of your 500M rows and the amount of actual sorting. That's probably all the PGA you need at the time. Why would you expect Oracle use more PGA ?

          This part of Oracle document talks about tuning PGA
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#PFGRF01401

          for example, you can use this query to find out current active PGA usage,
          SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
                 operation_type OPERATION,
                 trunc(EXPECTED_SIZE/1024) ESIZE,
                 trunc(ACTUAL_MEM_USED/1024) MEM,
                 trunc(MAX_MEM_USED/1024) "MAX MEM",
                 NUMBER_PASSES PASS,
                 trunc(TEMPSEG_SIZE/1024) TSIZE
            FROM V$SQL_WORKAREA_ACTIVE
           ORDER BY 1,2;
          
          The output of this query might look like the following:
          SID         OPERATION     ESIZE       MEM   MAX MEM  PASS   TSIZE
          --- ----------------- --------- --------- --------- ----- -------
            8   GROUP BY (SORT)       315       280       904     0
            8         HASH-JOIN      2995      2377      2430     1   20000
            9   GROUP BY (SORT)     34300     22688     22688     0
           11         HASH-JOIN     18044     54482     54482     0
           12         HASH-JOIN     18044     11406     21406     1  120000
          • 2. Re: V$PGASTAT question about PGA used vs allocated
            614789
            Thanks,

            My average rowlen is about 200 but of course not all the columns are being selected. I was just thinking that it might be using more but may be the V$PGASTAT wasn't showing me the right values and wanted to double check. Also I checked the documentation on V$SQL_WORKAREA_ACTIVE and its says the columns are in Kb's but that seems to be too high, expecially since you are doing a divide by 1024? Also most of my "pass" values are greater than 0, hence I guess is not optimal and need to tune my PGA
            SQL> SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
              2         operation_type OPERATION,
              3         trunc(EXPECTED_SIZE/1024) ESIZE,
              4         trunc(ACTUAL_MEM_USED/1024) MEM,
              5         trunc(MAX_MEM_USED/1024) "MAX MEM",
              6         NUMBER_PASSES PASS,
              7         trunc(TEMPSEG_SIZE/1024) TSIZE
              8    FROM V$SQL_WORKAREA_ACTIVE
              9   ORDER BY 1,2;

               SID OPERATION            ESIZE        MEM    MAX MEM       PASS      TSIZE
            ------ --------------- ---------- ---------- ---------- ---------- ----------
               212 HASH-JOIN           700001     927267     927267          1    4133888
               212 HASH-JOIN           266873     108407     151972          3   43650048
               212 HASH-JOIN             2305       1403       1403          0
               212 HASH-JOIN           478532     581954     581954          1    5714944
               212 HASH-JOIN          1047234    1444730    1444730          1    1064960
               212 SORT (v2)           268466     242656     276794          1   26169344

            6 rows selected.
            • 3. Re: V$PGASTAT question about PGA used vs allocated
              orafad
              Oracle Server version?

              The bound "statistic", of 1024MB (or 1073741824 bytes), might have some relevance here as it guides the max size per auto work area.

              If this is a test setup, perhaps you could try to increase pga_a_t even more (hence increasing the global bound).

              Edited by: orafad on Jan 7, 2009 12:27 AM
              • 5. Re: V$PGASTAT question about PGA used vs allocated
                247514
                Can you post the result of this query from your system?

                It seems normal that bigger sized work area buckets has onepass execution.
                SQL>SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
                       optimal_executions, onepass_executions, multipasses_executions
                FROM   v$sql_workarea_histogram
                WHERE  total_executions != 0;
                  2    3    4  
                    LOW_KB    HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
                ---------- ---------- ------------------ ------------------ ----------------------
                         2          4          147464860                  0                      0
                        64        128              77382                  0                      0
                       128        256              39709                  0                      0
                       256        512              61619                  0                      0
                       512       1024            3152658                  0                      0
                      1024       2048             258814                  2                      0
                      2048       4096             113562               3124                      0
                      4096       8192              27361               7260                      0
                      8192      16384              45133               3616                      0
                     16384      32768              19949               2100                      0
                     32768      65536               6854                997                      0
                     65536     131072               1502                551                      0
                    131072     262144                  3                393                      0
                    262144     524288                  2                356                      0
                    524288    1048576                  3                163                      0
                   1048576    2097152                  0                123                      0
                   2097152    4194304                  0                 78                      0
                • 6. Re: V$PGASTAT question about PGA used vs allocated
                  614789
                  SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
                         optimal_executions, onepass_executions, multipasses_executions
                  FROM   v$sql_workarea_histogram
                  WHERE  total_executions != 0;
                    2    3    4
                      LOW_KB    HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
                  ---------- ---------- ------------------ ------------------ ----------------------
                           2          4            1341699                  0                      0
                          64        128                424                  0                      0
                         128        256                523                  0                      0
                         256        512                301                  0                      0
                         512       1024              33875                  0                      0
                        1024       2048              13534                  0                      0
                        2048       4096                  9                  0                      0
                        4096       8192                  4                  0                      0
                        8192      16384                  1                  0                      0
                      131072     262144                  1                  0                      0
                      262144     524288                  2                  0                      0
                     1048576    2097152                  0                  4                      1
                     2097152    4194304                  0                  2                      2
                     8388608   16777216                  0                  1                      0
                    33554432   67108864                  0                  1                      0

                  15 rows selected.
                  • 7. Re: V$PGASTAT question about PGA used vs allocated
                    247514
                    maximum PGA allocated 3648694272 bytes
                    8388608 16777216 0 1 0
                    33554432 67108864 0 1 0
                    That's weird, you had two very big workarea bucket 8-16G and 16-32G but your max PGA allocated only 3G or so.
                    Are you sure the result is from same period where no instance restart in between?
                    • 8. Re: V$PGASTAT question about PGA used vs allocated
                      orafad
                      How do you see that's wierd? Those "bucket" sizes are not saying there's an actual work area of that size, which I think 0's in the optimal column agrees with.
                      Also note how optimal runs ends at the global mem bound size.

                      Shouldn't we be looking at temp segment usage in connection with the >1 gigabyte passes?

                      Edited by: orafad on Jan 7, 2009 2:37 AM
                      • 9. Re: V$PGASTAT question about PGA used vs allocated
                        orafad
                        Perhaps watching V$SQL_WORKAREA_ACTIVE during the run of those huge queries might add some information.
                        • 10. Re: V$PGASTAT question about PGA used vs allocated
                          247514
                          orafad wrote:
                          How do you see that's wierd? Those "bucket" sizes are not saying there's an actual work area of that size, which I think 0's in the optimal column agrees with.
                          Also note how optimal runs ends at the global mem bound size.

                          Shouldn't we be looking at temp segment usage in connection with the >1 gigabyte passes?

                          Edited by: orafad on Jan 7, 2009 2:37 AM
                          The query used will filter out all empty bucket which has total execution 0. These bucket has 0 optimal execution but they has one pass execution. Therefore we will know there's work area bucket that sized over 8G and 16G been allocated and used for execution which is contradict to max PGA allocated statistics.
                          • 11. Re: V$PGASTAT question about PGA used vs allocated
                            614789
                            I'm not sure how it can go up so much more than the pga_aggregate_target. There is another DB where the pga_aggregate_target is set to 1G and query with about 50M result set and here are the details
                            SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
                                   optimal_executions, onepass_executions, multipasses_executions
                            FROM   v$sql_workarea_histogram
                            WHERE  total_executions != 0;
                              2    3    4
                                LOW_KB    HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
                            ---------- ---------- ------------------ ------------------ ----------------------
                                     2          4             990269                  0                      0
                                    64        128                331                  0                      0
                                   128        256                144                  0                      0
                                   256        512                 71                  0                      0
                                   512       1024               5309                  0                      0
                                  1024       2048               1130                  0                      0
                                131072     262144                  0                  2                      0
                                262144     524288                  0                  5                      0
                               1048576    2097152                  0                  1                      2
                               8388608   16777216                  0                  2                      0

                            10 rows selected.
                            • 12. Re: V$PGASTAT question about PGA used vs allocated
                              614789
                              Also going back to the original question about "total PGA inuse" from the V$PGASTAT, the entire 500Million result set does have an order by and the average row size is about 250 Bytes. I'm not sure why I never see this more than 4G. Does the space used for sorts, hash-joins get included in these rows from V$PGASTAT?
                              • 13. Re: V$PGASTAT question about PGA used vs allocated
                                137669
                                Hi,

                                There are several hidden parameters which define the maximum size of pga used by a query.
                                To find out the upper limit of PGA memory for one serial process/query you should have a look at the hidden parameter pgamax_size (in byte). To find out the upper limit of PGA memory for one single workarea of a query you should have a look at the hidden parameter smmmax_size (in Kbyte !).

                                SELECT
                                substr(a.ksppinm,1,30) parameter,
                                to_number(c.ksppstvl) value,
                                substr(a.ksppdesc,1,60) description
                                FROM x$ksppi a,x$ksppsv c
                                WHERE a.indx = c.indx AND a.ksppinm in
                                ('pga_aggregate_target', '_pga_max_size', '_smm_max_size','_smm_px_max_size');

                                From your output of V$SQL_WORKAREA_ACTIVE it seems to me that your are executing a serial query. To improve the performance and to use more pga I would recommend you to execute it in parallel (degree 4 or 8).

                                Regards
                                Maurice
                                • 14. Re: V$PGASTAT question about PGA used vs allocated
                                  137669
                                  A really interesting document related to this subject is "ADVANCED MANAGEMENT OF WORKING AREAS IN ORACLE 9I/10G" from Joze Senegacnik.
                                  http://tonguc.yilmaz.googlepages.com/JozeSenegacnik-PGAMemoryManagementvO.zip

                                  You may also have a look at:
                                  Temporary tablespace performance tuning

                                  Regards
                                  Maurice
                                  1 2 Previous Next