| sizing SGA...??? [message #137744] |
Fri, 16 September 2005 08:10  |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
hai all,
our db size is around 250G.
we need to tune the database.
the sga_max_size is 600M.is it sufficient for the
database to work in gooh health..?
if to increase , in what proportion it should be increased..??
plz help out..
regards
bala
|
|
|
|
|
|
| Re: sizing SGA...??? [message #137751 is a reply to message #137749] |
Fri, 16 September 2005 09:20  |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
yes , we have the statspack report.!!!
with in that the buffer hit ratio is only 80% and library hit ratio is 99%. so we presume that the buffer size is not enough..
i would like to know in general , how to set the sga_max_size for a database with 250G of size....?
moreover , the report has set os SQL statements needs to be tuned.
we have used dbms_stats package to gather the statistics for the objects involved in those statements to trigger the CBO...is it right..??
[Updated on: Fri, 16 September 2005 09:20] Report message to a moderator |
|
|
|
| Re: sizing SGA...??? [message #137758 is a reply to message #137751] |
Fri, 16 September 2005 10:20 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Ignore Buffer hit ratios.
They mean nothing.
>>moreover , the report has set os SQL statements needs to be tuned.
What is OS SQL STATEMENT?
What makes you to say that these reports need to be tuned?
are they running slow?
THen identify the offending sql/module fix it.
in Most case it is a bad sql.
(THe problem may be with your sql , or database or report server or webserver or your network).
Set your acceptable limit for those sql statments.
gather stats, look into plans, compare the plans / statspack reports before and after tuning the statment.
had you read the documentation, it says
SGA_MAX_SIZE - Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.
So it means, it depends on the options you use, nature of your database.
How much sql/plsq/java/xml you run?
and SGA_MAX_SIZE is the upper/max limit for SGA.
SGA_MAX_SIZE limits your buffer cache and shared pool.
so if SGA_MAX_SIZE=1g, oracle grabs 1g memory from OS. If your process uses only 100m all the rest are paged.
I would usually start low and go higher if we have issues ( it depends on OS,filesystem etc).
But these are not RULES.
>>we have used dbms_stats package to gather the statistics for the objects involved in those statements to trigger the CBO...is it right..??
Read sticky?
http://www.orafaq.com/forum/t/51267/42800/
|
|
|
|
| Re: sizing SGA...??? [message #138128 is a reply to message #137758] |
Tue, 20 September 2005 04:39 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
As per the question ....mahesh had given proper answer which is to the point and correct.
But few thing i want to specify very briefly.... so please go through it
Perforamce Tunning should be Goal oriented (i.e)
===========================================
1. You have to define problem definition.
2. Look for solution (note: - for one problem there are many solution)
3. Apply Solution (note: - we should be care full when applying solution i.e. Apply solution slowly and partly)
When we do performance tuning we should know DataBase resources
===============================================================
1. Server hardware resources three thing are most important as per server are concern
Processor ----------> Affect the background process.
RAM-----------------> Limits SGA.
DISK (hard disk)---> It limit your Database size.
2. Database resources
SGA
Background process
Data files
Log files
Undo segments.
Object (user is also object).
AREAS Where PT is doen:
=======================
Design.
Application : (SQL)
Memory.
I/O.
Contention.
O/s.
Problems for which Performance Tunning is done are:
===================================================
60% are with application / program.
20% database design.
18% database tunning
2% operating system.
Who tunes :
============
Application designers.
Application developers.
DBAs.
System Administrators.
HOW PT PROCESS is carried (it is time consuming process)
========================================================
Gather statistics
Compare with standards
Find out result.
Take corrective actions.
==================================
SGA Size for 1 GB RAM (thumb rule)
==================================
SGA is made up of 5 part
Suppose if we consider SGA of 1 GB than
1. Shared pool is 48% of SGA
2. Database buffer is 48% of SGA.
3. Log Buffer is 4 % of SGA.
4. Java pool 20 MB
5. Large pool 20 MB.
There is table related to SGA
V$SGASTAT
The table shows SGA status
From this table we can find how SGA behaves.
I think above written things will help you lot
Regards
Always Friend Sunilkumar
|
|
|
|
| Re: sizing SGA...??? [message #138129 is a reply to message #138128] |
Tue, 20 September 2005 04:43 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
| Quote: |
SGA is made up of 5 part
Suppose if we consider SGA of 1 GB than
1. Shared pool is 48% of SGA
2. Database buffer is 48% of SGA.
3. Log Buffer is 4 % of SGA.
4. Java pool 20 MB
5. Large pool 20 MB.
|
Where does the extra 40M come from?
48%+48% +4% = 100% you then add and extra 40M Where does it come from??
Jim
|
|
|
|
| Re: sizing SGA...??? [message #138152 is a reply to message #138129] |
Tue, 20 September 2005 07:40 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
sorry for the calculation
SGA is made up of 5 part
Suppose if we consider SGA of 1 GB than
1. Shared pool is 472.6 MB of SGA
2. Database buffer is 472.6 MB of SGA.
3. Log Buffer is 39.36 MB of SGA.
4. Java pool 20 MB
5. Large pool 20 MB.
regard
sunil
|
|
|
|
| Re: sizing SGA...??? [message #138154 is a reply to message #138152] |
Tue, 20 September 2005 08:14 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
What if you are not running any java?
Why have a redo log buffer of nearly 40M?
In most instances i would guess at this being massively excessive.
The redo log buffer is 'flushed' at
1/3 full,
on commit and at
1M full
Unless you think that your system is going to write 38M of data in the time that it takes oracle to write at the most 1m from the redo log buffer, then this figure is excessive.
What if the op is running shared server (or not) this will impact the required size of the Large pool.
I think there are too many "what ifs" here to be able to give any real "rule of thumb" The best/only advice would be to go and read up on the documentation.
Jim
|
|
|
|
| Re: sizing SGA...??? [message #138281 is a reply to message #138154] |
Wed, 21 September 2005 03:08 |
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
|
Member |
|
|
All good points above.. Just an addition on the MAX_SGA_SIZE AND THE REDO LOG BUFFER
MAX_SGA_SIZE = 4g (we have 4g available)
SHARED_POOL = 500M
BUFFER_CACHE = 500M
FIXED AREAS (INCLUDING THE REDO LOG BUFFER) = 10M
LARGE_POOL = 50M
JAVA_POOL = 50M
(sizes for example only!!)
We are using less than 1g of our available space!!
Even though we have set the sga_max_size to 4g, oracle won't take 4g from the os,
only what it currently requires (about 1g) - THATS GOOD, THE REST IS
AVAILABLE TO THE APPLICATION OR WHATEVER..
We can grow to 4g if we want - that's also good...
However here's the gotcha!!! even though we are not taking 4g from the os
if we don't need it, each server process connected to the database will
have a paging table to refer to the whole possible 4g, that means each
server process will be significantly larger than it needs to be with the
current sga requirements... (unless your using ISM - Intimate Shared Memory)
My point is don't set the MAX_SGA_SIZE to be the biggest you can, set it
to be the biggest that you will need either on startup or for future
changes...
ALSO
Be careful of the REDO_LOG_BUFFER, it is usually trivial compared to the other SGA componants.
In my experience I've managed to run a database sucessfully with a redo log buffer of less than 1M, use the following scrip to see if you are having any waits on LGWR or SERVER PROCESSESS. This will let you know if you need to increase (or decrease it..)
col name for a30
set lines 200
set echo on
set feedback on
-- "REDO ENTRIES" SHOWS HOW MANY TIMES SPACE WAS ALLOCATED WITHIN THE
-- REDO LOG BUFFER SINCE INSTANCE STARTUP
-- "REDO BUFFER ALLOCATION RETRIES" SHOWS HOW MANY TIMES A REQUEST FOR
-- SPACE WITHIN THE REDO LOG BUFFER WAS UNSUCSESSFUL
SELECT a.name, a.value, b.name, b.value, (b.value/a.value)*100 "ratio"
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'redo entries'
AND b.name = 'redo buffer allocation retries'
/
set lines 75
AND
col name for a50
set lines 200
set echo on
set feedback on
-- CHECKPOINT INCOMPLETE CAN INDICATE THAT THERE ARE TO FEW REDOLOG GROUPS
-- OR THAT THE REDOLOG FILE SIZE MAY BE TOO SMALL
-- LOGFILE PARALLEL WRITE INDICATES A POSSIBLE I/O PROBLEM WITH THE LOGFILES.
SELECT name
FROM v$event_name
WHERE name = 'log file switch (checkpoint incomplete)'
OR NAME = 'log file switch (archiving needed)'
OR NAME = 'log file parallel write';
SELECT event, total_waits
FROM v$system_event
WHERE event = 'log file switch (checkpoint incomplete)'
OR event = 'log file switch (archiving needed)'
OR event = 'log file parallel write';
set lines 75
[Updated on: Wed, 21 September 2005 03:09] Report message to a moderator |
|
|
|
| Re: sizing SGA...??? [message #138298 is a reply to message #138281] |
Wed, 21 September 2005 04:19 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,,
==================================
SGA Size for 1 GB RAM
==================================
SGA is made up of 5 part
Suppose if we consider SGA of 1 GB than
1. Shared pool is 48% of SGA
2. Database buffer is 48% of SGA.
3. Log Buffer is 4 MB of SGA. <========
4. Java pool 20 MB
5. Large pool 20 MB.
What if i have such sizing for my SGA...?
regards
Always friend sunilkumar
|
|
|
|
| Re: sizing SGA...??? [message #138304 is a reply to message #138298] |
Wed, 21 September 2005 04:32 |
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
|
Member |
|
|
The shared_pool size bares no relation to the physical size of your database files.. it is sized based on the amount of code (reusable and otherwise) running against your database. e.g a 2TB database that runs 1 query per day will need a much smaller shared_pool than a 2gb database that runs 2000 querys per hour!!
DB_Buffer_cache is used to store copies of oracle block from disk, again it's size is related to the amount of access, disk i/o, full table scans, index scans and so on.. is 48% the right size WHO KNOWS, only you!!
Log_Buffer = 4M may be better than 38M but are you having waits, where server processes can't find space, or are you having waits for LGWR writting to disk?? that will be your indication..
Java_pool & Large_pool = no idea if your sizes are right, do you need them, are you running java against your database, are you using shared servers, are you using rman.. all these questions and more will need to be addresses.
Unfortunately there is no simple formula, if there was everyone would be doing it..
Sizing the SGA is a feat of experience, trial and error, watching, checking and again experience.
Stick with it, read the concepts guides, Tom Kyte's Oracle Expert one on one was very useful to me at the beginning and keep asking the questions.
Allie
|
|
|
|
| Re: sizing SGA...??? [message #138754 is a reply to message #138304] |
Fri, 23 September 2005 07:47 |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
@Allie
You wrote:
We can grow to 4g if we want - that's also good...
How can that be good, if you are having all of 4 gb RAM only & allocating everything of it to Oracle. Believe me... this scenario will cause great-graet performance issues for you...
Try to restrict your SGA to max 75-80% of RAM...
Regds
Girish
|
|
|
|
| Re: sizing SGA...??? [message #138759 is a reply to message #138754] |
Fri, 23 September 2005 07:52 |
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
|
Member |
|
|
I get your point, but what I meant was that we have 4G which can be allocated to Oracle, this will not as you rightly suggest all the memory on the server, some will have to be put aside for the OS and anything other than Oracle running on the server.
But you were right to point this out so as not to cause miss-understanding...
|
|
|
|
| Re: sizing SGA...??? [message #138763 is a reply to message #138759] |
Fri, 23 September 2005 08:20 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
Hmm,
girish I think you've made an incorrect assumption here. According to alliejane's post, she said that the sga_max_size was 4g. I don't see anywhere where she said that the total ram available is 4g?
By her post she could easily have had far more ram available to the system, it's just that 4g was made available to Oracle.
Jim
|
|
|
|
| Re: sizing SGA...??? [message #139725 is a reply to message #138763] |
Thu, 29 September 2005 06:02 |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
@JS
It was mentioned in the post itself.
Post: Wed, 21 September 2005 13:38
MAX_SGA_SIZE = 4g (we have 4g available)
& I concluded on basis of that only.
--Girish
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
