Forum Stats

  • 3,851,556 Users
  • 2,263,999 Discussions
  • 7,904,778 Comments

Discussions

SI database -> RAC database: estimation for SGA size for each instance

2737092
2737092 Member Posts: 3
edited Aug 14, 2015 4:00AM in Real Application Clusters

Hello!

Let's say there is a single-instance DB with 100G SGA size (suppose this size is optimal for that database).

If that database is intended to be converted to RAC database with 2 instances (assume DB workload will be evenly distributed across instances),

how to estimate SGA size requirements for each instance in this RAC database?

Should it be 50G + RAC overhead (let's say +15%) for each instance?

(Don't consider situation when one of instances has failed, and all workload is redirecting to survived instance)

I would appreciate if somebody suggested or provided some useful references on this topic.

---

Regards,

Kyrylo

Tagged:

Best Answer

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Aug 13, 2015 3:39PM Answer ✓

    As a rule of thumb, I would size the SGA's on the new nodes to be 120% of the single-instance SGA. Yes, this means that you will have more than double the SGA size. You'll need additional space to support Cache Fusion overhead. And the Buffer Cache may need a bit more space to store extra versions of blocks that seem to accompany many RAC implementations. Now this is just a rule of thumb, so it has a high degree of being very wrong.

    You will want to consider two big things...1) the services being employed. Are you going to leverage services to perform application partitioning? If so, then the instances may see different workloads which can lead to different resource demands. 2) Will you be supporting some sort of application failover (i.e. TAF or manual failover)? If so, then the SGA needs to be sized adequately to support the additional workload.

    Cheers,
    Brian

Answers

  • SPA09
    SPA09 Member Posts: 866 Gold Badge
    edited Aug 13, 2015 8:28AM

    Hi,

    here are few tips.

    https://docs.oracle.com/cd/E11882_01/rac.112/e41960/admcon.htm#RACAD1111

    SGA

    Performance guidelines must be established in site-specific SLA's based on business and availability requirements and associated workload measured as part of application and system testing to ensure adequate system performance and response times are delivered.
    · The recommendation for sizing the buffer cache in a RAC environment is similar to a single node where the same tuning procedures would apply.  Although actual memory requirements will depend on application workload, the general recommendation is approximately a 10% increase in buffer cache to accommodate scale up requirements (example: 1 node - 1000 users, 2 nodes - 2000 users).
    · Overall SGA sizing requires workload testing specific to each RAC deployment, as application and user workload requirements will vary based on these requirements.

    regards

  • 2737092
    2737092 Member Posts: 3
    edited Aug 13, 2015 9:36AM

    Thank you for help!

    I red this excerpt from the document, you provided me with, some time ago, but it says that  SGA should be increased and I don't understand why.

    OK. If it's correct, consequently, for example, in the case of [Source: SI DB with SGA 100G],  we get for 4-node RAC database: Node1 - approx. 100G, Node2 - 100G, Node3 - 100G, Node4 - 100G.

    Total amount of RAC SGA memory: 400G vs 100G in case of Single-Instance db.

    If there is 8 nodes in RAC, total amount of SGA according to the doc is 800G.

    It seems to be strange, at least.

    Af far as I understand how does Global Cache work, a buffer can exist only in buffer cache of one instance  in any moment of time.

    So, in assumption that database workload will be distributed evenly across all instances in the cluster database, and  there are no changes at application level,
    for what reason should we multiply SGA size for SI database by the number of instances.

    Best Regards,

    Kyrylo

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Aug 13, 2015 3:39PM Answer ✓

    As a rule of thumb, I would size the SGA's on the new nodes to be 120% of the single-instance SGA. Yes, this means that you will have more than double the SGA size. You'll need additional space to support Cache Fusion overhead. And the Buffer Cache may need a bit more space to store extra versions of blocks that seem to accompany many RAC implementations. Now this is just a rule of thumb, so it has a high degree of being very wrong.

    You will want to consider two big things...1) the services being employed. Are you going to leverage services to perform application partitioning? If so, then the instances may see different workloads which can lead to different resource demands. 2) Will you be supporting some sort of application failover (i.e. TAF or manual failover)? If so, then the SGA needs to be sized adequately to support the additional workload.

    Cheers,
    Brian

  • JohnWatson2
    JohnWatson2 Member Posts: 4,487 Silver Crown
    edited Aug 13, 2015 4:09PM
    2737092 wrote:
    
    Thank you for help!
    
    <snip>
    
    Af far as I understand how does Global Cache work, a buffer can exist only in buffer cache of one instance  in any moment of time.
    <snip>
    
    Best Regards,
    Kyrylo
    

    This statement above is not correct, and may be the key to your misunderstanding.

    One block may be cached in any and every instance. If you are distributing your sessions across the instances and they are all doing the same work, the contents of the caches will tend to stabilize with copies of the same blocks everywhere. Furthermore, because of the way the cache fusion mechanism works, you may have several versions of blocks cached by each instance.

    2737092
  • 2737092
    2737092 Member Posts: 3
    edited Aug 14, 2015 4:00AM

    Many thanks for your help, colleagues!

    Regards,

    Kyrylo

This discussion has been closed.