1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

I have query about SGA Size

Discussion in 'Server Administration and Options' started by kimipatel, Jul 18, 2013.

  1. kimipatel

    kimipatel Active Member

    Messages:
    53
    Likes Received:
    1
    Trophy Points:
    140
    Hi,

    I want to increase my shared pool. my SGA values are as under below.
    please tell me the steps. So i can do it.

    SELECT NAME,SUM(VALUE)/1024/1024/1024 gb FROM V$SGA
    GROUP BY NAME;

    Database Buffer 4.1125
    Redo Buffers 0.0682823911015625
    Variable Size 18.0000001564622
    Fixed Size 0.00203248294210434

    Thanks & regards,
    kimipatel
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,344
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I am guessing this was an interview question, or one you expect to be asked in an upcoming interview. WHy did you not use the 'show sga' command in sql*plus? It provides more readable information.

    The online documentation at http://tahiti.oracle.com is a good source for such questions.
     
  3. kimipatel

    kimipatel Active Member

    Messages:
    53
    Likes Received:
    1
    Trophy Points:
    140
    Hi Zargon,

    Thanks for the Your reply to me. I am not asking for Interview.
    How i can find memory_max_target and memory_target values & status(enable or disable)?

    Thanks & regards,
    kimipatel
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,344
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is how to see those parameters and their currently set values:

    Code (SQL):
    SQL*Plus: Release 11.2.0.3.0 Production ON Mon Jul 22 07:51:27 2013
    Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.

    Connected TO:
    Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
     
    SQL> SHOW parameter memory
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    hi_shared_memory_address             INTEGER     0
    memory_max_target                    big INTEGER 800M
    memory_target                        big INTEGER 800M
    shared_memory_address                INTEGER     0
    SQL>
    If you set one and do not set the other (that is, if you set memory_target yet do not set memory_max_target, for example) both parameters are assigned the same value. And, as in the case shown above, they are the same then memory_target is NOT dynamic and will require a restart of the database to effect the changes. If you also happen to have sga_max_size set then any setting that puts memory_max_target lower than sga_max_size will cause startup to fail:

    Code (SQL):
    SQL> ALTER system SET memory_target=600M scope=spfile;
     
    System altered.
     
    SQL> startup force
    ORA-00844: Parameter NOT taking MEMORY_TARGET INTO account
    ORA-00851: SGA_MAX_SIZE 838860800 cannot be SET TO more than MEMORY_TARGET 629145600.
    SQL>
    At this pomt ot will be necessary to generate a pfile from your spfile at the operating system level;; for Unix and Unix-like systems this will create that file; the example is for a database named TEST:

    $ cd $ORACLE_HOME/dbs
    $ strings spfiletest.ora > inittest.ora

    You will now need to edit the inittest.ora file and comment out the sga_* parameter lines using #:

    test.__db_cache_size=297795584
    test.__java_pool_size=4194304
    test.__large_pool_size=4194304
    test.__oracle_base='C:\app\fitzjarrell.david\oradb'#ORACLE_BASE set from environment
    test.__pga_aggregate_target=293601280
    #test.__sga_target=545259520
    test.__shared_io_pool_size=0
    test.__shared_pool_size=226492416
    test.__streams_pool_size=0
    *.audit_file_dest='C:\app\fitzjarrell.david\oradb\admin\test\adump'
    *.audit_sys_operations=TRUE
    *.audit_trail='DB'
    *.compatible='11.2.0.0.0'
    *.control_files='C:\app\oradb\oradata\test\control01.ctl','D:\app\oradb\oradata\test\control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_name='test'
    *.diagnostic_dest='C:\app\oradb'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
    *.fast_start_mttr_target=32
    *.memory_target=629145600
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.result_cache_mode='AUTO'
    #*.sga_max_size=838860800
    *.undo_tablespace='UNDOTBS1'

    Save the inittest.ora (or whatever your init file is named) and use it to start the database:

    Code (SQL):

    SQL> startup pfile="?/dbs/inittest.ora"
    ORACLE instance started.
     
    Total System Global Area  626327552 bytes
    Fixed SIZE                  2257792 bytes
    Variable SIZE             373296256 bytes
    DATABASE Buffers          243269632 bytes
    Redo Buffers                7503872 bytes
    DATABASE mounted.
    DATABASE opened.
    SQL>
     
    To maintain those changes across database restarts you will need to create a new spfile:

    Code (SQL):
    SQL> CREATE spfile FROM pfile;
     
    File created.
     
    SQL>
    Test your new spfile:

    Code (SQL):
    SQL> startup force
    ORACLE instance started.
     
    Total System Global Area  626327552 bytes
    Fixed SIZE                  2257792 bytes
    Variable SIZE             373296256 bytes
    DATABASE Buffers          243269632 bytes
    Redo Buffers                7503872 bytes
    DATABASE mounted.
    DATABASE opened.
    SQL>
    Now your memory parameters are set:

    Code (SQL):
    SQL> SHOW parameter memory
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    hi_shared_memory_address             INTEGER     0
    memory_max_target                    big INTEGER 600M
    memory_target                        big INTEGER 600M
    shared_memory_address                INTEGER     0
    SQL>
    Of course if you want to enable dynamic memory sizing then both parameters need distinct values:

    Code (SQL):
    SQL> ALTER system SET memory_target=600m scope=spfile;
     
    System altered.
     
    SQL> ALTER system SET memory_max_target=800m scope=spfile;
     
    System altered.
     
    SQL>
    Again, you will need to restart your database:

    Code (SQL):
    SQL> startup force
    ORACLE instance started.
     
    Total System Global Area  835104768 bytes
    Fixed SIZE                  2259800 bytes
    Variable SIZE             583009448 bytes
    DATABASE Buffers          243269632 bytes
    Redo Buffers                6565888 bytes
    DATABASE mounted.
    DATABASE opened.
    SQL>
    Notice that the Total System Area is now reported as the 800M memory_max_target is set to. This allows you to dynamically alter memory_target (within reasonable limits) without shutting down and restarting the database:

    Code (SQL):
    SQL> SHOW parameter memory
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    hi_shared_memory_address             INTEGER     0
    memory_max_target                    big INTEGER 800M
    memory_target                        big INTEGER 600M
    shared_memory_address                INTEGER     0
    SQL> ALTER system SET memory_target=700M;
     
    System altered.
     
    SQL> SHOW parameter memory
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    hi_shared_memory_address             INTEGER     0
    memory_max_target                    big INTEGER 800M
    memory_target                        big INTEGER 700M
    shared_memory_address                INTEGER     0
    SQL>
    The memory_target parameter can be set higher or lower than its original value, again, within limits as database buffers are also allocated from that pool and it may be impossible to deallocate them to satisfy the desired memory_target setting:

    Code (SQL):
    SQL> ALTER system SET memory_target=100m;
    ALTER system SET memory_target=100m
    *
    ERROR at line 1:
    ORA-02097: parameter cannot be modified because specified VALUE IS invalid
    ORA-00838: Specified VALUE OF MEMORY_TARGET IS too small, needs TO be at least
    340M

    SQL> SHOW parameter memory
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    hi_shared_memory_address             INTEGER     0
    memory_max_target                    big INTEGER 800M
    memory_target                        big INTEGER 700M
    shared_memory_address                INTEGER     0
    SQL>
    I hope this helps.
     
  5. kimipatel

    kimipatel Active Member

    Messages:
    53
    Likes Received:
    1
    Trophy Points:
    140
    Hi,

    Got it, I set the max according above scenario. Thanks for the help. I got your answer n also understood. It's really appreciate your help.

    Thanks & regards,
    kimipatel
     
  6. jethwa.jignesh@gmail.com

    jethwa.jignesh@gmail.com Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Nasik, Maharastra
    Nice Explanation David.
    Keep it up.