OraFAQ Forum: Server Administration » Database Performance vs SGA size

Home » RDBMS Server » Server Administration » Database Performance vs SGA size (LINUX 5.3 Oracle 11gr2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Database Performance vs SGA size [message #625818]

Tue, 14 October 2014 07:49

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.

Report message to a moderator

Re: Database Performance vs SGA size [message #625823 is a reply to message #625818]

Tue, 14 October 2014 08:02

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Database Performance vs SGA size [message #625826 is a reply to message #625823]

Tue, 14 October 2014 08:36

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.

Report message to a moderator

Re: Database Performance vs SGA size [message #625827 is a reply to message #625826]

Tue, 14 October 2014 08:40

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Database Performance vs SGA size [message #625828 is a reply to message #625827]

Tue, 14 October 2014 08:47

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>

Report message to a moderator

Re: Database Performance vs SGA size [message #625829 is a reply to message #625828]

Tue, 14 October 2014 08:55

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Database Performance vs SGA size [message #625830 is a reply to message #625828]

Tue, 14 October 2014 08:56

cookiemonster
Messages: 13961
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.

Report message to a moderator

Re: Database Performance vs SGA size [message #625832 is a reply to message #625829]

Tue, 14 October 2014 09:01

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

John Watson
Messages: 8962
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.

Report message to a moderator

Re: Database Performance vs SGA size [message #625834 is a reply to message #625833]

Tue, 14 October 2014 09:18

BlackSwan
Messages: 26766
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 01:24:02 CST 2024