Reduce SGA size
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Reduce SGA size

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    17

    Exclamation

    I found we allocated to my memory to SGA compared to its physical memory. Total SGA size is about 224M and physical RAM is 256.
    Can I reduce size of shard_pool_size and db_block_buffers in the parameter file and Do I need to shut down DB?
    And also is there any concerns before I perform this task?

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Do a shutdown immediate and change the shared_pool_size and db_block_buffers and then restart the instance ... Once this is done monitor your library cache hit and buffer catch hit ratios

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    If you want to understand better the SHARED POOL you need to know better X$KSMSP.

    One common DBA mistake is oversizing the Shared Pool.


  4. #4
    Join Date
    Feb 2001
    Posts
    17
    How can I see X$KSMSP table?
    and what is it?
    Please explain why I have to know about it and what kind of information the table include...etc.
    Thanks,

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Code:
    SQL> select ksmchcom contents, count(*) chunks,
      2  sum(decode(ksmchcls, 'recr', ksmchsiz)) recreatable,
      3  sum(decode(ksmchcls, 'freeabl', ksmchsiz)) freeable,
      4  sum(ksmchsiz) total
      5  from x$ksmsp
      6  where ksmchcls not like 'R%'
      7  group by ksmchcom;
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    That's it Sam!

    Note that each chunk is a bit larger than the containing object for there is a 16 byte header in which some info is stored: type, class and size of the chunck, etc.

    Free chuncks: they do not contain valid objects.
    Recreatable chunks: they contain objects that may be temporaly removed and then recreated.
    Freeable chunks: they contain objects need during the session and then freed. They are not recreatable, hence they cannot be temporaly removed from memory.

    Sam did not mention the perminant chunks, they contain the so called persistant objects.



  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    try http://www.ixora.com.au for better understanding of Oracle Internals

  8. #8
    Join Date
    Feb 2001
    Posts
    17
    'No rows selected' after querying the statement Sam gave me.
    What does that mean?

  9. #9
    Join Date
    Nov 2000
    Posts
    245

    login as user sys then try it

  10. #10
    Join Date
    Feb 2001
    Posts
    17
    Could you give me any comments on this output below?

    Code:
    CONTENTS         CHUNKS RECREATABLE  FREEABLE     TOTAL
    ----------------     ---------     -----------      ---------       --------
    KGFF heap                  11         1296             5688         6984
    
    KGK contexts                2              *              2376         2376
    
    KGK heap                     3           8772             560        9332
    
    KGL handles           24514      7887248                *         7887248
    
    KQLS heap                 145          66140        92944       159084
    
    PL/SQL DIANA             18            4428         26912        31340
    
    PL/SQL MPCODE          53           16156         76212       92368
    
    PLS cca hp desc             1                 *             164          164
    
    PLS non-lib hp               1             2096                *           2096
    
    character set m             6                   *          28684      28684
    
    dictionary cach           142                  *         307432     307432
    
    fixed allocatio              18              576                  *            576
    
    free memory            6771                  *                 *     676992
    
    kzull                            32                  *             1588       1588
    
    library cache           57143       8178392       14278752  22457144
    
    multiblock rea                1                  *              2064        2064
    
    permanent memor        16                  *                 * 17075672
    
    row cache lru               24             1064                 *            1064
    
    session param v           24                  *            69216     69216
    
    sql area                  45422      5585220        49729024  55314244
    
    table columns               17          15248                   *        15248
    
    table definiti                  2              620                   *           620
    
    trigger defini                 2             1072              1072      2144
    
    23 rows selected.
     I took the liberty to edit the thread, Sam 

    [Edited by sambavan on 10-03-2001 at 06:11 PM]

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