Home » RDBMS Server » Server Administration » Database Performance vs SGA size (LINUX 5.3 Oracle 11gr2)
Database Performance vs SGA size [message #625818] Tue, 14 October 2014 07:49 Go to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Hi All,

We are facing database performance issue in our live environment.
Our total server memory is 32GB on each node.
SGA size is configured as 12GB.

Still database performance is not as per expected behaviour. Application vendor is suggesting to increase SGA size to 16GB.

I need to know how to identify that there is a need to increase SGA size ?

Secondly, please let us know what is the relation between server memory size and SGA memory size.

Your help in this regard will be much appreciated.
Re: Database Performance vs SGA size [message #625823 is a reply to message #625818] Tue, 14 October 2014 08:02 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
post results from SQL below


SELECT * FROM V_$SGA_TARGET_ADVICE ;

>Secondly, please let us know what is the relation between server memory size and SGA memory size.
SGA < RAM
Re: Database Performance vs SGA size [message #625826 is a reply to message #625823] Tue, 14 October 2014 08:36 Go to previous messageGo to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Please find below the result requested....

SQL> SELECT * FROM V_$SGA_TARGET_ADVICE;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
      3216            .375        25438             25.4635              282738
      4288              .5         1035               1.036              282738
      5360            .625          999                   1              254995
      6432             .75          999                   1              254995
      7504            .875          999                   1              254995
      8576               1          999                   1              254995
      9648           1.125          999                   1              254995
     10720            1.25          999                   1              254995
     11792           1.375          999                   1              254995
     12864             1.5         1003               1.002              254995
     13936           1.625         1003               1.002              254995
     15008            1.75         1003               1.002              254995
     16080           1.875         1003               1.002              254995
     17152               2         1003               1.002              254995

14 rows selected.


Re: Database Performance vs SGA size [message #625827 is a reply to message #625826] Tue, 14 October 2014 08:40 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
>SGA size is configured as 12GB.
reported reality disagrees with statement above & does not indicate that SGA should be increased.
post SQL & results to show how you obtained 12GB value.
Re: Database Performance vs SGA size [message #625828 is a reply to message #625827] Tue, 14 October 2014 08:47 Go to previous messageGo to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Just now we have increased it to 15 GB, the above select is provided after it has been changed to 15GB.

Please find below the the way we are checking SGA size.

SQL> show parameters sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 15G
sga_target big integer 0
SQL>
Re: Database Performance vs SGA size [message #625829 is a reply to message #625828] Tue, 14 October 2014 08:55 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
sakamboh wrote on Tue, 14 October 2014 06:47
Just now we have increased it to 15 GB, the above select is provided after it has been changed to 15GB.

Please find below the the way we are checking SGA size.

SQL> show parameters sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 15G
sga_target big integer 0
SQL>



above does NOT show current size.
It is obvious that you don't know what you are doing.
SQL> show sga

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             348129976 bytes
Database Buffers          100663296 bytes
Redo Buffers                6008832 bytes

Re: Database Performance vs SGA size [message #625830 is a reply to message #625828] Tue, 14 October 2014 08:56 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you restart the DB after changing sga_max_size?
Cause the above query results say that it's 8G and the DB will be quite happy if you reduce it to 4G.
SGA is not the problem here.
So I suggest you start investigating the actual issue rather than guessing. A sqltrace of the problem sessions would be a good place to start.
Re: Database Performance vs SGA size [message #625832 is a reply to message #625829] Tue, 14 October 2014 09:01 Go to previous messageGo to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
I was showing you the value set for SGA parameter.

find below results..
SQL> show sga

Total System Global Area 1.6034E+10 bytes
Fixed Size                  2244192 bytes
Variable Size            9462350240 bytes
Database Buffers         6543114240 bytes
Redo Buffers               26505216 bytes
SQL>

*BlackSwan added {code} tags to improve readability. Do so yourself in the future.

[Updated on: Tue, 14 October 2014 09:04] by Moderator

Report message to a moderator

Re: Database Performance vs SGA size [message #625833 is a reply to message #625828] Tue, 14 October 2014 09:04 Go to previous messageGo to next message
John Watson
Messages: 7266
Registered: January 2010
Location: Global Village
Senior Member
sakamboh wrote on Tue, 14 October 2014 14:47
Just now we have increased it to 15 GB, the above select is provided after it has been changed to 15GB.

Please find below the the way we are checking SGA size.

SQL> show parameters sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 15G
sga_target big integer 0
SQL>
These show that you have not set the SGA size. You have set the maximum possible SGA size. It looks to me as though you may be using automatic memory management.
You need to show the complete set of instance parameters, please. Then you need to run the applications for a sensible time before querying the various memory advisor views.
Re: Database Performance vs SGA size [message #625834 is a reply to message #625833] Tue, 14 October 2014 09:18 Go to previous message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
>We are facing database performance issue in our live environment.

What evidence (SQL & results) can you post that shows any relationship between the reported performance problem & the size of the SGA?
Most solutions to generic performance problem do NOT involve any changes to the SGA.

[Updated on: Tue, 14 October 2014 09:50]

Report message to a moderator

Previous Topic: Error login to the Oracle 11g (3 merged by MC)
Next Topic: Temp tablespace size
Goto Forum:
  


Current Time: Sun Feb 25 19:49:13 CST 2018

Total time taken to generate the page: 0.01129 seconds