PGA /SGA size for a DWH DB
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: PGA /SGA size for a DWH DB

  1. #1
    Join Date
    Mar 2002
    Posts
    534

    PGA /SGA size for a DWH DB

    Hi,

    I'm working on a DWH project and was wondering which size you would recommend for the PGA and SGA.

    We are using a Sun Solaris server with 20 CPUs and 40 Gb RAM. 99% of the queries get the data from the disk. Most queries have to do some sorts/hash on disk.

    Right now 80% (32 Gb) from the memory is asined to the DB instance. Around 16 Gb to the SGA and 16Gb to the PGA. Is it ok like that? Wouldn't it probably be better to increase the PGA size to something like 22Gb and reduce the SGA to 10 Gb? What kind of settings do you have on your DWH DB?

    Thanks for any input
    Mike

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Those look like reasonable starting points, and I would base any decision to modify those numbers purely on the databases own cache advice.

    Monitor the cache advice and work out (a spreadsheet helps) what split would give you the lowest combined i/o load.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Just wondering... what is your db cache? I used to work for a DWH which had 900mb db cache, the perfomance wasnt too bad. I am wondering because recently I discovered many times that full table scan does physical reads no matter how big is you db cache

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    ========================
    Right now 80% (32 Gb) from the memory is asined to the DB instance. Around 16 Gb to the SGA and 16Gb to the PGA. Is it ok like that? Wouldn't it probably be better to increase the PGA size to something like 22Gb and reduce the SGA to 10 Gb? What kind of settings do you have on your DWH DB?

    ======================

    I would start with 20GB for SGA and 10GB for PGA_AGGREGATE_TARGET and watch closely cache utilization.

    What is the DB Size?

    Tamil

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    Hi everybody,

    First of all thanks for your feedback.

    Pando,
    The DB Cache is set to 8GB. The problem is that, because of a bad design, we have some large tables 2-3 GB which are often accessed vai fts. Also, same as you, I always see that fts does physical reads. So is there any benefit of setting the cache so high?

    tamilselvan,
    The DB size is about 1.2 TB (data size, indexes and mviews are not included). But 95% of the queries access about 300 GB of data.

    Regards
    Mike
    Last edited by mike9; 02-16-2004 at 04:03 AM.

  6. #6
    Join Date
    Mar 2002
    Posts
    534

    V$PGASTAT: extra bytes read/written

    I executed the following query to check how pga is used:

    Code:
    SELECT 
    name, 
    decode(unit, 'bytes', trunc(value/1024/1024), value) value ,  
    decode(unit, 'bytes', 'MBytes', unit) unit  
    FROM V$PGASTAT;
    
    NAME                                  VALUE UNIT
    -------------------------------- ---------- ------------
    aggregate PGA target parameter        16384 MBytes
    aggregate PGA auto target             14687 MBytes
    global memory bound                     100 MBytes
    total PGA inuse                         393 MBytes
    total PGA allocated                    2585 MBytes
    maximum PGA allocated                 12942 MBytes
    total freeable PGA memory              2087 MBytes
    PGA memory freed back to OS          188854 MBytes
    total PGA used for auto workarea        331 MBytes
    maximum PGA used for auto workar       7376 MBytes
    total PGA used for manual workar          0 MBytes
    maximum PGA used for manual work         22 MBytes
    over allocation count                     0
    bytes processed                     5769077 MBytes
    extra bytes read/written            5638302 MBytes
    cache hit percentage                  50,57 percent
    What I'm concerned about is that "extra bytes read/written" is so large. Is it correct that the meaning of this value is that since the last startup of the DB 5TB has been written and read again from disk because there was no enough memory available for the PGA? The DB instance being up since the 1st of February, it would mean that with in 15 days 5TB of IO would have been generated because of the PGA?

    Would be gratefull if any of the Oracle gurus could confirm or correct my understanding.

    Thanks a lot
    Mike
    Last edited by mike9; 02-16-2004 at 06:40 AM.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    run

    Code:
    select case 
           when low_optimal_size < 1024*1024
           then to_char(low_optimal_size/1024,'999999') || 'kb <= PGA < ' ||
                (HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
           else to_char(low_optimal_size/1024/1024,'999999') || 'mb <= PGA < ' ||
                (high_optimal_size+1)/1024/1024|| 'mb'
           end pga_size,
           optimal_executions,
           onepass_executions,
           multipasses_executions
      from v$sql_workarea_histogram
     where total_executions <> 0
    order by low_optimal_size;

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    Hi Pando,

    Here the result of the query

    Code:
    PGA_SIZE                                                      OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
    ------------------------------------------------------------- ------------------ ------------------ ----------------------
         16kb <= PGA < 32kb                                                  3283545                  0                      0
         32kb <= PGA < 64kb                                                   223778                  0                      0
         64kb <= PGA < 128kb                                                   36257                  0                      0
        128kb <= PGA < 256kb                                                   20468                  0                      0
        256kb <= PGA < 512kb                                                  110417                  4                      0
        512kb <= PGA < 1024kb                                                 168354                 10                      0
          1mb <= PGA < 2mb                                                     45312                152                      0
          2mb <= PGA < 4mb                                                     20427                  4                      0
          4mb <= PGA < 8mb                                                     14908                 40                      0
          8mb <= PGA < 16mb                                                    11010                 77                      0
         16mb <= PGA < 32mb                                                     9118                199                      0
         32mb <= PGA < 64mb                                                     6047               1013                      0
         64mb <= PGA < 128mb                                                    2289               1386                     18
        128mb <= PGA < 256mb                                                     216               2381                     75
        256mb <= PGA < 512mb                                                     501               1285                    260
        512mb <= PGA < 1024mb                                                     42                648                    180
       1024mb <= PGA < 2048mb                                                     98                975                    188
       2048mb <= PGA < 4096mb                                                      8                 27                     16
       4096mb <= PGA < 8192mb                                                      0                 16                      0
       8192mb <= PGA < 16384mb                                                    13                 39                      0

  9. #9
    Join Date
    Mar 2002
    Posts
    534
    Pando,

    I think I understand the meanding of the result of your query.

    When I take the average PGA size (low_optimal_size+high_optimal_size/2) and multiply it by the number of ONEPASS_EXECUTIONS I got 3.7 TB. For the MULTIPASSES_EXECUTIONS I do the same but multiply the result by 2 (as I undestood with MULTIPASS Oracle will write at least 2 times the PGA to disk) and got 1.1 TB. And when I added the 2 result I got something close to the 5TB. So if I got it correctly, that means that the DB realy wrote 5TB, within 15 days, to disk because of the PGA. That would also explain why the server has often over 50% IOWaits.

    Regards
    Mike
    Last edited by mike9; 02-16-2004 at 07:43 AM.

  10. #10
    Join Date
    Mar 2002
    Posts
    534
    I got it.

    The main reason for the problem is the follwing:

    Code:
    PGA_AGGREGATE_TARGET limits both the global PGA consumption and the
    size of a  workarea i.e. the memory allocated to a single SQL 
    operator is also limited  to min(5% PGA_AGGREGATE_TARGET, 100MB) 
    for serial operations, and to  30% PGA_AGGREGATE_TARGET/DOP for 
    parallel operations (DOP=Degree of Parallelism).
    http://metalink.oracle.com/metalink/...&p_id=147806.1

    So all serial queries have a maximum of 100 mb PGA !!!!
    Great for a DWH.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width