thumb rule on SGA SIZE
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: thumb rule on SGA SIZE

  1. #1
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    38

    thumb rule on SGA SIZE

    Hi All,
    What I want to ask is...Is there ia thumb rule depending on various factors..like Database size(X GB)..and on number of transactions... I determine the size of SGA...

    Warm Regards,

    Paddy

  2. #2
    Join Date
    Dec 2002
    Location
    India & Australia
    Posts
    68
    Hi,

    Yeah it all starts with the creation of database infact.

    You need to ANLAYZE everything before you create, as it may be frequently used tables under which indexes, data realated, other objects which relates to be created especially the above 8i versions will provide such packages (as may require).

    You need to apply all these structures while anlyzing them. subsequntly your SGA size will depend on it.

    Normally several DBA's will go based on the DB BLOCK size on which the SGA will be created.

    Still it varies practically it implies at the site (your work place) requirements of the SGA size.

    Hope I have shared my view.

    Good luck paddy.

    VAST

  3. #3
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    38
    Hi Again,

    Thanks for the reply...
    But the thing is that, I want to predecide the size of SGA without even looking at the Server..depending on the Size of SGA and knowing the apprximate traffic at the server...how do I determine if the Size of SGA is Ok or not...

    This might sound werid...but some situations do crop up like this in reality....

    Thanks in advance,

    Paddy

  4. #4
    Join Date
    Aug 2001
    Posts
    36
    There is no thump rule. Try to devote as much as space you will be able to for SGA. then depending upon the statistics you can increase or decrease the size

    Regards,

  5. #5
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    My "rule of thumb" is that when nothing else is running on the server except Oracle, then Oracle can receive around 35-40% of the machine´s memory MAXIMUM.

    You may want to use the Buffer Cache Hit Ratio to determine if you have allocated enough DB Block Buffers. You should shoot for 95% or ideally 99% if the resources are available.

    The large pool needs resources when you use RMAN for backups or you are using MTS.

    The shared pool needs to be larger when you have a lot of packages and stored functions and procedures. For performance reasons, you might want to pin packages.

    If you are using 9i, you can resize the Shared Pool dynamically.

    If you give the specifics of your environment, perhaps someone will give you a better or more precise answer.
    David Knight
    OCP DBA 8i, 9i, 10g

  6. #6
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    38
    Hi dknight,

    Thanks for that eloborate reply....
    Most of the things you have pointed is avaliable in most of the documents I have gone through....
    What I wanted to know actually is that by experience have anybody been able to come to a approximation that if an (X GB) of database on which the number of transactions takeing place per second is Y ..then in any way could they relate it to the size of SGA...and moreover if the response time was T then approximately how much the SGA size has to be increased...etc...

    Actually here I dont need the specifics by going in details of making a querry on to the Database V$ views...

    I guess i could put my point...

    Thanks in advance,

    Paddy

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    with your approach, start guessing and good luck

  8. #8
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    38
    Whoops....thanks pondo..

  9. #9
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Trial and Error is probably the best approach.
    David Knight
    OCP DBA 8i, 9i, 10g

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by paddy04
    Whoops....thanks pondo..
    pando plz

    serious, how do you suppose to estimate something if you cant access the database?

Posting Permissions

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



Click Here to Expand Forum to Full Width