Home » RDBMS Server » Server Administration » Changing SGA parameter (Oracle 11g R2, 2 node RAC on solaris 10)
Changing SGA parameter [message #622715] Thu, 28 August 2014 12:23 Go to next message
janakors
Messages: 180
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

Re: Changing SGA parameter [message #622717 is a reply to message #622715] Thu, 28 August 2014 12:33 Go to previous messageGo to next message
BlackSwan
Messages: 26203
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.
Re: Changing SGA parameter [message #622719 is a reply to message #622715] Thu, 28 August 2014 12:40 Go to previous messageGo to next message
John Watson
Messages: 7620
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.


Re: Changing SGA parameter [message #622723 is a reply to message #622719] Thu, 28 August 2014 13:14 Go to previous messageGo to next message
janakors
Messages: 180
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

Re: Changing SGA parameter [message #622724 is a reply to message #622723] Thu, 28 August 2014 13:16 Go to previous messageGo to next message
janakors
Messages: 180
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
Re: Changing SGA parameter [message #622725 is a reply to message #622723] Thu, 28 August 2014 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 26203
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)!
Re: Changing SGA parameter [message #622726 is a reply to message #622723] Thu, 28 August 2014 13:26 Go to previous messageGo to next message
John Watson
Messages: 7620
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.
Re: Changing SGA parameter [message #622727 is a reply to message #622726] Thu, 28 August 2014 13:32 Go to previous messageGo to next message
BlackSwan
Messages: 26203
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.

Re: Changing SGA parameter [message #622738 is a reply to message #622727] Thu, 28 August 2014 14:40 Go to previous messageGo to next message
janakors
Messages: 180
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
Re: Changing SGA parameter [message #622934 is a reply to message #622738] Tue, 02 September 2014 04:27 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, 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
Re: Changing SGA parameter [message #623214 is a reply to message #622934] Sat, 06 September 2014 04:00 Go to previous messageGo to next message
janakors
Messages: 180
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
Re: Changing SGA parameter [message #623220 is a reply to message #623214] Sat, 06 September 2014 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No but you can decrease it.

Re: Changing SGA parameter [message #623229 is a reply to message #623220] Sat, 06 September 2014 14:09 Go to previous messageGo to next message
janakors
Messages: 180
Registered: September 2009
Senior Member
thanks but how can i interpret the above query output?any help? any reading for that?

Regards,

Janakors
Re: Changing SGA parameter [message #623230 is a reply to message #623229] Sat, 06 September 2014 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Changing SGA parameter [message #623231 is a reply to message #623230] Sat, 06 September 2014 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Changing SGA parameter [message #623232 is a reply to message #623229] Sat, 06 September 2014 14:19 Go to previous messageGo to next message
BlackSwan
Messages: 26203
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.


Re: Changing SGA parameter [message #623237 is a reply to message #623232] Sun, 07 September 2014 03:24 Go to previous messageGo to next message
janakors
Messages: 180
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


Re: Changing SGA parameter [message #623258 is a reply to message #623237] Sun, 07 September 2014 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 26203
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/database/121/TDPPT/toc.htm
Re: Changing SGA parameter [message #623267 is a reply to message #623258] Sun, 07 September 2014 11:31 Go to previous message
janakors
Messages: 180
Registered: September 2009
Senior Member
Thank u sir i am on it...thanks a lot
Previous Topic: Slow response during peak activity (merged)
Next Topic: Fatal NI connect error 12170.
Goto Forum:
  


Current Time: Sat Oct 20 16:28:34 CDT 2018