Re: DB block buffers and Shared Pool Size on Oracle 7.3 Windows NT 4.0

Try looking at the sorts that are happening. If the sort area size is default i.e. 64k then this may be a problem. Try the following code

select sn.name, ss.value
from v$sysstat ss, v$statname sn
where ss.statistic#=sn.statistic#
and sn.name like '%sort%'
/

If the number of sorts on disk are high then increase sort_area_size (init.ora)
and set sort_area_retained_size=65536

try
sort_area_size=1000000 (about a meg and increase if need )

Then restart the db. Examine the sorts again with the sql above once the database has been in use for a while.

The sort area size affects every user so setting the retained size to the old default of 64k ensures that users only use the memory if they need it.

Dan,

xsun1913_at_my-dejanews.com wrote in message <71cl0g$nhs$1_at_nnrp1.dejanews.com>...
>In article <909742367.18183.0.nnrp-11.c2ded24f_at_news.demon.co.uk>,
> "Dean Cunningham" <drcunningham_at_bewiseltd.demon.co.uk> wrote:
>> I am currently running Oracle 7.3 on a dedicated AlphaServer 4100 with
1.5GB
>> of memory.
>> This machine has only one instance and the physical database is around 30
GB
>> with over 60 users and large batch runs.
>> The DB block size was set to 4k and the number of block buffers is set to
>> 24000 resulting in a cache size of around 90 Mbytes.
>> The shared pool size is set to 250 Mbytes. When using NT to monitor the
free
>> memory at the operating system level it reports that around 1.1 GB is
free
>> which you would expect. The data dictionary caches are still 100% full
and
>> there are still misses.
>
>If the dictionary cache hit ratio is over 95% (of course, there are still
>misses), the performance is already very good.
>
>>There is still over 140 Mbytes of free memory in the
>> SGA area so why is this database trying to conserve memory when the
>> dictionary cache needs more memory. Is there something in oracle that
will
>> not allow
>> the dictonary caches to use say more than 10% of the SGA.
>>
>> We have been told by the suppliers of the oracle database that we
shouldn't
>> change the number of buffers or the shared pool size as they are
considered
>> massive already considering the size of the database.
>>
>> Ideally I would like to change the number of block buffers to 80,000 and
the
>> shared pool size to at least 512 Mbytes to at least use some more memory
and
>> hopefully achieve a bit more perfromance.
>
>Increasing the db_block_buffers won't help your case. But increasing the
>shared_pool_size will. In our case, we had buffer cache hit ratio greater
>than 99%. We reduced the size of buffer cache and increased the size of
>shared pool.
>
>> Is there some internal limits in oracle or N.T. that would damage the
>> database if these changes were made.
>>
> No.
>
>> I have heard of some companies running oracle on Windows N.T. with 4 GB
>> memory. Surely they cannot be having the same problems and only using 300
>> Mbytes.
>>
>> Any feedback on the above would be greatly appreciated.
>
>We are running three instances and Oracle Financials on an NT box (a
Compaq)
>with two CPUs and 520MB physical memory, over 20 concurrent users. The
>performance is pretty good.
>
>
>Xuening Sun
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Sun Nov 01 1998 - 12:39:31 CST