Oracle SGA and growth stat

 

 

 

SGA Report
SGA and growth stat




sga.sql

===========

  set serverout on

  declare

  dbname        varchar2(15);     -- Database Name
  tsgasize      number;           -- Total SGA Size
  bcsize        number;           -- Buffer Cache Size
  spsize        number;           -- Shared Pool Size
  jpsize        number;           -- Java Pool Size
  lpsize        number;           -- Large Pool Size
  fsize         number;           -- Fixed SGA Size
  rbsize        number;           -- Redo Buffers
  used          number;           -- Used SGA Memory
  free          number;           -- Free SGA Memory
  granule_size  number;           -- Granule Size
  tvsize        number;           -- Total Variable Size

  cursor c1 is
  select name, value from sys.v$parameter where name in ('java_pool_size', 'large_pool_size');

  cursor c2 is
  select name, value from sys.v$sga;

  begin
  select name into dbname from sys.v$database;
  select x.ksppstvl/(1024*1024) into granule_size
  from sys.x$ksppsv x, sys.x$ksppi y
  where x.indx=y.indx
  and y.ksppinm='_ksmg_granule_size';

  for cur1 in c1 loop
  case cur1.name
  when 'java_pool_size' then jpsize  := cur1.value;
  when 'large_pool_size' then lpsize := cur1.value;
  end case;
  end loop;

  for cur2 in c2 loop
  case cur2.name
  when 'Fixed Size' then fsize        := cur2.value;
  when 'Variable Size' then tvsize    := cur2.value;
  when 'Database Buffers' then bcsize :=cur2.value;
  when 'Redo Buffers' then rbsize     :=cur2.value;
  end case;
  end loop;

  -- Getting Shared Pool Size. Can not use shared_pool_size parameter value due to bug 1673506

  select cursiz_kghdsnew*granule_size into spsize from sys.x$ksmsp_dsnew;
  tsgasize := (fsize+tvsize+bcsize+rbsize);
  free     := (tvsize - ((spsize*1024*1024)+lpsize+jpsize));
  used     := tsgasize - free ;
  dbms_output.put_line('kk ');
  dbms_output.new_line;
  dbms_output.put_line('.....................SGA Configuration for.......................||dbname);
  dbms_output.put_line('------------------------------------------------------------------------');
  dbms_output.put_line('kkk ');
  dbms_output.new_line;
  dbms_output.put_line('Current SGA Size               : '||round(used/(1024*1024),2)||' MB');
  dbms_output.put_line('Maximum SGA Size               : '||round(tsgasize/(1024*1024),2)||' MB');
  dbms_output.put_line('Memory Available for SGA Growth: '||round(free/(1024*1024),2)||' MB');
  dbms_output.put_line('Buffer Cache Size              : '|| round(bcsize/(1024*1024),2) ||' MB');
  dbms_output.put_line('Shared Pool Size               : '|| spsize ||' MB');
  dbms_output.put_line('Large Pool Size                : '|| round(lpsize/(1024*1024),2) ||' MB');
  dbms_output.put_line('Java Pool Size                 : '|| round(jpsize/(1024*1024),2) ||' MB');
  dbms_output.put_line('Fixed SGA                      : '||round(fsize/(1024*1024),2) ||' MB');
  dbms_output.put_line('Redo Buffers                   : '|| round(rbsize/(1024*1024),2) ||' MB');
  dbms_output.put_line('Granule Size                   : '||granule_size||' MB');
  end
  /
  set serverout off
  set doc on


====================



@sga
.....................SGA Configuration for.......................ORCL
------------------------------------------------------------------------
Current SGA Size               : 540.4 MB
Maximum SGA Size               : 800.4 MB
Memory Available for SGA Growth: 260 MB
Buffer Cache Size              : 196 MB
Shared Pool Size               : 340 MB
Large Pool Size                : 0 MB
Java Pool Size                 : 0 MB
Fixed SGA                      : 2.12 MB
Redo Buffers                   : 2.29 MB
Granule Size                   : 4 MB