Re: Please help me debug these critical init.ora params

"Mike Harris" <badshah2000_at_hotmail.com> wrote in message news:a3e27c56.0306081041.40096bdf_at_posting.google.com...
> Hi,
>
> The server is running W2K Server, SP/3.
> 2GB (2048MB) RAM.
> Oracle 8.1.6 with all the latest patches.
>
> The server is running only one instance of Oracle, and nothing else
> runs on this machine.
>
> repeat, the machine has 2GB RAM.
> The original parameters in init.ora were (these are not typos)
> DB_BLOCK_SIZE=2048
> DB_BLOCK_BUFFERS=35000 (this results in allocating about 70MB to it).
> SHARED_POOL_SIZE=40MB
> LARGE_POOL_SIZE=0 (yes, zero)
>
> During peak hours, over 100 users connect to the machine. As you can
> guess, it would slow to a crawl. Upon some investigation, these
> parameters were brought to light. Following is what was tried as a
> fix.
> DB_BLOCK_SIZE=2048 (it should not be modified, right?)
> DB_BLOCK_BUFFERS=600000 (results in about 1170MB)
> SHARED_POOL_SIZE=350MB
> LARGE_POOL_SIZE=35MB
>
> The problem is that it didn't run. Only a short while into running
> again, users started to get "Out of process memory" errors (while
> tried to allocate 26K of memory, etc). We started to lower the
> DB_BLOCK_BUFFERS and SHARED_POOL_SIZE. Eventually, it was brought down
> to all the way
> DB_BLOCK_BUFFERS=350000 (about 680MB)
> SHARED_POOL_SIZE=200MB
>
> But the "out of process memory" errors didn't go away. They got
> delayed a little but eventually came up. In frustration, we restoed it
> to the original init.ora file. But as you can see above, that is
> probably not a very efficient. Machine has 2GB of memory, and only
> 7-8% of that is allocated to Oracle's critical parametrs. Could you
> please help.
>
> (the init.ora file is the one created by Oracle. Other than the listed
> params, we didn't touch any thing else).
>
> thanks a lot!
> Mark

The Oracle guideline is that you shouldn't devote more than one third of physical RAM to the SGA.
On NT 50 percent of the memory will go to the OS anyway.

DB_BLOCK_SIZE = 2048 is a *very bad* idea. You *should* set it to 8k, however you can only do this by recreating your database (export, change the parameter, recreate the database, import) DB_BLOCK_BUFFERS=11700 is way too high, and it is probably helping a bit. You are only forcing the O/S to page. You should determine what the hit ratio is at say 80Mb and if the hit ratio is above 80 percent, leave it alone for a while.
The large pool is only used when you are using MTS, you probably don't do that, so you can just set it to 0, and save memory.

Regards

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address