SGA issue --help rqd
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: SGA issue --help rqd

  1. #1
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87

    Angry

    Folks
    we have Oracle 8.1.5 on Solaris 2.7 on e450 box.
    we are running two instances running on this box
    and total database size is about 1gb..
    we are having around 350 active connections for each databasememory on our server is 2GB.And now as you guys can seeour total SGA on each database is aroung 130MB only
    to increase this i did modified shmmax parameters but still
    my SGA as a whole is still 130MB..could u guys pls give me someidea on how to tune this properly for more SGA...and alsowe dont have any Java applications running and is there any way we can eliminate Java shared pool of 20Mb--or is it mandatory..
    could u guys pls thro some ideas on this pls and help me in tuning
    this db..

    set shmsys:shminfo_shmmax=8053063600
    set shmsys:shminfo_shmmin=1
    set shmsys:shminfo_shmmni=100
    set shmsys:shminfo_shmseg=20
    set semsys:seminfo_semmns=1220
    set semsys:seminfo_semmni=100
    set semsys:seminfo_semmsl=820
    set semsys:seminfo_semopm=100
    set semsys:seminfo_semvmx=32767

    NAME VALUE
    -------------------- ----------
    Fixed Size 64912
    Variable Size 91381760
    Database Buffers 41943040
    Redo Buffers 180224

    db_file_multiblock_read_count = 32
    db_block_buffers = 10240
    shared_pool_size = 83886080
    log_buffer = 163840
    db_block_size = 4096

    thanks
    sat
    sat

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    You're half way there now. You also need to tell oracle how to go out and grab more memory. You will want to look at your init.ora parameters for each instance. The two likely candidates for increase would be "db_block_buffers" (# blocks of db_block_size) and "shared_pool_size" (straight calculation).

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Increase the DB_BLOCK_BUFFER and bounce the database.

  4. #4
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87
    thanks folks
    i did increased the database buffers in initora file
    still doent seen any change in total sga as only dbbuffers were increased
    thx
    sat
    sat

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    The factors that contribute to the size of SGA are

    SGA = log_buffers + shared_pool_size + large_pool_size + java_pool_size + (db_block_buffers * db_block_size)

    Once you make any changes to these parameters, you have to restart the instance to get them to take charge of the new parameters.

    also check the v$sgastat for the distribution of resources.

    Sam

  6. #6
    Join Date
    Oct 2000
    Posts
    449
    so sambavan, can you tell how is SGA done in my db..


    SVRMGR> show sga
    Total System Global Area 559910896 bytes
    Fixed Size 69616 bytes
    Variable Size 498221056 bytes
    Database Buffers 61440000 bytes
    Redo Buffers 180224 bytes
    SVRMGR>


    I know Database buffers only..(db block buffers * blocksize)

    How about defining the rest..

    This question I have asked several times and no one responds. I wondered why..
    Thx.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Total System Global Area 559910896 bytes
    Fixed Size 69616 bytes - OS Dependant , You can't change
    Variable Size 498221056 bytes - Shared Pool Size
    Database Buffers 61440000 bytes - Buffer Cache
    Redo Buffers 180224 bytes - Log Buffer.

    I hope you are clear now.

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    I hope it would be clear now, after tamilselvan had explicitly defined things. Answering to one of your other questions of java_pool_size, the 20M seems to be the default one and you do not want to further reduce that size, b'cos it might cause the problem at the time of starting the instance. If you would like, you can go ahead and increase it, but not reduce it if you have any of the oracle intermedia and other stuff that were installed by default.

    to see whether the java components have been installed in the database, try doing

    describe dbms_java


    Good luck,
    Sam

  9. #9
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87
    Folks,tamil,sam,maris
    Thanks ..to resolve this issue of sga in my db..and most of
    my doubts were cleared now..
    could u guys pls thro some light on FREELISTS/Freegroups
    as we have a table with inserts of 400,000 at a time
    and i observed contentin for dbblock buffers..and came to know we can do it by freelists..as we dont have any on this table..if i have to do this how much shall we give for that table on 8.1.5..and no.of connections on db is 150..
    thanks
    sat
    sat

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