spool off
exit;
!01

cechon "5.34 Archive size is: " red
echo
cat 534.txt
echo
rm -rf 534.txt

###########5.41-5.60 about HIT RATE###########

#5.41 SGA HIT RATIO

sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 541.txt

select * from
(
SELECT (1 - (phy.value-phyd.value)/( (cur.value + con.value-phyd.value)))*100 "Data Buffer Hit Ratio"
FROM v\$sysstat cur,
v\$sysstat con,
v\$sysstat phy,
v\$sysstat phyd
WHERE cur.name = 'db block gets' AND con.name = 'consistent gets'
AND phy.name = 'physical reads' and phyd.NAME = 'physical reads direct'
),
(
select (sum(pinhits)/sum(pins))*100 "Shared pool Hit Ratio" from v\$librarycache
),
(
select (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 " Data Dictionary Hit Ratio"
from v\$rowcache
),
(
select ((sum(pins) / (sum(pins) + sum(reloads))) * 100) "Reload Hit Ratio"
from v\$librarycache
);

spool off
exit;
!01

cechon "5.41 SGA HIT RATIO is: " red
echo
echo "--------------------------------------------------------------------"
cechon "Data Buffer Hit Ratio should >95 " yellow
echo
cechon "Shared pool Hit Ratio should >95 " yellow
echo
cechon "Data Dictionary Hit Ratio >95% " yellow
echo
echo "--------------------------------------------------------------------"
cat 541.txt |xargs |awk '{print "Data Buffer Hit Ratio:"$1, \
"\nShared pool Hit Ratio:"$2,"\nData Dictionary Hit Ratio:"$3,"\nReload Hit Ratio:"$4}'

echo
rm -rf 541.txt

#5.42 REDOLOG HIT RATIO

sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 542.txt

SELECT name,
100 - Decode(gets,0,0,misses/(gets+misses))*100 ratio1,
100 - Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses))*100 ratio2
FROM v\$latch WHERE name IN ('redo allocation', 'redo copy');

spool off
exit;
!01

cechon "5.42 REDOLOG HIT RATIO is: " red
echo
echo "--------------------------------------------------------------------"
cechon "REDOLOG HIT RATIO should >95" yellow
echo
echo "--------------------------------------------------------------------"
cat 542.txt |xargs |awk '{print "redo copy:" $3 " " $4,"\nredo allocation:" $7" "$8}'
echo
rm -rf 542.txt

#5.43 The ratio of free data buffers

sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 543.txt

SELECT SUM(DECODE(STATUS,'AVAILABLE',VAL,0)) "AVAILABLE",
SUM(DECODE(STATUS,'BEING USED',VAL,0)) "BEING_USED",
round(SUM(DECODE(STATUS,'AVAILABLE',VAL,0))/(SUM(DECODE(STATUS,'AVAILABLE',VAL,0))
+SUM(DECODE(STATUS,'BEING USED',VAL,0)))*100,2)||'%'
"AVAILABLE_PERCENT"
FROM
(SELECT DECODE(STATE,0,'FREE',1,DECODE(LRBA_SEQ, 0, 'AVAILABLE', 'BEING USED'),
3,'BEING USED',STATE) "STATUS",
COUNT(*) VAL
FROM X\$BH
GROUP BY DECODE(STATE,0,'FREE',1,DECODE(LRBA_SEQ, 0, 'AVAILABLE', 'BEING USED'),
3,'BEING USED',STATE));

spool off
exit;
!01