Re: Maximum value for db block buffers

In article <376CF425.2927_at_yahoo.com>,
  connor_mcdonald_at_yahoo.com wrote:
> Purely a philosophical question...
>
> Given sufficient physical memory - is there a limit on db block
buffers
> where Oracle would start struggling with managing them...?
>
> Cheers

For NT, here is some information. There is also a Microsoft document that talks about this: Getting Started Guide for Windows NT Microsoft Article ID: Q171793

The maximum value is limited only by how much address space you have in your Oracle process and how much memory you have in the machine.

It also depends on your Windows NT Service Pack. Prior to Windows NT v4.0 SP3, you were limited to 2GB of address space per process (SP>=3 limit is 3GB), so.....

max db_block_buffers == (2 GB - code size - stack space)/db_block_size
                     == 1.98 GB / db_block_size  [roughly]
                     == 990,000 for db_block_size of 2K

In reality, you cannot allocate the maximums because you will have no memory left for PGAs, shadow processes, sort areas, etc.. Therefore, these are theoreticals and not completely usable.

Also, Due to address space fragmentation, it is often not possible to utilize the maximum available address space. If you try to allocate a huge SGA, you may get failures even if you have enough memory (not enough memory errors!!!!!).
Using the ORA_SGA_MAX_ALLOC (set very large!)registry variable will tell the RDBMS to allocate the SGA in pieces, which effectively works around the address space fragmentation problem and allows you to allocate more db_block_buffers.
Hope this helps

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jun 21 1999 - 10:14:56 CDT