Setting SGA size

[malru]

Hi,

Please clear the following doubt,while setting SGA size for a database creation,which one of the these two,DB_CACHE_SIZE & SHARED_POOL_SIZE should be sized more than other one?

I think always DB_CACHE_SIZE should be sized more when compared to SHARED_POOL_SIZE because database buffer is going to hold the actual data and if it's sized more,i/o will be reduced.

Am i right?If anybody is having other suggesstion or views please let me know.

For creating database of size 20GB,how should i size the db_cache_size & shared_pool_size.

Is there any document,which clearly describes how to set SGA parameters according to database size?

Thanks...

[davey23uk]

it totally depends on your application

[malru]

Thanks Dave,

My application type is OLTP,so can you tell me how can i size the SGA?
Could you please explain it to me little more briefly?

[davey23uk]

its dependant on your application, totally - its pointless spouting out numbers which have no basis to them - you need to know the behaviour of your application

[waitecj]

Google for the word "silver bullets oracle" and ignore everything that comes up.

There is no such thing as a perfect setup for an Oracle database, only a perfect setup for your environment. Study performance, change parameters, test.

[dbasan]

There is no thumb rule to set SGA on the go for your environment. Some where I read

It’s often very easy to spot the quick fixes (aka silver bullets) that make one SQL statement or one program go faster. The difficult bit is working out whether it’s possible to use a specific fix, what the side effects are, whether you’re prepared accept some associated risks, and what else you have to do to make it safe (enough).

Google in for the terms and study all the materials, you would get some clues as what to do and what not.

Try creating the database using DBCA with the default oracle memory settings and start working on this test db. You would come to know this memory settings may be less. try increasing them until your application works better. Run statspack to check and increase the parameters. This would go for few cycles until your application behaves the way you need. That's it you are set, you can move this db to production. What ever you get from the thumb rule setting may not be as good as your test db for your environment.

It may be time consuming but its the best solution any one can suggest you.
Good luck