Current Break Down of (SGA) Variable Size


prompt .
prompt .
prompt Current Break Down of (SGA) Variable Size
prompt ---------------
column Bytes format 999,999,999
column "% Used" format 999.99
column "Var. Size" format 999,999,999
select a.name "Name",
 bytes "Bytes",
 (bytes / b.value) * 100 "% Used",
 b.value "Var. Size"
from sys.v_$sgastat a,
 sys.v_$sga b
where a.name not in ('db_block_buffers','fixed_sga','log_buffer')
and b.name='Variable Size'
order by 3 desc;
prompt .
set feedback ON
declare
 h_char varchar2(100);
 h_char2 varchar(50);
 h_num1 number(25);
 result1 varchar2(50);
 result2 varchar2(50);
cursor c1 is
 select lpad(namespace,17)||': gets(pins)='||rpad(to_char(pins),9)||
 ' misses(reloads)='||rpad(reloads,9)||
 ' Ratio='||decode(reloads,0,0,to_char((reloads/pins)*100,999.999))||'%'
 from v$librarycache;
begin
 dbms_output.put_line
 ('==========================');
 dbms_output.put_line('. SHARED POOL: LIBRARY CACHE (V$LIBRARYCACHE)');
 dbms_output.put_line
 ('=========================');
 dbms_output.put_line('.');
 dbms_output.put_line('. Goal: The library cache ratio < 1%' );
 dbms_output.put_line('.');
 
 Begin
 SELECT 'Current setting: '||substr(value,1,30) INTO result1
 FROM V$PARAMETER 
 WHERE NUM = 23;
 SELECT 'Current setting: '||substr(value,1,30) INTO result2
 FROM V$PARAMETER 
 WHERE NUM = 325;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN 
 h_num1 :=1;
 END;
 dbms_output.put_line('Recommendation: Increase SHARED_POOL_SIZE '||rtrim(result1));
 dbms_output.put_line('. OPEN_CURSORS ' ||rtrim(result2));
 dbms_output.put_line('. Also write identical sql statements.');
 dbms_output.put_line('.');
 
 open c1;
 loop
 fetch c1 into h_char;
 exit when c1%notfound;
 
 dbms_output.put_line('.'||h_char);
 end loop;
 close c1;
dbms_output.put_line('.');
select lpad('Total',17)||': gets(pins)='||rpad(to_char(sum(pins)),9)||
 ' misses(reloads)='||rpad(sum(reloads),9),
 ' Your library cache ratio is '||
 decode(sum(reloads),0,0,to_char((sum(reloads)/sum(pins))*100,999.999))||'%'
 into h_char,h_char2
 from v$librarycache;
 dbms_output.put_line('.'||h_char);
 dbms_output.put_line('. ..............................................');
 dbms_output.put_line('. '||h_char2);
dbms_output.put_line('.');
end;
/
declare
 h_num1 number(25);
 h_num2 number(25);
 h_num3 number(25);
 result1 varchar2(50);
begin
 dbms_output.put_line
 ('===========================================');
 dbms_output.put_line('. SHARED POOL: DATA DICTIONARY (V$ROWCACHE)');
 dbms_output.put_line
 ('===========================================');
 dbms_output.put_line('.');
 dbms_output.put_line('. Goal: The row cache ratio should be < 10% or 15%' );
 dbms_output.put_line('.');
 dbms_output.put_line('. Recommendation: Increase SHARED_POOL_SIZE '||result1);
 dbms_output.put_line('.');
select sum(gets) "gets", sum(getmisses) "misses", round((sum(getmisses)/sum(gets))*100 ,3)
 into h_num1,h_num2,h_num3
 from v$rowcache;
dbms_output.put_line('.');
 dbms_output.put_line('. Gets sum: '||h_num1);
 dbms_output.put_line('. Getmisses sum: '||h_num2);
dbms_output.put_line(' .......................................');
 dbms_output.put_line('. Your row cache ratio is '||h_num3||'%');
end;
/
declare
 h_char varchar2(100);
 h_num1 number(25);
 h_num2 number(25);
 h_num3 number(25);
 h_num4 number(25);
 result1 varchar2(50);
