SGA AND PGA TUNING [message #659087] |
Thu, 05 January 2017 20:27 |
|
arin.oradba
Messages: 33 Registered: January 2016 Location: Kolkata
|
Member |
|
|
I have a new OLTP Midium database .DB size -800 GB, RAM size -320 GB .I want to increase sga size to 120 GB.
Please advice AMM use or not?
Hugepage use or not?
Only Automatic Shared Memory and Automatic PGA Memory will use?How to determine the appropriate value?
Here I am giving some information about the server:
TEMPFS DETAILS:
[oracle]$ df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
tmpfs 165249092 25439544 139809548 16% /dev/shm
Existing SGA value:
SQL> show parameter target
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 32512M
memory_target big integer 32512M
parallel_servers_target integer 512
pga_aggregate_target big integer 0
sga_target big integer 25G
SQL>
PGA DETAILS:
[oracle]$ sqlplus / as sysdba
SQL> COLUMN name FORMAT A30
SQL> COLUMN value FORMAT A10
SQL>
SELECT name, value FROM v$parameter
SQL> 2 WHERE name IN ('pga_aggregate_target', 'sga_target')
3 UNION
4 SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value
5 FROM v$pgastat WHERE name = 'maximum PGA allocated';
NAME VALUE
-------------------------- ----------
maximum PGA allocated 4984132608
pga_aggregate_target 0
sga_target 2684354560
0
MEMORY_TARGET CALCULATION:
SQL> SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target
2 FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga,
3 (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga,
4 (SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;
MEMORY_TARGET
-------------
3.1828E+10
CURRENT SIZE OF SGA COMPONENTS:
SQL> COLUMN component FORMAT A30
SELECT component, current_size, min_size, max_size
FROM v$memory_dynamic_components
WHERE current_size != 0;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
------------------------------ ------------ ---------- ----------
shared pool 4294967296 2617245696 4294967296
large pool 536870912 536870912 872415232
java pool 469762048 469762048 469762048
streams pool 67108864 0 134217728
SGA Target 2.6844E+10 2.6844E+10 2.6844E+10
DEFAULT buffer cache 2.1274E+10 2.1274E+10 2.3018E+10
PGA Target 7247757312 7247757312 7247757312
7 rows selected.
QUERY MEMORY_TARGET_ADVICE:
SQL> SELECT * FROM v$memory_target_advice ORDER BY memory_size;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
8128 .25 804619 1.0461 0
16256 .5 769161 1 0
20320 .625 769161 1 0
24384 .75 769161 1 0
28448 .875 769161 1 0
32512 1 769161 1 0
36576 1.125 769161 1 0
40640 1.25 769161 1 0
44704 1.375 769161 1 0
48768 1.5 769161 1 0
52832 1.625 769161 1 0
56896 1.75 769161 1 0
60960 1.875 769161 1 0
65024 2 769161 1 0
14 rows selected.
QUERY ON LINUX:
[oracle]$ grep PageTables /proc/meminfo
PageTables: 379560 kB
HUGEPAGE:
[oracle]$ grep Huge /proc/meminfo
AnonHugePages: 104448 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
I am requesting all of you to please help me on the above topic.
*BlackSwan added {code} tags. please do so yourself in the future.
How to use {code} tags and make your code easier to read
[Updated on: Thu, 05 January 2017 21:15] by Moderator Report message to a moderator
|
|
|
|
Re: SGA AND PGA TUNING [message #659089 is a reply to message #659088] |
Thu, 05 January 2017 21:33 |
|
arin.oradba
Messages: 33 Registered: January 2016 Location: Kolkata
|
Member |
|
|
Thank you BlackSwan for you quick response.
It shows nothing because it is not live till now.It is a replica of our existing production server with more powerful than the existing server.
Please share some right direction to investigate more details if possible.
[Updated on: Thu, 05 January 2017 21:39] Report message to a moderator
|
|
|
|
Re: SGA AND PGA TUNING [message #659094 is a reply to message #659090] |
Fri, 06 January 2017 01:29 |
|
arin.oradba
Messages: 33 Registered: January 2016 Location: Kolkata
|
Member |
|
|
My questions are -
Whether we will use AMM or memory_max_target=0 and memory_target=0 and go with Automatic Shared Memory and Automatic PGA Memory ?
How to determine the appropriate value?
whether we will use Hugepage or not?
I want to utilize 320 GB RAM properly or keep provision for future , so that need not to bounce the database after rearrange the SGA and PGA parameter's value in future.
I am sharing the following from existing prod.
SQL> select * from v$sga_target_advice order by sga_size;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
9600 .375 49368307 7.5771 2.2441E+10
12800 .5 24806318 3.8073 1.0311E+10
16000 .625 12673877 1.9452 4319656427
19200 .75 8259651 1.2677 2139634542
22400 .875 6907041 1.0601 1471709680
25600 1 6515462 1 1278080486
28800 1.125 6432716 .9873 1238587799
32000 1.25 6348015 .9743 1196922375
35200 1.375 6303058 .9674 1173916926
38400 1.5 6262662 .9612 1153978871
41600 1.625 6231388 .9564 1130845614
44800 1.75 6227480 .9558 1130845614
48000 1.875 6226828 .9557 1130845614
51200 2 6226828 .9557 1130845614
14 rows selected.
SQL> SELECT * FROM v$memory_target_advice ORDER BY memory_size;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
16256 .5 6530705 1.0048 0
20320 .625 6506007 1.001 0
24384 .75 6501457 1.0003 0
28448 .875 6499507 1 0
32512 1 6499507 1 0
36576 1.125 6499507 1 0
40640 1.25 6499507 1 0
44704 1.375 6499507 1 0
48768 1.5 6499507 1 0
52832 1.625 6499507 1 0
56896 1.75 6499507 1 0
60960 1.875 6499507 1 0
65024 2 6499507 1 0
13 rows selected.
SQL>select pga_target_for_estimate, pga_target_factor, estd_time from v$pga_target_advice;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ESTD_TIME
905969664 .125 72835098501
1811939328 .25 45959047651
3623878656 .5 43992893763
5435817984 .75 43851583487
7247757312 1 43712200208
8697308160 1.2 43549783878
10146860032 1.4 43543291777
11596410880 1.6 43543291777
13045962752 1.8 43543291777
14495514624 2 43543291777
21743271936 3 43543291777
28991029248 4 43543291777
43486543872 6 43543291777
57982058496 8 43543291777
-- ************************************************
-- Display pga target advice
-- ************************************************
column c1 heading 'Target(M)'
column c2 heading 'Estimated|Cache Hit %'
column c3 heading 'Estimated|Over-Alloc.'
SELECT
ROUND(pga_target_for_estimate /(1024*1024)) c1,
estd_pga_cache_hit_percentage c2,
estd_overalloc_count c3
FROM
v$pga_target_advice;
Estimated Estimated
Target(M) Cache Hit % Over-Alloc.
---------- ----------- -----------
864 60 40016
1728 94 281
3456 98 0
5184 99 0
6912 99 0
8294 99 0
9677 99 0
11059 99 0
12442 99 0
13824 99 0
20736 99 0
27648 99 0
41472 99 0
55296 99 0
14 rows selected.
-- ************************************************
-- Display pga target advice histogram
-- ************************************************
SELECT
low_optimal_size/1024 "Low(K)",
(high_optimal_size+1)/1024 "High(K)",
estd_optimal_executions "Optimal",
estd_onepass_executions "One Pass",
estd_multipasses_executions "Multi-Pass"
FROM v$pga_target_advice_histogram
WHERE pga_target_factor = 2
AND estd_total_executions != 0
ORDER BY 1;
Low(K) High(K) Optimal One Pass Multi-Pass
---------- ---------- ---------- ---------- ----------
2 4 237457218 0 0
64 128 79502 0 0
128 256 55822 0 0
256 512 68469 0 0
512 1024 6809902 0 0
1024 2048 3460314 0 0
2048 4096 8281686 0 0
4096 8192 27096 0 0
8192 16384 123758 0 0
16384 32768 981466 0 0
32768 65536 17564 0 0
65536 131072 194521 4 0
131072 262144 6961 205 0
262144 524288 1636 57 0
524288 1048576 717 194 0
1048576 2097152 263 62 0
2097152 4194304 49 5 0
8388608 16777216 10 7 0
18 rows selected.
[Updated on: Fri, 06 January 2017 01:36] Report message to a moderator
|
|
|
Re: SGA AND PGA TUNING [message #659096 is a reply to message #659094] |
Fri, 06 January 2017 01:38 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
It looks as though your memory usage is perfectly configured as it is. I would not change anything. Just run your application on the new machine and see how it goes.
|
|
|
|
Re: SGA AND PGA TUNING [message #659098 is a reply to message #659097] |
Fri, 06 January 2017 02:16 |
|
arin.oradba
Messages: 33 Registered: January 2016 Location: Kolkata
|
Member |
|
|
Thank you John and Roachcoach for your kind reply..
But pardon me, kindly tell me in details about using hugepages.
Actually I want to keep provision fro future.
So whether we will go with EXISTING VALUE or replace it with proposed value.
SQL> show parameter target
EXISTING VALUE:
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
memory_max_target big integer 32512M
memory_target big integer 32512M
pga_aggregate_target big integer 0
sga_target big integer 25G
OR
PROPPOSE VALUE:
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
memory_max_target big integer 0GB
memory_target big integer 0GB
pga_aggregate_target big integer 30G
sga_target big integer 80G
sga_max_target big integer 110G
OR
Whether I can follow the below calculation?
SGA=40% of RAM(320 G) = 40% of 300 GB= 120 G
PGA=40% of SGA = 40% of 120 GB= 48 G
But please note that the following parameter's value is - 157G
[oracle]$ df -k /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
tmpfs 165249092 25439544 139809548 16% /dev/shm
[Updated on: Fri, 06 January 2017 02:40] Report message to a moderator
|
|
|
Re: SGA AND PGA TUNING [message #659099 is a reply to message #659098] |
Fri, 06 January 2017 02:21 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Why do you want to fiddle around with something that is working fine? Why take any risk? Your clients will not thank you of anything goes wrong.
|
|
|
|
|
Re: SGA AND PGA TUNING [message #659107 is a reply to message #659101] |
Fri, 06 January 2017 04:08 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
You need allocate sufficient huge pages so that the database can fit the entire SGA in there [plus a little more for wiggle room]. If you don't do this, with larger SGAs and connection numbers you can very quickly run into serious problems around paging/swapping as the OS tries (and to all effective purposes fails) to manage the memory.
However these are guidelines, yours and anyone else landing here from google will have their own unique situation so my advice is to test this in your environment and ensure your configuration works for you. There is no one size fits all, you have to liaise with the OS admins to set these parameters as well.
Finally, AMM is incompatible with hugepages so I'd say that answers that from my point of view.
But as I say, each place is different. From your last post it sounds like you have a set off issues and are throwing memory at the database in the hope it resolves it. It may help, in fact it almost always has to a certain degree in my experience, however will it help enough to keep and maintain performance over the SLAs? Doubtful and it'll be blind luck if it does without having done any analysis on the root cause of the issues.
If things are slow, you have to work out what they are waiting on and assess your options available to move that bottleneck around to a faster (or as is often the case, less contended) component.
[Updated on: Fri, 06 January 2017 04:12] Report message to a moderator
|
|
|
|
|
|
Re: SGA AND PGA TUNING [message #659145 is a reply to message #659143] |
Sat, 07 January 2017 12:32 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
arin.oradba wrote on Sat, 07 January 2017 10:11I will requesting everyone to share your views on my analysis..
If anything goes wrong, I will change previous spfile and pfile again...
@Dear BlackSwan
When response is slow, CPU, I/O waiting is their.
log file sync,db file scattered read,write complete waits are also showing in AWR.
log file sync - change redo log file size from 200M to 500M and add one archive process
db file scattered read - Need to tune the code and later I will try to cache the small static frequently used table
write complete waits - It is just showing one time, I will monitor it. If happens frequently ,then add one DBWR and configure to checkpoint more often
Please share your views in details.
I am requesting all of you to help me to get some knowledge...
Everything above that you listed indicates that bottleneck is I/O.
Your obsession & fixation on SGA & RAM will have ZERO impact on improving I/O performance.
The same applies by moving to a system with more CPU power.
It is similar to increasing the size of the gas tank & hoping the car will go faster.
How will increasing REDO log file size improve I/O performance?
The amount of REDO that needs to be archive remains the same.
It is similar to saying that you can eat a pizza cut into 4 pieces faster than you can eat the same sized pizza cut into 8 slices;
because there are fewer slices.
Since you ignore were the bottleneck exists, you are wasting time & money making unneeded changes impacting other resources.
|
|
|
Re: SGA AND PGA TUNING [message #659150 is a reply to message #659143] |
Sat, 07 January 2017 14:20 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Arin, you are fixated on what one calls "database tuning". Memory, te redo log, simple stuff like that. Database tuning is easy. I can tune your database in ten minutes. SQL tuning is hard work, and that is what you need to do. You need to start by identifying the problem statements.
One point - do not use a keep pool. It was useful back in the last century, but in recent releases Uncle Oracle has improved the buffer cache management algorithms hugely and (in my opinion) interfering with them by creating a keep pool is more likely to cause problems than to solve them.
|
|
|
|
Re: SGA AND PGA TUNING [message #659199 is a reply to message #659198] |
Mon, 09 January 2017 06:45 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:In oracle performance tuning book and many google resource are saying that still manual SGA settings is better that Automatic SGA settings and many mission critical databases still follow the manual settings.
Please share your view on this... It is not easy to "share your view" on a book that one has not seen. You have not even given its name. Please note that I have not once suggested that manual configuration would be either better or worse for your database. I have said that there is no indication of any problem with your memory configuration.
What I have also said is that if you have a performance problem, you need to tune the code. Why are you always ignoring this suggestion? If you disagree, or are incompetent, you should say say so.
|
|
|
|
Re: SGA AND PGA TUNING [message #659333 is a reply to message #659202] |
Fri, 13 January 2017 01:57 |
|
arin.oradba
Messages: 33 Registered: January 2016 Location: Kolkata
|
Member |
|
|
Dear All, Thank you all of you to participate on this topic that help me to get a good experience and knowledge through all these discussions.
After changing the server and tuned as described earlier, we got a 40% - 50% performance improvement.
Now Cpu wait, commit,user I/O or system I/O are very much lower than before as described below.
Cpu Wait was 15569 but now 8367.
User I/O for db file sequential read was 2552 but now 356.
System I/O for log file parallel write was 1521 but now 287.
Commit for log file sync was 12014 but now 531.
AVG LOG SWITCHS PER HOUR WAS 7.8-8.5, NOW IT IS 4.5-6.
Specially at night, schedule jobs are much more faster than before. LOG SWITCHS PER HOUR at that time are also improve a lot.
Now I will concentrate SQL tuning and huge pages implement.
Thanks again all of you for your support.
|
|
|