-
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
-
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.
-
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
-
========================
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
-
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 05:03 AM.
-
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 07:40 AM.
-
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;
-
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
-
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 08:43 AM.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|