实战:oracle巡检脚本v1(九)
ading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 56.txt
select username,sid,opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining,sql_text
from v\$session_longops , v\$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value
;
spool off
exit;
!01
cechon "5.6 Long run SQL is: " red
echo
cat 56.txt
echo
rm -rf 56.txt
#5.7 Most disk reads 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 57.txt
select
st.sql_text
from
v\$sql s,
v\$sqlarea st
where
s.address=st.address
and s.hash_value=st.hash_value
and s.disk_reads > 300
order by s.disk_reads asc
;
spool off
exit;
!01
cechon "5.7 Most disk reads SQL is: " red
echo
cat 57.txt
echo
rm -rf 57.txt
#5.8 The most serious SQL disk sorting
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 58.txt
select
sess.username,
sql.sql_text,
sort1.blocks
from v\$session sess,
v\$sqlarea sql,
v\$sort_usage sort1
where
sess.serial# = sort1.session_num
and sort1.sqladdr = sql.address
and sort1.sqlhash = sql.hash_value
and sort1.blocks > 200
order by sort1.blocks asc
;
spool off
exit;
!01
cechon "5.8 The most serious SQL disk sorting is: " red
echo
cat 58.txt
echo
rm -rf 58.txt
#5.9 Top 10 most expensive SQL(Elapsed Time)
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 59.txt
select rownum as rank, a.*
from (
select elapsed_Time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from v\$sqlarea
where elapsed_time > 20000
order by elapsed_time desc) a
where rownum < 11;
spool off
exit;
!01
cechon "5.9 Top 10 most expensive SQL(Elapsed Time) is: " red
echo
cat 59.txt
echo
rm -rf 59.txt
#5.10 Top 10 most expensive SQL (CPU Time)
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 510.txt
select rownum as rank, a.*
from (
select elapsed_Time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from v\$sqlarea
where cpu_time > 20000
order by cpu_time desc) a
where rownum < 11;
spool off
exit;
!01
cechon "5.10 Top 10 most expensive SQL (CPU Time) is: " red
echo
cat 510.txt
echo
rm -rf 510.txt
#5.11 Top 10 most expensive SQL (Buffer Gets by Executions)
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 511.txt
select rownum as rank, a.*
from (
select buffer_gets,
executions,
buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
hash_value,
sql_text
from v\$sqlarea
where buffer_gets > 50000
order by buffer_gets desc) a
where rownum < 11;
spool off
exit;
!01
cechon "5.11 Top 10 most expensive SQL (Buffer Gets by Executions) is: " red
echo
cat 511.txt
echo
rm -rf 51.txt
#5.12 Top 10 most expensive SQL (Physical Reads by Executions)
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 512.txt
select rownum as rank, a.*
from (
select disk_reads,
executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec,
hash_value,
sql_text
from v\$sqlarea
where disk_reads > 10000
order by disk_reads desc) a
where rownum < 11;
spool off
exit;
!01
cechon "5.12 Top 10 most expensive SQL (Physical Reads by Executions) is: " red
echo
cat 512.txt
echo
rm -rf 512.txt
#5.13 Top 10 most expensive SQL (Rows Processed by Executions)
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesi