Re: ORACLE 9i - memory usage

Just asking...

why would you need a 6GB Shared Pool ???? thats a hell a lot of memory to waste for SQL statements, PL/SQL objects...even for the biggest application in the world!

you won't have to worry much about memory if you do something about your shared_pool_size

cheers
ORA600 ORA600 wrote:
> Hi,
>
> I would say that Oracle has to allocate or "occupy" or reserve 20GB of
> virtual address space from the OS since you have explicitly set that
> value for sga_max_size.
>
> Oracle allocates 13GB (which is the total size of your SGA) at startup
> and then allocate the rest of the 7GB on demand when you resize your
> SGA.
>
> 20GB is therefore reserved within the virtual address space and since
> the physical memory has not yet been allocated, 20GB will not show up
> within physical memory. It will ONLY show up in virtual memory address
> space.
>
> Only 13GB of shared memory is occupied at start. no other external
> process will be able to address any part of this 20GB memory since it
> is owned by Oracle.
>
> This is why you see a single 13GB shared memory segment with your ipcs
> command. Whenever you want to increase the SGA size, Oracle can do so
> by bringing in the required memory into its physical address space.
>
> Without this "reserve" feature, there wouldn't be any point in having a
> sga_max_size.
>
> On HPUX, glance tool can show you the process address space. it should
> be visible from there. i can't remember the HPUX equivalent of pmap...
>
> What does this query show you?
>
> select * from v$SGA_DYNAMIC_FREE_MEMORY
>
> the output must show approximately 7GB of space available for
> expansion.
>
> Why don't you try expanding the db_cache_size by another 6GB and see
> the results?
>
> that should clearly give you the answer...
>
> hope that helps.
>
> cheers
> anand
>
>
> Dave wrote:
> > Thanks to all for attending this discussion.
> >
> > Firstly let me add some information...
> >
> > ipcs shows me the shared segment for this db:
> > m 826383 0x43fe1ba4 --rw-rw---- oracle dba 12903632896
> > show sga shows me it is the same than sga:
> > Total System Global Area 1.2887E+10 bytes
> > Fixed Size 754680 bytes
> > Variable Size 6492782592 bytes
> > Database Buffers 6392119296 bytes
> > Redo Buffers 1167360 bytes
> >
> > 1) Can I guess it is actually allocating 13Gb of memory?
> >
> > Jagjeet, your tip related to 9i limitation is Very interesting:
> > unfortunately the Oracle note you mentioned is related to Solaris (I am
> > working with hpux 11.11) and it does not apply to 9.2 (I have 9.2.0.7).
> >
> > I have tried without success to find in metalink something related to
> > hpux: do you have information about the same problem on this OS ?
> >
> > 2) A correct way to confirm if my db is allocating the sga or the
> > SGA_MAX_SIZE of memory could be to check how much memory is actually
> > allocating.
> > What is the correct way to evaluate the current memory used by an
> > instance?
> > Looks like V$PROCESS does not give the correct picture:
> > pga_used_mem = 17298780
> > pga_alloc_mem = 119051092
> >
> > Thank you again,
> >
> > Dave
Received on Wed May 31 2006 - 05:36:40 CDT