Re: ORACLE 9i - memory usage

Hi,

Apologies if this is a double post...something wrong with usenet..

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 - 01:08:05 CDT