Re: huge sga

The question is 'if a 1.2G SGA is too big'. The answer depends on how much real memory does your machine have, and how much is left for user processes?

In general if you start with a small SGA you can keep increasing it in size until you start to incur paging and swapping that was not present at the previous size. Swapping is very undesirable. Remember that all intermediate query results for a user session are stored and processed in OS memory. It does little good to create a giant SGA if you are crowding out everything else.

This is a general statement, but your SGA should probably not exceed 1/2 of real memory, and in most cases should be much smaller that this. Before 7.3 with its multiple buffer pool chains there were studies that Oracle did not make efficient use of SGA's over 250M because it took longer to search the buffer pool that to do an I/O to get the buffer. Now if your SGA was large mostly to support packages then this may not apply, and 7.3 has multiple search lists to support larger buffer pools. The shared pool seems to be largely accessed via hash so increased size should not hurt here either. I think it comes down to, how much memory do you need to support the user sessions and non-Oracle applications? The rest you can give to your SGA. You can monitor v$bh to see how you are using your buffer pool and there are several v$ tables for the shared pool like v$sgastat and v$object_cache.

We can support 200 connected users in an OLTP environment with around 80G of table and index data plus 40G of rollback, temp, system, etc... using an SGA in the 160M range. The buffer pool is a little under 1/2 of the SGA. I have used sar -r to watch free memory and swap usage and during peek times we definitely use most all of memory.

Beverly Hom <bhom_at_sprintmail.com> wrote in article >>...
> You really need to look at how your database will be used. The shared
pool
> contains > shared cursors and stored procedures. Larger values improve
performance in multi-user systems. Smaller values use less memory. Therefore, if you are going to utilize stored procedures or your users will execute the same sql over and over, it is a good idea to have a large shared_pool_size. You can see how your system is performing by querying some of the V$ tables, for example, the v$sesstat table.
>
> Ed Lufker wrote:
>
> > Hi All;
> >
> > Does a shared pool size of 150000000 and db_block_buffers set
to
> > 150000 seem unrealistic for a db that has over 2000 indexes and about
450
> > tables. This creates about a 1.2GB sga.
> >
> > thanks for any help here.
> > eddie lufker
>
>
>
>
Received on Mon Jun 08 1998 - 09:04:22 CDT