Forgot your password?    
+ Reply to Thread
Results 1 to 5 of 5

Thread: Sga_target vs sga_max_size

  1. #1
    Oracle DBA
    Join Date
    Oct 2011

    Sga_target vs sga_max_size

    SGA_TARGET specifies the total size of all SGA components. If the SGA_TARGET is set, then the following memory pools are automatically sized :
    • Buffer cache (DB_CACHE_SIZE)
    • Shared pool (SHARED_POOL_SIZE)
    • Large pool (LARGE_POOL_SIZE)
    • Java pool ( JAVA_POOL_SIZE)
    SGA_MAX_SIZE specifies the hard limit upto which the SGA_TARGET can dynamically grow. While executing DBCA, Oracle suggests that the estimated SGA_MAX_SIZE is to set aside 40% of memory. However, it should be set according to your requirement that depends on multiple factors such as no of concurrent users, volume of transactions and growth rate of database. Under normal operation, you can set the SGA_MAX_SIZE equals to the SGA_TARGET. Sometimes, we need to perform some extra-heavy batch processing jobs that leads to more SGA size. At this circumstance, you must have capability to adjust for peak loads. That is why, you set hard limit for your SGA_MAX_SIZE.

    SGA_MAX_SIZE cannot be changed dynamically without bouncing the database whereas SGA_TARGET can be changed dynamically without bouncing the database.

    If you try to modify SGA_MAX_SIZE dynamically, you will get an error of
    ORA-02095: specified initialization parameter cannot be modified.

    SGA_TARGET can never be greater than SGA_MAX_SIZE. If you try to set the SGA_TARGET to a value which is greater than that of SGA_MAX_SIZE, then Oracle will throw an error of
    ORA-02097: parameter cannot be modified because specified value is invalid
    ORA-00823: specified value of SGA_TARGET greater than SGA_MAX_SIZE.

    If the SGA_MAX_SIZE is not set and the SGA_TARGET is set, then the SGA_MAX_SIZE takes the value of SGA_TARGET.

    If you set the SGA_MAX_SIZE greater than your server memory capacity and bounce the database, you will get an error of
    ORA-27102 : out of memory
    SVR4 Error : 12 : not enough space

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    New Delhi, India
    Very good post Nemat. Very comprehensive

  3. #3
    really gud post Nemat

  4. #4
    Oracle DBA
    Join Date
    Oct 2011
    its..nice post..

  5. #5


    Very Nice Post...

+ Reply to Thread

Tags for this Thread

Posting Permissions

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

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training

Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics