Tom..

I am immensly impressed by your great job. i have come to you to resolve one of the problem i faced today ...

while installing oracle today ...already there was 6 instance of orace running in the Sun Server with OS Solaris 2.8. ..the 7th instance which i was creating could not connect to oracle. error meesage "Oracle not available " i thought it was memory problem and so i stopped 2 of the running instance and proceeded ...i got it installed this time after stopping two instances.

i am worried about what will happen if i start all the 7 intances.. will it crash ??? - Pray GOD - It should not.

each instance has the Total System Global Area of 41812128 bytes (~ 40 mb)

I wanted to know the guidelines on sizing the SGA. I have read different messages that offer different guidelines about the size of the SGA.One says that the size of all SGAs on a system should be maximum of half the physical memory. Some say a Third. I have 1 GB of physical memory.

My machine has Websphere 5.0 and Oracle 8i and there is 7 instance of Oracle running !!. There will be only one applications running against each instance and the one instance of websphere will be catering to all 7 applications. I want to size it for application having only 5 user connections to the database.

My DB_BLock_Size is 8M. Currently processes parameter is 150.

shall i go about with the current sga size of 40 mb?
What guideline should I use to size the total SGA ? 500 M what?
How should I breakdown the SGA between the Variable Size, Fixed Size , Database Buffers and Redo Log Buffers. Are there any guidelines on these sizes.

Please advice me ...

Thanks in advance,

tom,
sorry for my ignorance,.. please throw some light on my questions below ...

i want actually 6 database( 6 instances). so you mean to say that we should not have many instances ?.. you are telling to have ONE database, ONE instance -- no more then that. RIGHT?

else ..if i can have more than 1 instances ...for 1 Gb ram how many instances can i have ...? (please keep in mind that i will also have an app server running in the same machine ). its just a development server - used mainly for testing, not much of load.

please let me know what parameter in etc/system i shud look for to change the value to accomadate my instances.

Thanks for your patience in hearing me....

Thanks tom for your valuable input. The link was good enough,....

yes as you say i am forced to use the single server for ...

I have a thought, let me know if you appreciate that .
i have planned to run one instance at a time ..while stopping all the other. when the testing is done on one instance i have planned to start the next, stopping the first one.

Thanks a lot

for i need to have different database for different applications. in all we have 6 different applications that need to be run in solaris environment.

does this make sense? or you have anyother to say.

thank you tom

tom,
i am confused more now .. i mean i didnt get you..

i created database 6 times using dbca. so it means 6 database right?...schema == user right?

and i have one user for each database ...on the schema/user created we apply the framework ...

now tell me whether i have
6 database/6 instance or 1 database/6 instance.

i think its 6 database/6 instance and still can i run all the instances ..pls clarify

sorry tom ..for bugging you

Tom,

I am looking for a query that will display breakdown of SGA
which looks like this.

NAME MB InUSe MB
----------------------------- ---------- --------
db_cache_size 1800 1500
shared_pool_size 800 700
java_pool_size 80 70
large_pool_size 200 150
log_buffer 2 1


Thanks.

use v$sgastat with decode or case to assign the names you want and use group by.

the "inuse" of the buffer cache/log buffer is really pretty much always the buffer itself.  


here is a starting (eg: not full baked) point:

ops$tkyte@ORA9IR2> select name, round(sum(mb),1) mb, round(sum(inuse),1) inuse
  2    from (
  3  select case when name = 'buffer_cache'
  4                  then 'db_cache_size'
  5                      when name = 'log_buffer'
  6              then 'log_buffer'
  7              else pool
  8          end name,
  9             bytes/1024/1024 mb,
 10             case when name <> 'free memory'
 11              then bytes/1024/1024
 12          end inuse
 13   from v$sgastat
 14  )
 15  group by name
 16  /
 
NAME                                   MB      INUSE
------------------------------ ---------- ----------
db_cache_size                          64         64
java pool                              16
large pool                             16
log_buffer                             .6         .6
shared pool                           144       20.8
                                       .4         .4
 
6 rows selected.
 

Tom,

How to increase the sga parameters, given that you have memory added to RAM.

any criteria, and what all governs this ...the scenario is we have asked customer support of sun solaris to increase the ram size, as we saw very poor performance in one server.

we wanted to add some memory to oracle processes... can you let us know, your help at this moment will be really useful for us

Thanks a lot

Tom,
I have few user processes (oracle sessions) taking up about 2Gb of memory. I didn't expect that a user session would takeup this much memory.

How can I troubleshoot and pin point what went wrong in the application

Thanks!

disregard the question.

Hi Tom,
After having read all the posts it comes like SGA sizing is all about making some blanket assumption till you get it right for you.

I would like to know what all factors should we consider while DB sizing. Mean to make a blanket assumption what all are the items i need to count?
And does DB sizing implies any other meaning than SGA sizing?

Thanks,
Kirtish.

Hi Tom,
To add to the above list of question i would like to know, What do you mean by capacity planing and how do you go about it?

Thanks,
Kirtish

I know we dont have any rules of thumb (RoT) round DB sizing. But i expected some answer which will help me do the blanket assumption. Say it this way round, if i am asked by my PM to give a doc on DB sizing. How do i explain him how do we go about DB Sizing? (assumption being the machine is going to have only one instance running and only Oracle s/w on it). I am not asking for any figures, for their cannot be. But we can have larger buckets to classify.

Hi Tom,
When i think of SGA sizing the few thing s that i take into consideration are
1] the number of concurrent users
2] If we are going for Dedicated or Shared servers.
3] Function of sql, pl/sql and Java in the application.
4] What else is going on in the machine.

am i missing any thing on the list?

My question is not round figures but round the list of things i need to consider.

Thanks

Hi Tom,

In your follow up to reviewer Ganob Hutu, you have asked what is the scientific reason for advising additional RAM.

Can you please explain under what circumstances do we need to recommend for additional RAM (assuming that there is no swapping at the OS level)? In other words is there a definitive indicator that shows us that RAM available to Oracle is not enough?

Regards,
Albert Nelson A.

Thanks Tom for answering my question here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:488583600346559562