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