Reduce SGA size - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Reduce SGA size

  1. #11
    Join Date
    Feb 2001
    Posts
    17
    oops!
    sorry...how come copy and paste doesn't work here...





  2. #12
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    I have gone ahead and edited the code for you. In the future if you would like to use the copy and paste option, use the

    [  CODE   ]

    option...

    NOTE: If you feel that I have violated your rights on this forum, by editing your post, please let me know and I'll refrain from doing it.

    Sam

    [Edited by sambavan on 10-03-2001 at 06:18 PM]
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #13
    Join Date
    Feb 2001
    Posts
    17
    Thanks sam..any comments on this output?

  4. #14
    Join Date
    Aug 2001
    Posts
    111
    After reducing the SGA via above steps. All you need to do is make sure that memory use is as efficient as possible.
    If the application using the db has a lot of packages(procedures,functions) check to see what the number of executions is for each and pin the most used into the SGA.
    Why? this will reduce the overhead to reload into memory.

    Have Fun

    roo

    Here are some alternative scripts to check free and freeable memory

    Try this script for SGA

    PROMPT R-free = Shared pool reserved size
    PROMPT R-freea = reserved memory
    PROMPT free = This is the amount of contiguous free memory available
    PROMPT freeabl = Used memory is freeable
    PROMPT perm = Freed memory not yet available
    PROMPT recr = Reserved

    select sum(ksmchsiz) Bytes, ksmchcls Status
    from sys.x$ksmsp
    group by ksmchcls;

    And this for buffer_cache (requires catparr.sql to have been run)

    PROMPT State 0 = Buffers that have NOT been used
    PROMPT State 1 = Buffers that have been used

    select state, count(*)
    from x$bh
    group by state;



    select
    decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state) "BLOCK
    STATUS", count(*)
    from x$bh
    group by
    decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state);
    Performance... Push the envelope!

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