This discussion is archived
1 2 3 4 Previous Next 53 Replies Latest reply: May 1, 2012 10:39 PM by 678290 Go to original post RSS
  • 15. Re: SGA_MAX_SIZE != SGA_TARGET when?
    412527 Newbie
    Currently Being Moderated
    This coincidentally is why I posted this question in the first place. It seems to me that if the RAM is going to get allocated anyway whether I'm using it or not, it's not all that useful to be able to split these two parameters. Why not just default sga_target to sga_max_size and then let me specify minimums for the SGA substructures as needed? It's not like setting a lower SGA_TARGET is going to save OS RAM right?

    The only reason I could think of is maybe I want SGA_TARGET smaller so my buffer cache, etc., are smaller so that I can save some clock cycles on buffer scans, or some such. In this day and age of fast CPU-RAM interconnects and lots of compute power, I'm not sure if we still need to care about doing that, either...
  • 16. Re: SGA_MAX_SIZE != SGA_TARGET when?
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    As I stated originally, my understanding was that the memory the difference provided by
    (SGA_MAX_SIZE - (SGA_TARGET + SGA_FIXED_SIZE + manually set caches) )
    was not to be allocated. And if TARGET was reduced, those sizes were to be returned to the OS to be made available for other SHMEM useage, perhaps by other instances.

    Under this scheme introduced in 10g, the FIXED memory area, which contains the base linked lists to the various pools, was to be allocated to allow growth to the max size.

    I seem to stand corrected. (although OS independance has not been established)


    Metalink notes related, that I'm still digesting, include

    Note:396940.1
    Note:270065.1
    Note:256913.1
    and Note:295626.1 which specifically is relevant to this discussion.

    Basically that leaves 2 SGA_TARGET settings - 0, and 'nearly' SGA_MAX_SIZE' (to accomodate overheads).
  • 17. Re: SGA_MAX_SIZE != SGA_TARGET when?
    51034 Newbie
    Currently Being Moderated
    It depends on your OS.

    If you have an OS that takes SGA_MAX_SIZE from real, physical memory and leaves it there, then there isn't a lot of point in having SMS<ST.

    But not all OSes are Windows or Linux: on Solaris, for example, the unused difference between SMS and ST can be virtualised out of existence until it's needed. If something else needs it in the meantime, then it can use that memory as if it were unallocated. Meanwhile, you've gained yourself the ability to grow your SGA beyond its normal limits when needed, too.
  • 18. Re: SGA_MAX_SIZE != SGA_TARGET when?
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    Thanks Howard. Apparently I am not entirely out to lunch. I knew I'd read, and discussed, the topic the way I presented it ... just did not remember the assumptions.

    As you stated, one assumption is that we are using an OS that actually knows how to disregard/virtualize SHM segments that are not in use. Another feather in the Solaris cap. <g>


    Message was edited by: Hans Forbrich

    Note to self - check whether SHM is allocated in chunks to the processes, something like Oracle's Granules. If so, under Linux, is there an IPC settings combination that allows a process to release a chunk back to OS, or is it an all-or-nothing situation based on the Shmem handle.

    Further note to self - I've had my head way to far up the Linux and Windows path lately. It is time to refresh on the big guys
  • 19. Re: SGA_MAX_SIZE != SGA_TARGET when?
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    That mean our mighty Tom was wrong in this respect.
    Perhaps that means that Hans took mighty Tom's comments out of context, perhaps not using all of the information provided or reading between the lines.
    Let's do an extreme test, set an 8G SGA_MAX_SIZE on my server only have 2G memory. (don't try this at home)
    Perhaps it means that the incredible yingkuan makes certain assumptions about the operation of shared memory and SGA across all operating systems - assumptions that are are not universal. (I know Hans certainly made this mistake!)

    Which operating systems did you test? Please state your assumptions. <g>

    /Hans
  • 20. Re: SGA_MAX_SIZE != SGA_TARGET when?
    247514 Expert
    Currently Being Moderated
    I feel I need to make few notes before proceed.

    1. Even mighty person make mistakes, making mistake doesn't make him unworthy.
    2. Pointing out mighty person's mistake doesn't automatically make you mighty or "incredible"
    3. I was just joking in that post.
    4. At least I did my test and speaking/sharing my expereience based on my observation. However incomplete or wrong it is.
    Perhaps it means that the incredible yingkuan makes
    certain assumptions about the operation of shared
    memory and SGA across all operating systems -
    assumptions that are are not universal. (I know Hans
    certainly made this mistake!)
    Not sure where the Satiric coming from, I was simply making same assumption as you did. By calling me 'incredible', so that making you 'incredible' as well? That make two of us.

    I was testing on Solaris, my testing on Red Hat Linux show different result.
  • 21. Re: SGA_MAX_SIZE != SGA_TARGET when?
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    Not sure where the Satiric coming from, I was simply
    making same assumption as you did. By calling me
    You have provided a lot of useful information in your posts, and I do find I have learned a lot from them. Your posts are always on my 'to read' list.

    Message was edited by: Hans Forbrich

    The satire came from responding a 3 AM ... after being awakened by a cat jumping on me. Unfortunate, perhaps.
  • 22. Re: SGA_MAX_SIZE != SGA_TARGET when?
    Daljit Explorer
    Currently Being Moderated
    I was testing on Solaris, my testing on Red Hat Linux show different result.
    So are you saying on redhat its not using the memory specified by SGA_MAX_SIZE at startup?

    Daljit Singh
  • 23. Re: SGA_MAX_SIZE != SGA_TARGET when?
    247514 Expert
    Currently Being Moderated
    Wait a second, I made a mistake in my Linux testing.
    I didn't set sga_max_size explicitly in my spfile file, so it automatically equaled with sga_target.

    After I set it to say 900M and leave sga_target as 600M, the result suport my previous observation.

    Oracle will allocate amount of memory equal to SGA_MAX_SIZE from OS.
    -- Assuming you set it explicitly in spfile
    SYS@azdev > show sga

    Total System Global Area  943718400 bytes
    Fixed Size                  2077264 bytes
    Variable Size             490737072 bytes
    Database Buffers          444596224 bytes
    Redo Buffers                6307840 bytes
    SYS@azdev > show parameter sga

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    lock_sga                             boolean     FALSE
    pre_page_sga                         boolean     FALSE
    sga_max_size                         big integer 900M
    sga_target                           big integer 600M
    ##########################################
    ------ Shared Memory Segments --------
    key        shmid      owner      perms      bytes      nattch     status     
    0xc5b3cb58 6946840    oracle    640        945815552  22                     
    ##########################################
    uname -a
    Linux 2.6.9-42.0.8.ELsmp #1 SMP Tue Jan 23 12:49:51 EST 2007 x86_64 x86_64 x86_64 GNU/Linux
  • 24. Re: SGA_MAX_SIZE != SGA_TARGET when?
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    Nice verification. Under Linux, SGA_MAX_SIZE is pre-allocated at startup.
  • 25. Re: SGA_MAX_SIZE != SGA_TARGET when?
    247514 Expert
    Currently Being Moderated
    Hi Hans,

    I admit making premature assumption is one of my bad habit, it's perticularly bad for a DBA. Thanks for point that out.

    Did you find any OS that will not preallocate SGA_MAX_SIZE?
    I only get to test Solaris and Linux, our HP is production so no good for testing.
    I don't have any Windows installation apparently.
  • 26. Re: SGA_MAX_SIZE != SGA_TARGET when?
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    Hi Hans,

    I admit making premature assumption is one of my bad
    habit, it's perticularly bad for a DBA. Thanks for
    point that out.

    Did you find any OS that will not preallocate
    SGA_MAX_SIZE?
    Based on my current research, Solaris (and perhaps AIX) may be configured so that when pages are not used, the pre-allocation does not use physical memory.

    Therefore under Solaris 10 - and I still need to verify this myself - the real memory requirement will be managed by SGA_TARGET + SGA fixed overhead + SGA manual sub-pools.

    This definitely does NOT work under Linux and Windows.

    Message was edited by: Hans Forbrich

    Please holder for various sites I find during the investigation

    http://www.ixora.com.au/q+a/0107/04120647.htm
    http://www.freelists.org/archives/oracle-l/03-2007/msg00903.html

    I think this is critical to the discussion: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b15658/appe_sol.htm
    This same discussion elsewhere: http://www.webservertalk.com/archive149-2004-8-333787.html
  • 27. Re: SGA_MAX_SIZE != SGA_TARGET when?
    51034 Newbie
    Currently Being Moderated
    Thanks for that first link to the "Administering Oracle Database on Solaris" article, Hans.

    I have been saying 'it depends on your OS and Solaris <> Linux in this regard' for a long time, but I had completely forgotten where I got that from. Your link reminds me that I am not just making it up!
  • 28. Re: SGA_MAX_SIZE != SGA_TARGET when?
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    Current conclusion (not verified as I currently do not have available test systems):


    SGA_MAX_SIZE will be allocated from the Shared Memory on all operating systems. This will apparently always show up under the ipcs size.

    However, some operating systems allow the Shared Memory to be unlocked, or not pinned in real memory and handled in chunks or pages. Under these situations, the unused amount may be either "not physically allocated" or "placed on swap device" - eg: DISM in Solaris. In these cases, the unused amount does not use RAM.

    So when [(SGA_TARGET + fixed SGA + manual SGA (keep, recycle pool, etc.) + manual minimums for automatic cache) < SGA_MAX_SIZE], it is possible that physical RAM used may be less than SGA_MAX_SIZE, even though SGA_MAX_SIZE is actually allocated to IPC.

    Therefore, on DISM environments, SGA_TARGET could theoretically be used to reduce physical RAM requirements.


    Is this good or bad for performance? ... I am not even going there without a significant test box.

    Whew!
  • 29. Re: SGA_MAX_SIZE != SGA_TARGET when?
    247514 Expert
    Currently Being Moderated
    Thanks for the link and conclusion. It's pretty educational.

    The Solaris I was testing on certainly doesn't have ora_dism_<sid> running.