Home » RDBMS Server » Performance Tuning » SGA AND PGA TUNING (Oracle 11.2)
SGA AND PGA TUNING [message #659087] Thu, 05 January 2017 20:27 Go to next message
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 #659088 is a reply to message #659087] Thu, 05 January 2017 21:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>SELECT * FROM v$memory_target_advice ORDER BY memory_size;
shows nothing will be gained by changing SGA size.

Go find more productive activity to spend your time doing.
Re: SGA AND PGA TUNING [message #659089 is a reply to message #659088] Thu, 05 January 2017 21:33 Go to previous messageGo to next message
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 #659090 is a reply to message #659089] Thu, 05 January 2017 21:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
arin.oradba wrote on Thu, 05 January 2017 19:33
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.
Does new DB have the same parameters as in Production now?

post FORMATTED results from SQL below from Production DB

SELECT * FROM v$memory_target_advice ORDER BY memory_size;

post SQL & results that show some problem that needs to be improved or corrected.
Re: SGA AND PGA TUNING [message #659094 is a reply to message #659090] Fri, 06 January 2017 01:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #659097 is a reply to message #659096] Fri, 06 January 2017 01:50 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I'd be using huge pages if I were you.
Re: SGA AND PGA TUNING [message #659098 is a reply to message #659097] Fri, 06 January 2017 02:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #659100 is a reply to message #659099] Fri, 06 January 2017 02:49 Go to previous messageGo to next message
arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata
Member
Thanks for your reply.

But it is not fine at all the time...

Lots of complex query are running through application
Lots of Materialized views are there and refreshed at night job for report purpose.
Night jobs are taken much time.
Application will go slow if any heavy sql is firing during office time.

Re: SGA AND PGA TUNING [message #659101 is a reply to message #659100] Fri, 06 January 2017 03:17 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So identify and tune the problem statements. There is no evidence of anything wrong with your memory allocations.
Re: SGA AND PGA TUNING [message #659107 is a reply to message #659101] Fri, 06 January 2017 04:08 Go to previous messageGo to next message
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 #659140 is a reply to message #659107] Sat, 07 January 2017 10:47 Go to previous messageGo to next message
arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata
Member
At the very first, I want to thanks you very much for your such wonderful analyzed post.

Once again I am analyze in sequence -
My problem:
Lots of complex query are running through application 
Lots of Materialized views are there and refreshed at night job for report purpose.
Night jobs are taken much time.
Application will go slow if any heavy sql is firing during office time.
Users are getting slow sometimes in the day.

currently we use 12 core cpu and 12 processors along with RAM (MemTotal:126 GB) -- Not a physical machine.

tmpfs size in Linux:
Filesystem                        Size  Used Avail Use% Mounted on
tmpfs                              63G   25G   39G  40% /dev/shm


SGA parameters:
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
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

Dynamically given by oracle:
*.__large_pool_size=268435456---------256 MB
*.__pga_aggregate_target=7247757312---6.75G
*.__sga_target=26843545600------------ 25G
*.__shared_io_pool_size=0
*.__shared_pool_size=6777995264-------6.3125 G
*.__streams_pool_size=67108864--------64 MB


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


Now set the question for me as belows:-
a) Buffer cache allocation for maximum performance
Ans-Required big size to accomodate for OLTP transaction and report purpose huge data.

b) shared/java pool size determination:-
Ans- Lot of sql/plsql are executing in the database throughout the day and night.so shared pool size should be bigger than allocated through oracle.

c) We are using dedicated server or shared server?
Ans- It is dedicated server. So Leave 56% RAM for OS memory for dynamic allocations that is 180GB

d) Only one oracle database is running here or not?
Ans - Yes only one.



Now In new database:

currently we will use physical server 8 core cpu and 32 processors along with RAM (MemTotal: 320 GB)

Here tmpfs size is 157 G.
Filesystem     1K-blocks     Used Available Use% Mounted on
tmpfs          165249092 25439544 139809548  16% /dev/shm

So we can use at least 140 GB for oracle.

Now first keep a backup of existing pfile and spfile.
Then I set set the following parameterlike below-

sga_max_size------------110 GB
sga_target-------------- 50 GB
pga_aggregate_target---- 30 GB

So in future we can adjust sga_target as per requirement (increase or decrease) without bouncing the database.

Later I have a plan to use manual settings and set keep_buffer_cache after deciding the small frequently used static table.

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                      0
memory_target                        big integer                      0
parallel_servers_target              integer                          512
pga_aggregate_target                 big integer                      30G
sga_target                           big integer                      50G
SQL>

Details components :

COMPONENT                      CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------------ ------------ ---------- ----------
shared pool                      7516192768 7516192768 7516192768
large pool                       1073741824 1073741824 1073741824
java pool                        1879048192 1879048192 1879048192
SGA Target                       5.3687E+10 5.3687E+10 6.4425E+10
DEFAULT buffer cache             4.2413E+10 4.2413E+10 5.3150E+10
PGA Target                       3.2212E+10 3.2212E+10 3.2212E+10

Re: SGA AND PGA TUNING [message #659142 is a reply to message #659140] Sat, 07 January 2017 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
IMO, you suffer from Compulsive Tuning Disorder.

When response is slow, which system resource (CPU, RAM, or I/O) is the primary bottleneck causing the slowness?
Re: SGA AND PGA TUNING [message #659143 is a reply to message #659142] Sat, 07 January 2017 12:11 Go to previous messageGo to next message
arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata
Member
I 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...
Re: SGA AND PGA TUNING [message #659145 is a reply to message #659143] Sat, 07 January 2017 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
arin.oradba wrote on Sat, 07 January 2017 10:11
I 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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #659198 is a reply to message #659150] Mon, 09 January 2017 06:25 Go to previous messageGo to next message
arin.oradba
Messages: 33
Registered: January 2016
Location: Kolkata
Member
Thank you all of you to share your opinions that a good experience for me.
Whatever I have fixed regarding database tuning ,it is not a one calls.
I have worked in this database long time, did many query tuning etc. and observed the behavior.

Here we have to handle night job as well as day OLTP transactions.
In night, redo log switch occurs within a minute and opposite at day time.

So that earlier we had increase redo log group but not redo log size.
But now i will again monitor and if require then we will do manual log switch schedule at 15-20 minute interval at day time.

I have not added any ARCH process yet.

@John,
Thanks for your advice. But I have a question for clarification.

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

Re: SGA AND PGA TUNING [message #659199 is a reply to message #659198] Mon, 09 January 2017 06:45 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #659202 is a reply to message #659198] Mon, 09 January 2017 08:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What criteria must be met for you to conclude that your database is sufficiently tuned?
Re: SGA AND PGA TUNING [message #659333 is a reply to message #659202] Fri, 13 January 2017 01:57 Go to previous message
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.


Previous Topic: Need your help to optimize the Query
Next Topic: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals)
Goto Forum:
  


Current Time: Fri Mar 29 04:59:13 CDT 2024