Home » RDBMS Server » Performance Tuning » Decide the size of every component of SGA in given case?
icon5.gif  Decide the size of every component of SGA in given case? [message #117149] Mon, 25 April 2005 08:18 Go to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,


What should be the percentage of every SGA Component if SGA total size is 100MB

Data base buffer
Redo buffer
Shared pool
java pool
PGA

case here is that user is only doing select on database and there are 500 user and... please let me know the reason why you decided that much % for every component of SGA (specially the size of Redo buffer)

thank you for your reply in advance... but please dont forget to send me the reason for deciding the size of every component of SGA in given above case ..

From:- Sunil
Re: Decide the size of every component of SGA in given case? [message #117189 is a reply to message #117149] Mon, 25 April 2005 12:11 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
If you are using oracle 10g you can just use sga_target and let oracle figure it out for you.

Otherwise, it is best to read the concepts guide chapter on memory architecture. Understand the various components of memory and their purposes, and then understand what your particular system needs to do, and then you'll have a starting point for the values. Then tweak things over time to fine tune your guesses, using what you learn from reading the performance tuning guide.
Re: Decide the size of every component of SGA in given case? [message #117398 is a reply to message #117189] Wed, 27 April 2005 00:58 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,

sorry to disturb you again smartim, but this question was asked to me in my interview .. and i dont know what to say ...

case that interviewer had given me is as .... nearly 500 user are only doing select on databse (i.e. one type of query) they are not updating any record in database nor they are inserting or deleting ....AS for example you can consider that when you send *123# to your mobile connection provider to AIR TEL to know your balance money left in prepaid card you get message showing your balance money left that can be used by you...
Note:-in place of *123# it can be any code that your mobile service provider had mention for checking balance... now by sending this code to service provider we get information of our balance money in preparid card.

so now can you tell me ...any wild guess, but that with some reason

What should be the percentage of every SGA Component if SGA total size is 100MB

Data base buffer
Redo buffer
Shared pool
java pool
PGA

case here is that user is only doing select on database and there are 500 user and... please let me know the reason why you decided that much % for every component of SGA (specially the size of Redo buffer)

thank you for your reply in advance... but please dont forget to send me the reason for deciding the size of every component of SGA in given above case ..

From:- Sunil
Re: Decide the size of every component of SGA in given case? [message #123904 is a reply to message #117149] Wed, 15 June 2005 08:49 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
After reading and understanding the concepts, you would realize that the interviewer did not provide enough information to answer the question, even with a good guess. For example, is the 500 users the total users on the system or the active concurrent user sessions at any one time? Are stored procedures in the database written in Java? Do users execute a very low number of distinct queries or is the number higher? Is there a lot of plsql code stored in the database? Is the size of the data involved very big or relatively small? Are there lots of indexes, or very small lookup tables? All of these factors must be taken into consideration.
Re: Decide the size of every component of SGA in given case? [message #124022 is a reply to message #117149] Wed, 15 June 2005 18:33 Go to previous messageGo to next message
DMcG
Messages: 51
Registered: May 2005
Location: Auckland, New Zealand
Member
If you have 500 users and 100Mb of SGA, it doesn't really matter how you apportion it, your system is going to be slow! Laughing

As Smartin says, unless you know the performance characteristics of the database then it's almost impossible to accurately spec the memory parameters for tunning.

Dougie McGibbon
Re: Decide the size of every component of SGA in given case? [message #124125 is a reply to message #124022] Thu, 16 June 2005 09:08 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
In addition to Dougie's and Smartin's valuable advices -
there are tuning views such as v$db_cache_advice and v$librarycache. They can help you to make a decision on
changing SGA values counting on real database load statistics.

Rgds.

[Updated on: Thu, 16 June 2005 09:09]

Report message to a moderator

Re: Decide the size of every component of SGA in given case? [message #124180 is a reply to message #124125] Thu, 16 June 2005 14:10 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
There is a quite readable section on memory configuration on the Performance Tuning Guide. As a first guess for setting PGA_AGGREGATE_TARGET they recommend a smaller setting for OLTP systems, such as the one described above, than for DSS systems such as a data warehouse.
Re: Decide the size of every component of SGA in given case? [message #124184 is a reply to message #117149] Thu, 16 June 2005 14:38 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I really like the 10g Performance Tuning Guide. I haven't read the whole thing cover to cover yet, but I'm getting there, and it is really good and time well spent.
Previous Topic: Storage recomendation please
Next Topic: VERY URGENT -- SQL- TUNING
Goto Forum:
  


Current Time: Sun Sep 20 16:25:24 CDT 2020