Free space in SGA
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Free space in SGA

  1. #1
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29

    Free space in SGA

    Dear All,

    Is there anyway I can tell how much free space is left in SGA area? For example, if I want to increase the size of the buffer cache or the shared pool, I would like to know how much space in sga is available for me.

    Thanks,

    -- Chris

  2. #2
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    ---- How much memory is left for SHARED_POOL_SIZE: ----

    col value for 999,999,999,999 heading 'Shared Pool Size'
    col bytes for 999,999,999,999 heading 'Free Bytes'

    select to_number(v$parameter.value) value, v$sgastat.bytes,
    (v$sgastat.bytes/v$parameter.value)*100 Percent_Free
    from v$sgastat, v$parameter
    where v$sgastat.name = 'free memory'
    and v$parameter.name = 'shared_pool_size';

    ---------- or ---------------
    select sum(ksmchsiz) Bytes, ksmchcls Status
    from x$ksmsp
    group by ksmchcls
    /



    HTH
    Gregg

  3. #3
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29
    Thanks for the reply. What I want to find out is how much space is left in sga_max_size. So, if I need to increase the size of a particular cache buffer or the shared pool, I know how much more I can increase the size. Any idea?

    Thanks,

    -- Chris

  4. #4
    Join Date
    Nov 2002
    Posts
    115
    Hi

    You can check sga free by the following.

    select name,
    sgasize/1024/1024 "Allocated (M)",
    bytes/1024/1024 "Free (K)",
    round(bytes/sgasize*100, 2) "% Free"
    from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
    where f.name = 'free memory'
    /
    Prasad

  5. #5
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68
    SQL> select name,
    2 sgasize/1024/1024 "Allocated (M)",
    3 bytes/1024/1024 "Free (K)",
    4 round(bytes/sgasize*100, 2) "% Free"
    5 from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
    6 where f.name = 'free memory'
    7 /

    NAME Allocated (M) Free (K) % Free
    -------------------------- ------------- ---------- ----------
    free memory 1101.26323 186.887745 16.97
    free memory 1101.26323 50 4.54

    Which one is correct?

    Thanks, ST2003

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