OraFAQ Forum: Server Administration » Changing SGA parameter

Home » RDBMS Server » Server Administration » Changing SGA parameter (Oracle 11g R2, 2 node RAC on solaris 10)

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

Changing SGA parameter [message #622715]

Thu, 28 August 2014 12:23

janakors
Messages: 232
Registered: September 2009

Senior Member

hi,
i want to increase my SGA currently it is 12 gb and we have 32 gb of RAM so i would like ti increase it to 20 gb. i surf the internet and found that chnaging SGA in 11 g r2 RAC is little buggy so hasiatating little bit as it is production svr.can any one guide or caution me regarding this action like any hazard as doing it for ist time. we are having spfile so to me it as following

alter system set sga_target=20 scope=spfile sid='*';

restart db but my shmsys:shminfo_shmax=17179869184 (16 gb) do i need to increase it as well and if increase do i need to restart the machine or not

anything i am missing please help

Regards

Report message to a moderator

Re: Changing SGA parameter [message #622717 is a reply to message #622715]

Thu, 28 August 2014 12:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>anything i am missing please help
yes, you are missing any facts to conclude that having SGA at 20GB would be a good thing to do.
It appears that you simply believe that a bigger SGA is better.

Ready, Fire, AIM!

If performance problem is because of I/O bottleneck or system is CPU bound, then changing SGA to 20GB could make performance worse.

Report message to a moderator

Re: Changing SGA parameter [message #622719 is a reply to message #622715]

Thu, 28 August 2014 12:40

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

This is terrifying. In Solaris 10, there are no kernel parameters. You control such things through projects. If you increase your SGA without adjusting your project settings for DISM and swap appropriately, you will (eventually) crash your database.

I assume this is a followup to your previous topic: you are under the impression that throwing memory at Oracle will fix your SQL performance problem. You really need to hire a consultant who understands RAC, Solaris, and SQL tuning. If you don't want to do that, then follow the advice you have been given already: identify the problem SQL, and provide the necessary details.

Report message to a moderator

Re: Changing SGA parameter [message #622723 is a reply to message #622719]

Thu, 28 August 2014 13:14

janakors
Messages: 232
Registered: September 2009

Senior Member

well let me tell you the exact situation...we have delays in application(biometric accecc) and blame is on DB i defended and said i have noting in db only some I/O peaks in EM so what should i do now. i have memory with me in machine and ADDM was also recommending that under size sga so that why going for it

Report message to a moderator

Re: Changing SGA parameter [message #622724 is a reply to message #622723]

Thu, 28 August 2014 13:16

janakors
Messages: 232
Registered: September 2009

Senior Member

i have generated AWR report of last two days 27 and 28 of aug i will opst in due time and kindly if anone can read it and find out anything

i know i am asking a lot but without any training i am doing this so trying to learn it

Regards

Report message to a moderator

Re: Changing SGA parameter [message #622725 is a reply to message #622723]

Thu, 28 August 2014 13:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

so have at it.
It is your system, your database, & your consequences be they good or bad.

>so what should i do now?
you keep asking us.
We keep telling you.
You keep ignoring what we say.
so You're On Your Own (YOYO)!

Report message to a moderator

Re: Changing SGA parameter [message #622726 is a reply to message #622723]

Thu, 28 August 2014 13:26

John Watson
Messages: 8965
Registered: January 2010
Location: Global Village

Senior Member

janakors wrote on Thu, 28 August 2014 19:14

well let me tell you the exact situation...we have delays in application(biometric accecc) and blame is on DB i defended and said i have noting in db only some I/O peaks in EM so what should i do now. i have memory with me in machine and ADDM was also recommending that under size sga so that why going for it

One last time: you need to tune the SQL.

If you do not know how to do this, you should tell your manager that you need help. No-one will criticize you for this.

Report message to a moderator

Re: Changing SGA parameter [message #622727 is a reply to message #622726]

Thu, 28 August 2014 13:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post results like below

SQL> select * from v$sga_target_advice;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       213             .75        26890              1.3363              107210
       284               1        20123                   1               58858
       355            1.25        18445               .9166               50859
       426             1.5        18312                 .91               50859
       497            1.75        18312                 .91               50859
       568               2        18312                 .91               50859

6 rows selected.

Report message to a moderator

Re: Changing SGA parameter [message #622738 is a reply to message #622727]

Thu, 28 August 2014 14:40

janakors
Messages: 232
Registered: September 2009

Senior Member

thanks a lot sir now i have postponed the idea of increasing size of SGAs and will post the query result on monday

Report message to a moderator

Re: Changing SGA parameter [message #622934 is a reply to message #622738]

Tue, 02 September 2014 04:27

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Quote:

and blame is on DB

I'm afraid that this is par for the course when one is a DBA. Everything ultimately connects to a database and every link in the system chain will pass the blame on ultimately to the database. You have to do as John and BlackSwan have suggested to demonstrate what the actual problem is. The most common cause of poor performance is poorly written SQL. No amount of extra memory will fix this.

HTH
-g

Report message to a moderator

Re: Changing SGA parameter [message #623214 is a reply to message #622934]

Sat, 06 September 2014 04:00

janakors
Messages: 232
Registered: September 2009

Senior Member

after away for some time here is the query

SQL> select * from v$sga_target_advice;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
      4608            .375     17482058              2.7409           524600473
      6144              .5      9094063              1.4258           163718659
      7680            .625      6894216              1.0809            94248763
      9216             .75      6506420              1.0201            63755411
     10752            .875      6411385              1.0052            56243412
     12288               1      6378218                   1            53657138
     13824           1.125      6346965               .9951            51210373
     15360            1.25      6325917               .9918            49552367
     16896           1.375      6309333               .9892            48200207
     18432             1.5      6262772               .9819            39175076
     19968           1.625      6262134               .9818            39175076

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
     21504            1.75      6262134               .9818            39175076
     23040           1.875      6261497               .9817            39175076
     24576               2      6261497               .9817            39175076

14 rows selected.

SQL>

do i need to inc

Regards

Report message to a moderator

Re: Changing SGA parameter [message #623220 is a reply to message #623214]

Sat, 06 September 2014 05:35

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No but you can decrease it.

Report message to a moderator

Re: Changing SGA parameter [message #623229 is a reply to message #623220]

Sat, 06 September 2014 14:09

janakors
Messages: 232
Registered: September 2009

Senior Member

thanks but how can i interpret the above query output?any help? any reading for that?

Regards,

Janakors

Report message to a moderator

Re: Changing SGA parameter [message #623230 is a reply to message #623229]

Sat, 06 September 2014 14:15

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3034.htm#REFRN30393

Report message to a moderator

Re: Changing SGA parameter [message #623231 is a reply to message #623230]

Sat, 06 September 2014 14:16

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#ADMIN11201

Report message to a moderator

Re: Changing SGA parameter [message #623232 is a reply to message #623229]

Sat, 06 September 2014 14:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

according to v$sga_target_advice


if you changed the size SGA from 12288 to be 04608, then the number of physical read will go from 53,657,138 to 524,600,473
( SGA decreases & the number of physical reads increases)
if you changed the size SGA from 12288 to be 24576, then the number of physical read will go from 53,657,138 to  39,175,076
(SGA increases & the number of physical reads decreases)

performance improves when you can do more physical reads in the same amount of time.

Report message to a moderator

Re: Changing SGA parameter [message #623237 is a reply to message #623232]

Sun, 07 September 2014 03:24

janakors
Messages: 232
Registered: September 2009

Senior Member

ohhh thank you very much...now i have got it and thanks for the document. i have already start reading it. but now want to read on this database tuning subject but where should i start. please let me know i dont want to ask any foolish question anymore on this orafaq.com rather want to contribute to myself and orafaq.com, so please tell me u experts out there that on database tunning subject step by step which guides should i start reading..as i can only learn when things are in sequence and i also know that tuning of db came through experience so i have got good platform . Please help i want to educate

Best Regards

Report message to a moderator

Re: Changing SGA parameter [message #623258 is a reply to message #623237]

Sun, 07 September 2014 09:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://docs.oracle.com/database/121/TDPPT/toc.htm

Report message to a moderator

Re: Changing SGA parameter [message #623267 is a reply to message #623258]

Sun, 07 September 2014 11:31

janakors
Messages: 232
Registered: September 2009

Senior Member

Thank u sir i am on it...thanks a lot

Report message to a moderator

Previous Topic:	Slow response during peak activity (merged)
Next Topic:	Fatal NI connect error 12170.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 22 17:32:44 CST 2025