Results 1 to 2 of 2

Thread: SGA advice

  1. #1
    Join Date
    May 2009
    Location
    US
    Posts
    29

    Unanswered: SGA advice

    I've been asked to comment on whether the following SGA memory allocations are sized correctly for our various ORACLE production databases...
    Buffer Cache, Shared Pool, Java Pool

    I've retrieved the sizes of these memory sections by running the following query for each database:

    select
    name, bytes/1024/1024 "MB", resizeable
    from
    v$sgainfo

    This tells me what they're currently set at.
    In order to determine what they should be set to i've resorted to looking at the
    "Resource Advisors" present in ORACLE Enterprise Manager Console.
    (i used the "Buffer Cache Size Advisor" and the "Shared Pool Size Advisor")

    These advisors plot a graph showing the effects of more and less memory - compared to what its currently set to - and indicates how that affects I/O.
    This indicates whether one should add more memory or take away some.
    As far as i know, these "advisors" are dependant on the ORACLE statistics being up to date?

    In any event, i was wondering how else i could go about evaluating the current settings for these memory allocations without using these advisors?
    I'm assuming there's a "real DBA" alternative?

    I'm a newbie by the way, so this is part of a learning experience for me, which probably explains why i've been told to not use the advisors for this.

    So how can i otherwise evaluate these settings??



    The next thing i need to know is:
    What factors should one take into account when providing new estimates?

    Obviously if one simply used the advisors then the accuracy of those estimates are dependant on how up to date the database statistics are?

    Any advice here would be most welcome.


    Next question i have is:
    What is the cost of over allocation?
    To me it is obvious - it just means that there will be less memory left to distribute to other areas such as the PGA, and other parts of the SGA.
    Anything else i'm missing here?


    Next question is:
    Can this [over allocation] be reversed easily?
    i'd say YES - just change the settings back to whatever you like.
    Granted, any changes made will only be to the spfile and one would have to bounce the database to have the changes taken up.
    Any comments appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> select
    name, bytes/1024/1024 "MB", resizeable
    from
    v$sgainfo  2    3    4  ;
    
    NAME					 MB RES
    -------------------------------- ---------- ---
    Fixed SGA Size			 2.11110687 No
    Redo Buffers			   7.234375 No
    Buffer Cache Size			272 Yes
    Shared Pool Size			496 Yes
    Large Pool Size 			 16 Yes
    Java Pool Size				128 Yes
    Streams Pool Size			 32 Yes
    Shared IO Pool Size			  0 Yes
    Granule Size				 16 No
    Maximum SGA Size		 1481.34766 No
    Startup overhead in Shared Pool 	 80 No
    
    NAME					 MB RES
    -------------------------------- ---------- ---
    Free SGA Memory Available		528
    
    12 rows selected.
    Should these values be adjusted?
    To what new values & why?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •