实战:oracle巡检脚本v1(八)
et feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 47.txt
select pid, spid, username, terminal, program
from v\$process
where addr not in (select paddr from v\$session);
spool off
exit;
!01
cechon "4.7 database zombie processes is: " red
echo
cat 47.txt
echo
rm -rf 47.txt
cechon "***********************************************************************" yellow
echo
cechon "5.oracle database performance:" green
echo
echo 5.1-5.14 about sql
echo 5.15-5.20 about table
echo 5.21-5.30 about IO
echo 5.31-5.40 about REDO
echo 5.41-5.60 about HIT RATE
cechon "***********************************************************************" yellow
echo
##########5.1-5.14 about sql##########
#5.1 database Wait event
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 51.txt
select sid,event,WAIT_TIME from v\$session_wait
where event not like 'SQL%' and event not like 'rdbms%';
spool off
exit;
!01
cechon "5.1 database Wait event is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""sid","event","wait_time" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 51.txt
echo
rm -rf 51.txt
#5.2 system Wait event
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 52.txt
select EVENT,TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT from (
select * from v\$system_event
where event not like '%rdbms%'
and event not like '%message%'
and event not like 'SQL*Net%'
order by total_waits desc
)
where rownum <=5 ;
spool off
exit;
!01
cechon "5.2 system Wait event is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:" "EVENT","TOTAL_WAITS", "TOTAL_TIMEOUTS", "TIME_WAITED", "AVERAGE_WAIT" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 52.txt
echo
rm -rf 52.txt
#5.3 inefficient sql statements
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 53.txt
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V\$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
spool off
exit;
!01
cechon "5.3 inefficient sql statements is: " red
echo
cat 53.txt
echo
rm -rf 53.txt
#5.4 Long-running SQL
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 54.txt
SELECT sql_text "SQL", executions "Number of runs",
buffer_gets / decode(executions, 0, 1, executions) / 4000 "Response time"
FROM v\$sql
WHERE buffer_gets / decode(executions, 0,1, executions) / 4000 > 10
AND executions > 0;
spool off
exit;
!01
cechon "5.4 Long-running SQL is: " red
echo
cat 54.txt
echo
rm -rf 54.txt
#5.5 top 10 Poor performance sql
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 55.txt
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V\$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10 ;
spool off
exit;
!01
cechon "5.5 top 10 Poor performance sql is: " red
echo
cat 55.txt
echo
rm -rf 55.txt
#5.6 Long run SQL
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set he