Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Showing results for 
Search instead for 
Did you mean: 

Question about init.ora

Go to solution
Steve Bazinet_1

Question about init.ora

In the init.ora file it has recommended setting of values for SMALL, MEDIUM, and LARGE databases. I am not a DBA, but did not like the answer I got from our DBA, so I figured I would ask here, what defines a SMALL/MEDIUM/LARGE database?


harry d brown jr
Honored Contributor

Re: Question about init.ora


it's subjective in my opinion. To me a SMALL database is something under 100GB, a MEDIUM is something under 1TB, and a LARGE is 1TG+++

live free or die
Live Free or Die
A. Clay Stephenson
Acclaimed Contributor

Re: Question about init.ora


There is obviously no real answer to this question. Everyone would agree that a database with lots of large rows is a large database BUT a database that has many,many rows of data that are themselves quite small might also be considered a large database.

I suppose something in the tens of GB is small, medium is maybe 90-800 GB and large is anything bigger but I could still devise an 8 GB database with many rows and many indices that could tax the most robust system.

Bear in mind, that the only really immportant value initially is blocksize; before 9i this value is fixed and can't be changed after the database is built (without a full export/import); essentially the others can be tuned as you go. The small, medium, and large guidelines are just meant to be starting points. You may find that you will have some that are more typical of large databases and other parameters that better fit large databases - all within the same instance.

If it ain't broke, I can fix that.
Dennis J Robinson
Frequent Advisor

Re: Question about init.ora

Nice answers everyone.

The init.ora settings while intended to be a starting point, have been the same starting point for over 5 years now.

The "LARGE" settings are barely suitable for a "SMALL" database today.

Please do not create your database with any of those recommended values!!! They are way too small for anything useful - especially the db_block_size and db_block_buffers...
You know the drill
Alexander M. Ermes
Honored Contributor

Re: Question about init.ora

Hi there.
Don't trust the sample values in the init.ora, because that will not help you any further.
If you need help, just give us some basic info about the size. I am sure, taht somebody can give you an attachment of an init.ora, that will come close to your needs.
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Andreas D. Skjervold
Honored Contributor

Re: Question about init.ora


As Clay states, the important value for starters is the db_block_size as this can't easily be changed (up to 9i).

As for the values for db_block_buffer / shared_pool_size you'll have to get the database up and running in a scalable test enviroment and begin running the application upon it.
Use some performance tool ( or manually) to check for buffer cache hit ratio (should be close to 100%), this is how much of your data that remains in your db_buffer between transactions. If the ratio is low you'll have to increase the db_block_buffers setting.

Second look for the Library Cache hit ratio (should be close to 100%). This is the parsed SQL statements that are buffered in the shared_pool for reuse.
Increase the shared_pool_size if necessary.

Neat SQL's:
Buffer cache:
select round(((1-(sum(decode(name,
'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0))+
(sum(decode(name, 'consistent gets', value, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio"
from v$sysstat;

Library cache:
select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
from v$rowcache;

Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Honored Contributor

Re: Question about init.ora


actually I have seen these comments and values back in 1984 with Oracle5!
And even then they were already too low...

Up to and including Oracle8i you must not change only two parameters: the characterset used in the "create database" command, and the "db_block_size".
But care for "shared_pool_size" and the "sort_area_size" and "sort_area_retained_size", as these are much too low all the time!

Just my $0.02,

Ron Gordon

Re: Question about init.ora

I was told by oracle 5 years ago that S/M/L has nothing to do with the size of the db but the number of concurrent users so Small if 1-10 users Medium 11-49 and Large 50+ but you should get your dba to tune your db.
Bill Thorsteinson
Honored Contributor

Re: Question about init.ora

For multi-block read count, I consider the size and frequency of tablescans requiring data loads.

For db_block buffers I consider the size of the data that should be held in memory. Code tables, and other tables frequently tablescanned. Available memory and number of running instances are also a major factor.

For shared_pool_size, I consider the number of packages and the number of commonly run queries.

As noted these are just starting points and are definitely sized for systems with little available memory.

Watch that you don't run out of memory or you could end up with a severe memory thrashing problem.