PGA /SGA size for a DWH DB

[mike9]

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

[slimdave]

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.

[pando]

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

[tamilselvan]

========================
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

[mike9]

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

[mike9]

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

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

[pando]

run

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;

[mike9]

Hi Pando,

Here the result of the query

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

[mike9]

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

[mike9]

I got it.

The main reason for the problem is the follwing:

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.