performance of the database

create or replace procedure performance_exec is
/*
自動執行響應的函式與儲存過程
*/
var number:=0;
begin
--快取區的情況！
dbms_output.put_line(' the performance of the database!!!!');
for i in 101..102 loop
dbms_output.put_line(' -----------------------------------------------------');
dbms_output.put_line(' '||fun_sga(i));
end loop;
--dbms_output.put_line(' '||fun_sga(102));
--dbms_output.put_line(' the performance of the database^^^^');
/*for i in 101..111 loop
dbms_output.put_line(' -----------------------------------------------------');
print_sga(i);
--dbms_output.put_line(' '||fun_sga(i));
end loop;*/
--日誌快取
dbms_output.put_line(' the performance of the database!!!!');
for i in 201..205 loop
dbms_output.put_line(' -----------------------------------------------------');
dbms_output.put_line(' '||fun_sga(i));
end loop;
dbms_output.put_line(' the performance of the database!!!!');
for i in 301..306 loop
dbms_output.put_line(' -----------------------------------------------------');
dbms_output.put_line(' '||fun_sga(i));
end loop;
dbms_output.put_line(' the performance of the database!!!!');
for i in 401..401 loop
dbms_output.put_line(' -----------------------------------------------------');
dbms_output.put_line(' '||fun_sga(i));
end loop;
dbms_output.put_line(' the performance of the database!!!!');
for i in 501..506 loop
dbms_output.put_line(' -----------------------------------------------------');
dbms_output.put_line(' '||fun_sga(i));
end loop;

end performance_exec;
/
----------------------------------------------------------------------------------

create or replace function fun_sga(var number) return varchar2 is
Result varchar2(500);
begin
dbms_output.enable(50000000);
--get the sum of sga
--the iinstruction of the file using
if var=101 then
declare
re number:=0;
begin
select sum(bytes) into re from v$sgastat;
dbms_output.put_line(' the total of sga (byte)!!');
Result:=round(re/1024/1024,3)||' M';
end;
--快取區的健康情況！！
elsif var=102 then
declare
re number:=0;
res varchar2(20):=' SGA分配：';
begin
select round(((1 - (phy.VALUE) / (cur.value + con.VALUE))) * 100, 3) buffer_cache_hit_ratio
into re
from v$sysstat phy, v$sysstat cur, v$sysstat con
where phy.NAME = 'physical reads'
and cur.name = 'db block gets'
and con.NAME = 'consistent gets';
dbms_output.put_line( ' instruction:');
dbms_output.put_line(' if the value >95 :the sga is bigger for the database!');
dbms_output.put_line(' if the value between 90 and 95 :the sga is good for the database!');
dbms_output.put_line(' if the value <90 :the sga is smaller for the database!');
if re>95 then
res:=res||'過大！！';
elsif re<90 then
res:=res||'過小！！';
else
res:=res||'適當！！！' ;
end if;
Result:=re||' %'||res;
end;
--日誌快取分配重實統計
elsif var=201 then
declare
re number:=0;
res varchar2(100):=' log buffer分配：';
begin
select value into re from v$sysstat where name='redo buffer allocation retries';
dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 0 or be close to 0 ');
dbms_output.put_line(' or you had better add the log buffer ');
if re>0 then
res:=res||'過小！！you had better add the log buffer !';
end if;
Result:=to_char(re)||''||res;
end;
elsif var=202 then
declare
re number:=0;
res varchar2(100):=' log space 分配：';
begin

dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 0 or be close to 0 ');
dbms_output.put_line(' or you had better add the log file ');
select value into re from v$sysstat where name='redo log space requests';
if re>0 then
res:=res||'過小！！you had better add the log space !';
end if;
Result:=re||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
elsif var=203 then
declare
re number:=0;
res varchar2(100):=' log wait 分配：';
begin

dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 0 or be close to 0 ');
select value into re from v$sysstat where name='redo log space wait time';
if re>0 then
res:=res||'過小！！LGWR IS busing!!';
end if;
Result:=re||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
--日誌快取請求與記錄項的比率
elsif var=204 then
declare
re number:=0;
res varchar2(100):=' log wait 分配：';
begin

dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 0 or be close to 0 ');
select (r.VALUE*5000)/e.VALUE into re
from v$sysstat r,v$sysstat e
where r.NAME='redo log space requests'
and e.NAME='redo entries';
if re>0 then
res:=res||'過小！！you had better add the log buffer!';
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
--日誌快取重實與記錄項的比率
elsif var=204 then
declare
re number:=0;
res varchar2(100):=' log wait 分配：';
begin

dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 0 or be close to 0 ');
select (r.VALUE*5000)/e.VALUE into re
from v$sysstat r,v$sysstat e
where r.NAME='redo log buffer allocation retries'
and e.NAME='redo entries';
if re>0.1 then
res:=res||'過小！！you had better add the log buffer!';
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;

--對庫快取的情況·gethitration
elsif var=301 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：';
begin

dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 90% or be smaller to 90$ ');
select (sum(gethits)/sum(gets))*100 into re from v$librarycache;
if re>90 then
res:=res||'過小！！you had better add the librarycache!';
end if;
if re<60 then
res:=res||'過小！！you had better reduce the librarycache!';
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
elsif var=302 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：';
begin

dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 90% or be bigger to 90$ ');
select sum(pinhits)/sum(pins)*100 into re from v$librarycache;
if re<90 then
res:=res||'過小！！you had better add the librarycache!';
elsif re>90 then
res:=res||'還可以！！';
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
elsif var=303 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：';
begin

dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 5% or be smaller to 5$ ');
select (a.reload_count/b.object_count)*100 into re
from (select sum(reloads) reload_count from v$librarycache) a,
(select count(*) object_count from v$db_object_cache) b;
if re>5 then
res:=res||'過小！！you had better add the librarycache!';
elsif re<5 then
res:=res||'還可以！！';
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
--reload/pins<1
elsif var=304 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：';
begin

dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 5% or be smaller to 1$ ');
select sum(reloads)/sum(pins)*100 into re from v$librarycache;
if re>1 then
res:=res||'過小！！you had better add the librarycache!';
elsif re<5 then
res:=res||'還可以！！';
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
elsif var=305 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：';
begin

dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 1% or be smaller to 1$ ');
select (a.reload_count/b.object_count)*100 into re
from (select sum(invalidations) reload_count from v$librarycache) a,
(select count(*) object_count from v$db_object_cache) b;
if re>1 then
res:=res||'過小！！you had better add the librarycache!';
elsif re<1 then
res:=res||'還可以！！';
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
--reload/pins<1
--字典快取的計算
elsif var=306 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：';
begin

dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should equal 15% or be smaller to 15$ ');
select sum(getmisses)/sum(gets)*100 into re from v$rowcache where gets>0;
if re>15 then
res:=res||'過小！！you had better add the librarycache!';
elsif re<15 then
res:=res||'還可以！！';
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
--PGA的簡單的統計1
elsif var=401 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：#####';
begin
dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should very very small ');
select (sum(ONEPASS_EXECUTIONS)+sum(MULTIPASSES_EXECUTIONS))/sum(TOTAL_EXECUTIONS)*100 into re
from v$sql_workarea_histogram ;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;

--撤消段的應用與管理
--撤消段塊標題的徵用！
elsif var=501 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：';
begin
dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should very very small ');
select round(sum(waits)/sum(gets)*100,3) into re
from v$rollstat;
if re>1 then
res:=res||' 撤消段的塊有明顯的爭用！！' ;
else
res:=res||' 撤消段的塊沒有有明顯的爭用！！' ;
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
--撤消段標題的爭用！！！
elsif var=502 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：####';
begin
dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should very very small ');
dbms_output.put_line(' or is very big you shuld add roll segments!');
select sum(total_waits) into re from v$system_event where event like '%undo%';
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
--撤消段緩衝器的爭用！！！
elsif var=503 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：####';
begin
dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should very very small ');
dbms_output.put_line(' or is very big you shuld add roll segments!');
select ((w1.COUNT+w2.COUNT+w3.COUNT+w4.COUNT)/(st1.VALUE+st2.VALUE))*100 into re
from v$waitstat w1,
v$waitstat w2,
v$waitstat w3,
v$waitstat w4,
v$sysstat st1,
v$sysstat st2
where w1.CLASS='system undo header'
and w2.class='system undo block'
and w3.CLASS='undo header'
and w4.CLASS='undo block'
and st1.NAME='db block gets'
and st2.NAME='db block changes';
if re>1 then
res:=res||' 撤消段的塊有明顯的爭用！！' ;
else
res:=res||' 撤消段的塊沒有有明顯的爭用！！' ;
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
elsif var=504 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：####';
begin
dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should very very small ');
dbms_output.put_line(' or is very big you shuld add roll segments!');
select sum(nospaceerrcnt) into re from v$undostat;
if re>0 then
res:=res||' 撤消段的塊有明顯的爭用！！' ;
else
res:=res||' 撤消段的塊沒有有明顯的爭用！！' ;
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
elsif var=505 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：####';
begin
dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should very very small ');
dbms_output.put_line(' or is very big you shuld add roll segments!');
select sum(SSOLDERRCNT) into re from v$undostat;
if re>0 then
res:=res||' 撤消段的塊有明顯的爭用！！' ;
else
res:=res||' 撤消段的塊沒有有明顯的爭用！！' ;
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
elsif var=506 then
declare
re number:=0;
res varchar2(100):=' librarycache 分配：####';
begin
dbms_output.put_line(' instruction:');
dbms_output.put_line(' value should very very small ');
dbms_output.put_line(' or is very big you shuld add roll segments!');
select sum(UNXPSTEALCNT) into re from v$undostat;
if re>0 then
res:=res||' 撤消段的塊有明顯的爭用！！' ;
else
res:=res||' 撤消段的塊沒有有明顯的爭用！！' ;
end if;
Result:=round(re,3)||''||res;
exception
when others then
dbms_output.put_line(' '||substrb(sqlerrm,1,200));
end;
end if;

return(Result);
end fun_sga;
/

[@more@]