Question about init.ora

[Steve Bazinet_1]

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?

Thanks

Steve

[harry d brown jr]

Steve,

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
harry

[A. Clay Stephenson]

Hi:

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.

[Dennis J Robinson]

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...

[Alexander M. Ermes]

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.
Rgds
Alexander M. Ermes

[Andreas D. Skjervold · Accepted solution]

Hi

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;

Andreas

[Wodisch]

Hi,

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,
Wodisch

[Ron Gordon]

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]

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.