begin
 dbms_output.put_line('.');
 dbms_output.put_line
 ('=============================================');
 dbms_output.put_line('. BUFFER CACHE (V$SYSSTAT)');
 dbms_output.put_line
 ('==============================================');
 dbms_output.put_line('.');
 dbms_output.put_line('. Goal: The buffer cache ratio should be > 70% ');
 dbms_output.put_line('.');
 Begin
 SELECT 'Current setting: '||substr(value,1,30) INTO result1
 FROM V$PARAMETER 
 WHERE NUM = 125;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN 
 result1 := 'Unknown parameter';
 END;
 dbms_output.put_line('. Recommendation: Increase DB_BLOCK_BUFFERS '||result1);
 dbms_output.put_line('.');
select lpad(name,15) ,value
 into h_char,h_num1
 from v$sysstat
 where name ='db block gets';
 dbms_output.put_line('. '||h_char||': '||h_num1);
select lpad(name,15) ,value
 into h_char,h_num2
 from v$sysstat
 where name ='consistent gets';
 dbms_output.put_line('. '||h_char||': '||h_num2);
select lpad(name,15) ,value
 into h_char,h_num3
 from v$sysstat
 where name ='physical reads';
 dbms_output.put_line('. '||h_char||': '||h_num3);
h_num4:=round(((1-(h_num3/(h_num1+h_num2))))*100,3);
dbms_output.put_line('. .......................................');
 dbms_output.put_line('. Your buffer cache ratio is '||h_num4||'%');
dbms_output.put_line('.');
end;
/
declare
 h_char varchar2(100);
 h_num1 number(25);
 h_num2 number(25);
 h_num3 number(25);
cursor buff2 is
 SELECT name
 ,consistent_gets+db_block_gets, physical_reads
 ,DECODE(consistent_gets+db_block_gets,0,TO_NUMBER(null)
 ,to_char((1-physical_reads/(consistent_gets+db_block_gets))*100, 999.999))
 FROM v$buffer_pool_statistics;
begin
 dbms_output.put_line
 ('===============================');
 dbms_output.put_line('. BUFFER CACHE (V$buffer_pool_statistics)');
 dbms_output.put_line
 ('============================');
dbms_output.put_line('.');
 dbms_output.put_line('.');
 dbms_output.put_line('Buffer Pool: Logical_Reads Physical_Reads HIT_RATIO');
 dbms_output.put_line('.');
open buff2;
 loop
 fetch buff2 into h_char, h_num1, h_num2, h_num3;
 exit when buff2%notfound;
dbms_output.put_line(rpad(h_char, 15, '.')||' '||lpad(h_num1, 10, ' ')||' '||
 lpad(h_num2, 10, ' ')||' '||lpad(h_num3, 10, ' '));
end loop;
 close buff2;
dbms_output.put_line('.');
end;
/
declare
 h_char varchar2(100);
 h_num1 number(25);
 result1 varchar2(50);
cursor c2 is
 select name,value
 from v$sysstat
 where name in ('sorts (memory)','sorts (disk)')
 order by 1 desc;
begin
 dbms_output.put_line
 ('======================================');
 dbms_output.put_line('. SORT STATUS (V$SYSSTAT)');
 dbms_output.put_line
 ('=====================================');
 dbms_output.put_line('.');
 dbms_output.put_line('. Goal: Very low sort (disk)' );
 dbms_output.put_line('.');
 BEGIN
 SELECT 'Current setting: '||substr(value,1,30) INTO result1
 FROM V$PARAMETER 
 WHERE NUM = 320;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN 
 result1 := 'Unknown parameter';
 END;
 dbms_output.put_line(' Recommendation: Increase SORT_AREA_SIZE '||result1);
 dbms_output.put_line('.');
 dbms_output.put_line('.');
 dbms_output.put_line(rpad('Name',30)||'Count');
 dbms_output.put_line(rpad('-',25,'-')||' -----------');
open c2;
 loop
 fetch c2 into h_char,h_num1;
 exit when c2%notfound;
 dbms_output.put_line(rpad(h_char,30)||h_num1);
 end loop;
 close c2;
end;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: