实战:oracle巡检脚本v1

2022-09-27 15:57:59

/*************************************************************************************************************************

该脚本仅仅是为了工作方便书写,主要是在shell里整合了OS的系统命令和oracle的常见巡检脚本;

脚本执行过程中需要调用OS的系统命令的权限,如果执行脚本的用户是普通账户需要考虑权限设置的问题!

***********************************************************************************************************************/

#!/bin/sh
#ocpyang@126.com
#Modified according to the actual situation oracle username and password

export black=‘\033[0m‘
export boldblack=‘\033[1;0m‘
export red=‘\033[31m‘
export boldred=‘\033[1;31m‘
export green=‘\033[32m‘
export boldgreen=‘\033[1;32m‘
export yellow=‘\033[33m‘
export boldyellow=‘\033[1;33m‘
export blue=‘\033[34m‘
export boldblue=‘\033[1;34m‘
export magenta=‘\033[35m‘
export boldmagenta=‘\033[1;35m‘
export cyan=‘\033[36m‘
export boldcyan=‘\033[1;36m‘
export white=‘\033[37m‘
export boldwhite=‘\033[1;37m‘

cecho ()

## -- Function to easliy print colored text -- ##

# Color-echo.
# 参数 $1 = message
# 参数 $2 = color
{
local default_msg="No message passed."

message=${1:-$default_msg} # 如果$1没有输入则为默认值default_msg.
color=${2:-black} # 如果$1没有输入则为默认值black.

case $color in
black)
   printf "$black" ;;
boldblack)
   printf "$boldblack" ;;
red)
   printf "$red" ;;
boldred)
   printf "$boldred" ;;
green)
   printf "$green" ;;
boldgreen)
   printf "$boldgreen" ;;
yellow)
   printf "$yellow" ;;
boldyellow)
   printf "$boldyellow" ;;
blue)
   printf "$blue" ;;
boldblue)
   printf "$boldblue" ;;
magenta)
   printf "$magenta" ;;
boldmagenta)
   printf "$boldmagenta" ;;
cyan)
   printf "$cyan" ;;
boldcyan)
   printf "$boldcyan" ;;
white)
   printf "$white" ;;
boldwhite)
   printf "$boldwhite" ;;
esac
printf "%s\n" "$message"
tput sgr0   # tput sgr0即恢复默认值
printf "$black"

return
}

cechon ()

# Color-echo.
# 参数1 $1 = message
# 参数2 $2 = color
{
local default_msg="No message passed."
# Doesn‘t really need to be a local variable.

message=${1:-$default_msg} # 如果$1没有输入则为默认值default_msg.
color=${2:-black} # 如果$1没有输入则为默认值black.

case $color in
black)
  printf "$black" ;;
boldblack)
  printf "$boldblack" ;;
red)
  printf "$red" ;;
boldred)
  printf "$boldred" ;;
green)
  printf "$green" ;;
boldgreen)
  printf "$boldgreen" ;;
yellow)
  printf "$yellow" ;;
boldyellow)
  printf "$boldyellow" ;;
blue)
  printf "$blue" ;;
boldblue)
  printf "$boldblue" ;;
magenta)
  printf "$magenta" ;;
boldmagenta)
  printf "$boldmagenta" ;;
cyan)
  printf "$cyan" ;;
boldcyan)
  printf "$boldcyan" ;;
white)
  printf "$white" ;;
boldwhite)
  printf "$boldwhite" ;;
esac
printf "%s" "$message"
tput sgr0   # tput sgr0即恢复默认值
printf "$black"

return
}

#1.the server infomation
echo "the system basic infomation:"
echo "***********************************************************************"
echo
hostname=`/bin/hostname` #主机名

ipaddress=`/sbin/ifconfig |grep ‘inet addr:‘| grep -v ‘127.0.0.1‘ | cut -d: -f2 | awk ‘{ print $1}‘` #IP地址

gtway01=`cat /etc/sysconfig/network|grep GATEWAY|awk -F "=" ‘{print $2}‘` #网关

gtway02=`/bin/netstat -rn | awk ‘/^0.0.0.0/ {print $2}‘`

cpuinfo=`cat /proc/cpuinfo|grep "name"|cut -d: -f2 |awk ‘{print "*"$1,$2,$3,$4}‘|uniq -c` #cpu

phmem=`dmidecode | grep -A 16 "Memory Device$" |grep Size:|grep -v "No Module Installed"|awk ‘{print "*" $2,$3}‘|uniq -c` #物理内存数量

sysver=`cat /etc/issue | head -1` #--系统版本

kerver=`/bin/uname -a |awk ‘{print $3}‘` #内核版本

#mem usage
mem_total=$(/usr/bin/free -m |grep Mem|awk ‘{print $2}‘)
mem_used=$(/usr/bin/free -m |grep Mem|awk ‘{print $3}‘)
mem_rate=`expr $mem_used/$mem_total*100|bc -l`

#mem_summary
mem_sum=`/usr/bin/free -m | xargs | awk ‘{print "Free/total memory: " $17 " / " $8 " MB"}‘ \
| awk -F":" ‘BEGIN{print " FREE / TOTAL " } {print $2 }‘`

#disk space
dk_usage=`/bin/df -h | xargs | awk ‘{print "Free/total disk: " $11 " / " $9}‘`

cechon "1.1 server hostname is:" red
echo ${hostname}

cechon "1.2 server ipaddree is: " red
echo ${ipaddress}

if [ "${gtway01}" = "" ];then
cechon "1.3 server gateway is:" red
echo ${gtway02}
else
cechon "1.3 server gateway is:" red
echo ${gtway01}
fi

cechon "1.4 server cpuinfo is: " red
echo ${cpuinfo}

cechon "1.5 server Physical memory number is: " red
echo ${phmem}

cechon "1.6 server version is: " red
echo ${sysver}

cechon "1.7 server system kernel version is: " red
echo ${kerver}

cechon "1.8 server memory Summary is: " red
echo ${mem_sum}

cechon "1.9 server memory usage rate is: " red
echo ${mem_rate:0:5}%

cechon "1.10 server disk usage is: " red
echo
cechon "detail: " green
echo
#disk usage
df -H |awk -F ‘\t‘ ‘{ print $1,$2,$3,$4,$5,$6}‘
echo
cechon "Summary: " green
echo
echo ${dk_usage}

echo
cechon "1.11 server CPU load average is: " red
echo
/usr/bin/uptime | awk ‘BEGIN{print "1min, 5min, 15min"} {print $10,$11,$12}‘

echo
cechon "1.12 server started services is: " red
echo
/sbin/chkconfig --list | grep on
echo

echo
cechon "1.13 server CPU free is: " red
/usr/bin/top -b -n 1 | grep Cpu | awk ‘{print $5}‘ | cut -f 1 -d "."
echo

cechon "1.14 server ESTABLISHED TCP connect number is: " red
echo
/bin/netstat -n | awk ‘/^tcp/ {++S[$NF]} END {for(a in S) print a, S[a]}‘
echo

cechon "1.15 oracle ESTABLISHED connect is: " red
echo
/bin/netstat -an -t | grep ":1521" | grep ESTABLISHED | awk ‘{printf "%s %s\n",$5,$6}‘ | sort |sed ‘s/^::ffff://‘
echo

cechon "1.17 oracle LISTENER status is: " red
echo
/bin/ps -ef|grep lsn|grep -v grep
echo
lsnrctl status
echo

echo
echo "***********************************************************************"
echo

cechon "***********************************************************************" yellow
echo
cechon "2.Check the database basic situation:" green
echo
cechon "***********************************************************************" yellow
echo

#configure oracle user and password

ora_user="sys"
ora_pass="jinriDBAocpyang_jinri_ocp.com"

#2.1 oracle database version
echo
sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 1.txt
select * from v\$version;
spool off
exit;
!01

cechon "2.1 oracle database version is : " red
echo
cat 1.txt
echo
rm -rf 1.txt

#2.2 oracle database information

cechon "2.2 oracle database information is : " red
echo
cat 2.txt |xargs |awk ‘{print "dbid:"$1,"\nname:"$2, \
"\ncreated:"$3" "$4,"\nlog_mode:"$5,"\nopen_mode:"$6" "$7,"\ndb_unique_name:"$8}‘
echo
rm -rf 2.txt

#2.3 oracle instance information

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 3.txt
select instance_name,host_name,version,startup_time,status,database_status,
instance_role from v\$instance;
spool off
exit;
!01

cechon "2.3 oracle instance information is : " red
echo
cat 3.txt |xargs |awk ‘{print "instance_name:"$1,"\nhostname:"$2, \
"\nversion:"$3,"\nstartup_time:"$4" "$5,"\nstatus:"$6,"\ndatabase_status:"$7,"\ninstance_role:"$8}‘
echo
rm -rf 3.txt

#2.4 数据库非缺省初始化参数

cechon "2.4 Database non-default initialization parameters is : " red
echo
cat 4.txt
echo
rm -rf 4.txt

#2.5 database default temp tempspace

cechon "2.5 database default temp tempspace is : " red
echo
cat 25.txt
echo
rm -rf 25.txt

#2.6 database trace position

SELECT P1.VALUE||‘‘||P2.VALUE||‘_ORA_‘||P.SPID FILENAME
FROM
V\$PROCESS P,
V\$SESSION S,
V\$PARAMETER P1,
V\$PARAMETER P2
WHERE P1.NAME = ‘user_dump_dest‘
AND P2.NAME = ‘db_name‘
AND P.ADDR = S.PADDR
AND S.AUDSID = USERENV (‘SESSIONID‘);

spool off
exit;
!01

cechon "2.6 database trace position is : " red
echo
cat 26.txt
echo
rm -rf 26.txt

cechon "***********************************************************************" yellow
echo
cechon "3.Check the database object stauts:" green
echo
cechon "***********************************************************************" yellow
echo

#3.1 control file

cechon "3.1 Database control file is : " red
echo
cat 31.txt
echo

rm -rf 31.txt

#3.2 Online Redo Logfiles Status

cechon "3.2 Online Redo Logfiles Status : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""group#","status","type","member" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 32.txt
echo
rm -rf 32.txt

#3.3 tablespace Status

cechon "3.3 tablespace Status : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""tablespace_name","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 33.txt
echo
rm -rf 33.txt

#3.4 all datafile Status

cechon "3.4 all datafile Status : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""name","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 34.txt
echo
rm -rf 34.txt

#3.5 invalid objects

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 35.txt
select owner,object_name,object_type from dba_objects where status!=‘VALID‘ and owner!=‘SYS‘ and owner!=‘SYSTEM‘;
spool off
exit;
!01

cechon "3.5 invalid objects is : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""owner","object_name","object_type" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 35.txt
echo
rm -rf 35.txt

#3.6 undo segment status

cechon "3.6 undo segment status : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""segment_name","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 36.txt
echo
rm -rf 36.txt

#3.7 Tables and indexes in the same space object

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 37.txt
select a.owner,
a.tablespace_name tbsname ,
a.table_name tname,
b.index_name iname
from
     dba_tables a
     ,dba_indexes b
where
     a.tablespace_name=b.tablespace_name
     and b.table_name=a.table_name
     and a.owner=b.owner
     and b.owner NOT in
     (‘SYS‘,‘SYSTEM‘,‘XDB‘,‘WMSYS‘,‘SYSMAN‘,
     ‘ORDSYS‘,‘OUTLN‘,‘ORDDATA‘)
     and a.tablespace_name not in(‘SYSTEM‘,‘SYSAUX‘,‘UNDOTBS1‘,‘USERS‘
)
order by owner;
spool off
exit;
!01

cechon "3.7 Tables and indexes in the same space object is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""owner","tablespace_name","tablename","index_name" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 37.txt
echo
rm -rf 37.txt

#3.8 tablespace Automatic expansion

select t.tablespace_name,d.file_name,d.autoextensible,d.status
from dba_tablespaces t,dba_data_files d
where t. tablespace_name =d. tablespace_name
order by tablespace_name,file_name;

spool off
exit;
!01

cechon "3.8 tablespace Automatic expansion is: " red
echo
cat 38.txt
echo
rm -rf 38.txt

cechon "***********************************************************************" yellow
echo
cechon "4.Check the database resource usage:" green
echo
cechon "***********************************************************************" yellow
echo

#4.1 tablespace usage

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 41.txt
select f.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free";

spool off
exit;
!01

cechon "4.1 tablespace usage is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""tablespace_name","total","FREE","%FREE" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 41.txt
echo
rm -rf 41.txt

#4.2 resource limit

spool off
exit;
!01

cechon "4.2 resource limit is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""resource_name","max_utilization","initial_allocation","limit_value" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 42.txt
echo
rm -rf 42.txt

#4.3 session status

cechon "4.3 session status is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""sid","serial#","username","program" ,"machine","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 43.txt
echo
rm -rf 43.txt

#4.5 check Non-system tables in system tablespace

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 45.txt
select distinct(owner) from dba_tables
where tablespace_name=‘SYSTEM‘ and
owner!=‘SYS‘ and owner!=‘SYSTEM‘
union
select distinct(owner) from dba_indexes
where tablespace_name=‘SYSTEM‘ and
owner!=‘SYS‘ and owner!=‘SYSTEM‘;
spool off
exit;
!01

cechon "4.5 check Non-system tables in system tablespace is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""owner" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 45.txt
echo
rm -rf 45.txt

#4.6 temp tablespace usage

sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 46.txt
select ‘the ‘||name||‘ temp tablespaces ‘||tablespace_name||‘ idle ‘||round(100-(s.tot_used_blocks/s.total_blocks)*100,3)||‘% at ‘||to_char(sysdate,‘yyyymmddhh24miss‘)
from
(select d.tablespace_name tablespace_name,
nvl(sum(used_blocks),0) tot_used_blocks,
sum(blocks) total_blocks
from v\$sort_segment v ,dba_temp_files d
where d.tablespace_name=v.tablespace_name(+)
group by d.tablespace_name) s, v\$database;
spool off
exit;
!01

cechon "4.6 check Non-system tables in system tablespace is: " red
echo
cat 46.txt
echo
rm -rf 46.txt

#4.7 database zombie processes

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

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

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

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

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

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

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

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

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)

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)

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)

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)

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)

select rownum as rank, a.*
from (
select rows_processed,
executions,
rows_processed / decode(executions,0,1, executions) rows_per_exec,
hash_value,
sql_text
from v\$sqlarea
where rows_processed > 10000
order by rows_processed desc) a
where rownum < 11;

spool off
exit;
!01

cechon "5.13 Top 10 most expensive SQL (Rows Processed by Executions) is: " red
echo
cat 513.txt
echo
rm -rf 513.txt

#5.14 Top 10 most expensive SQL (Buffer Gets vs Rows Processed)

select rownum as rank, a.*
from (
select buffer_gets, lpad(rows_processed ||
decode(users_opening + users_executing, 0, ‘ ‘,‘*‘),20) "rows_processed",
executions, loads,
(decode(rows_processed,0,1,1)) *
buffer_gets/ decode(rows_processed,0,1,
rows_processed) avg_cost,
sql_text
from v\$sqlarea
where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
order by 5 desc) a
where rownum < 11;

spool off
exit;
!01

cechon "5.14 Top 10 most expensive SQL (Rows Processed by Executions) is: " red
echo
cat 514.txt
echo
rm -rf 514.txt

#5.15 full table scan

SELECT OPNAME,TARGET,B.NUM_ROWS,B.TABLESPACE_NAME,COUNT(TARGET) FROM
V\$SESSION_LONGOPS A,
ALL_ALL_TABLES B
WHERE A.TARGET=B.OWNER||‘.‘||B.TABLE_NAME
HAVING COUNT(TARGET)>10
GROUP BY OPNAME,TARGET,
B.NUM_ROWS,B.TABLESPACE_NAME;

spool off
exit;
!01

cechon "5.15 full table scan is: " red
echo
cat 515.txt
echo
rm -rf 515.txt

#5.16 Which operating segments using a large number of temporary

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V\$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

spool off
exit;
!01

cechon "5.16 Which operating segments using a large number of temporary is: " red
echo
cat 516.txt
echo
rm -rf 516.txt

#5.17 High degree of fragmentation of the table

SELECT segment_name table_name,COUNT(*) extents
FROM dba_segments WHERE owner NOT IN (‘SYS‘, ‘SYSTEM‘)
GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*))
FROM dba_segments GROUP BY segment_name);

spool off
exit;
!01

cechon "5.17 High degree of fragmentation of the table is: " red
echo
cat 517.txt
echo
rm -rf 517.txt

#5.21 Table space I / O ratio of

SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS PYR,
F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW
FROM V\$FILESTAT F, DBA_DATA_FILES DF
WHERE F.FILE# = DF.FILE_ID
ORDER BY DF.TABLESPACE_NAME;

spool off
exit;
!01

cechon "5.21 Table space I / O ratio of is: " red
echo
cat 521.txt
echo
rm -rf 521.txt

#5.22 file system I/O ratio

SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME",
A.STATUS, A.BYTES, B.PHYRDS, B.PHYBLKRD PBR, B.PHYWRTS, B.PHYBLKWRT PBW
FROM V\$DATAFILE A,
V\$FILESTAT B
WHERE A.FILE# = B.FILE#;

spool off
exit;
!01

cechon "5.22 file system I/O ratio is: " red
echo
cat 522.txt
echo
rm -rf 522.txt

#5.23 file system I/O ratio

SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME",
A.STATUS, A.BYTES, B.PHYRDS, B.PHYBLKRD PBR, B.PHYWRTS, B.PHYBLKWRT PBW
FROM V\$DATAFILE A,
V\$FILESTAT B
WHERE A.FILE# = B.FILE#;

spool off
exit;
!01

cechon "5.23 file system I/O ratio is: " red
echo
cat 523.txt
echo
rm -rf 523.txt

##########5.31-5.40 about REDO##########
#5.31 The current redo log file usage
sqlplus -S "${ora_user}/${ora_pass} as sysdba" <<!01 >/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 531.txt

select le.leseq "Current log sequence No",
       100 * cp.cpodr_bno / le.lesiz "Percent Full",
       (cpodr_bno - 1) * 512 "bytes used exclude header",
       le.lesiz * 512 - cpodr_bno * 512 "Left space",
       le.lesiz *512       "logfile size"
from x\$kcccp cp, x\$kccle le
where LE.leseq = CP.cpodr_seq
   and bitand(le.leflg, 24) = 8;

spool off
exit;
!01

cechon "5.31 The current redo log file usage is: " red
echo
cat 531.txt
echo
rm -rf 531.txt

#5.32 redo log generation in hour

WITH times AS
(SELECT /*+ MATERIALIZE */
   hour_end_time
    FROM (SELECT (TRUNC(SYSDATE, ‘HH‘) + (2 / 24)) - (ROWNUM / 24) hour_end_time
            FROM DUAL
          CONNECT BY ROWNUM <= (1 * 24) + 3),
         v\$database
   WHERE log_mode = ‘ARCHIVELOG‘)
SELECT hour_end_time, NVL(ROUND(SUM(size_mb), 3), 0) size_mb, i.instance_name
FROM(
SELECT hour_end_time, CASE WHEN(hour_end_time - (1 / 24)) > lag_next_time THEN(next_time + (1 / 24) - hour_end_time) * (size_mb / (next_time - lag_next_time)) ELSE 0 END + CASE WHEN hour_end_time < lead_next_time THEN(hour_end_time - next_time) * (lead_size_mb / (lead_next_time - next_time)) ELSE 0 END + CASE WHEN lag_next_time > (hour_end_time - (1 / 24)) THEN size_mb ELSE 0 END + CASE WHEN next_time IS NULL THEN(1 / 24) * LAST_VALUE(CASE WHEN next_time IS NOT NULL AND lag_next_time IS NULL THEN 0 ELSE(size_mb / (next_time - lag_next_time)) END IGNORE NULLS) OVER(
ORDER BY hour_end_time DESC, next_time DESC) ELSE 0 END size_mb
FROM(
SELECT t.hour_end_time, arc.next_time, arc.lag_next_time, LEAD(arc.next_time) OVER(
ORDER BY arc.next_time ASC) lead_next_time, arc.size_mb, LEAD(arc.size_mb) OVER(
ORDER BY arc.next_time ASC) lead_size_mb
FROM times t,(
SELECT next_time, size_mb, LAG(next_time) OVER(
ORDER BY next_time) lag_next_time
FROM(
SELECT next_time, SUM(size_mb) size_mb
FROM(
SELECT DISTINCT a.sequence#, a.next_time, ROUND(a.blocks * a.block_size / 1024 / 1024) size_mb
FROM v\$archived_log a,(
SELECT /*+ no_merge */
CASE WHEN TO_NUMBER(pt.VALUE) = 0 THEN 1 ELSE TO_NUMBER(pt.VALUE) END VALUE
FROM v\$parameter pt
WHERE pt.name = ‘thread‘) pt
WHERE a.next_time > SYSDATE - 3 AND a.thread# = pt.VALUE AND ROUND(a.blocks * a.block_size / 1024 / 1024) > 0)
GROUP BY next_time)) arc
WHERE t.hour_end_time = (TRUNC(arc.next_time(+), ‘HH‘) + (1 / 24)))
WHERE hour_end_time > TRUNC(SYSDATE, ‘HH‘) - 1 - (1 / 24)), v\$instance i
WHERE hour_end_time <= TRUNC(SYSDATE, ‘HH‘)
GROUP BY hour_end_time, i.instance_name
ORDER BY hour_end_time;

spool off
exit;
!01

cechon "5.32 redo log generation in hour is: " red
echo
cat 532.txt
echo
rm -rf 532.txt

#5.33 Redo log switch interval(7days)

SELECT B.RECID,B.FIRST_TIME,A.FIRST_TIME,ROUND((A.FIRST_TIME-B.FIRST_TIME)*24*60,2) MINATES
FROM V\$LOG_HISTORY A,
V\$LOG_HISTORY B
WHERE A.RECID=B.RECID +1 AND A.FIRST_TIME>SYSDATE - 20
AND ROUND((A.FIRST_TIME-B.FIRST_TIME)*24*60,2)<30
ORDER BY A.FIRST_TIME DESC;

spool off
exit;
!01

cechon "5.33 Redo log switch interval is: " red
echo
cat 533.txt
echo
rm -rf 533.txt

#5.34 Archive size

SELECT
TO_CHAR(first_time,‘MM/DD‘) DAY
--, TO_CHAR(first_time,‘YYYY/MM/DD‘) DAY2
, COUNT(*)||‘(‘||trim(to_char(sum(blocks*block_size)/1024/1024/1024,‘99,999.9‘))||‘G)‘ TOTAL
FROM
(select max(blocks) blocks,max(block_size) block_size,max(first_time) first_time
from
v\$archived_log a
where COMPLETION_TIME > sysdate - 7
and dest_id = 1
group by sequence#
)
group by to_char(first_time,‘MM/DD‘), to_char(first_time,‘YYYY/MM/DD‘)
order by TO_CHAR(first_time,‘YYYY/MM/DD‘) desc;

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

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

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

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

cechon "5.43 The ratio of free data buffers is: " red
echo
echo "--------------------------------------------------------------------"
cechon "REDOLOG HIT RATIO should >95" yellow
echo
echo "--------------------------------------------------------------------"
cat 543.txt |xargs |awk ‘{print "AVAILABLE:"$1, \
"\nBEING_USED:"$2,"\nAVAILABLE_PERCENT:"$3}‘
echo
rm -rf 543.txt

echo
cechon "***********************************************************************" yellow
echo
cechon "6.oracle database disabled objects:" green
echo "Disabled Indexes"
echo "Disabled constraints"
echo "Disabled triggers"
cechon "***********************************************************************" yellow
echo

#6.1 Disabled Indexes

select owner,index_name,index_type,table_name,status from dba_indexes
where status = ‘UNUSABLE‘;

spool off
exit;
!01

cechon "6.1 The Disabled Indexes is: " red
echo
cat 61.txt
echo
rm -rf 61.txt

#6.2 Disabled Constraints

select owner,constraint_name,constraint_type,table_name from dba_constraints
where status=‘DISABLED‘;

spool off
exit;
!01

cechon "6.2 The Disabled Constraints is: " red
echo
cat 62.txt
echo
rm -rf 62.txt

#6.3 Disabled Triggers

select owner,trigger_name,trigger_type from dba_triggers
where status=‘DISABLED‘;

spool off
exit;
!01

cechon "6.3 The Disabled Triggers is: " red
echo
cat 63.txt
echo
rm -rf 63.txt

echo
cechon "***********************************************************************" yellow
echo
cechon "7.oracle database latch and lock:" green
echo
echo 7.1-7.20 latch
echo 7.21-7.30 lock
cechon "***********************************************************************" yellow
echo

#7.1 latch wait top 20

select name,gets,misses,wait_time from v\$latch
where misses !=0 and rownum<=20
order by wait_time desc;

spool off
exit;
!01

cechon "7.1 latch wait top 20 is: " red
echo
cat 71.txt
echo
rm -rf 71.txt

#7.2 latch hits ratio

SET LINESIZE 200
COLUMN latch_hit_ratio FORMAT 999.00
SELECT a.name,
       a.gets,
       a.misses,
       ((1 - (a.misses / a.gets)) * 100) AS latch_hit_ratio
FROM   v\$latch a
WHERE a.gets   != 0
UNION
SELECT b.name,
       b.gets,
       b.misses,
       100 AS latch_hit_ratio
FROM   v\$latch b
WHERE b.gets   = 0
ORDER BY 4 DESC;

spool off
exit;
!01

cechon "7.2 latch hit ratio is: " red
echo
cat 72.txt
echo
rm -rf 72.txt

#7.3 latch hits ratio

column name format a30
column pct_of_gets format 99.00 heading "Pct of Gets |"
column pct_of_misses format 99.00 heading "Pct of Misses |"
column pct_of_sleeps format 99.00 heading "Pct of Sleeps |"
column pct_of_wait_time format 99.00 heading "Pct of|Wait Time |"
WITH latch AS (
    SELECT name,
           ROUND(gets * 100 / SUM(gets) OVER (), 2) pct_of_gets,
           ROUND(misses * 100 / SUM(misses) OVER (), 2) pct_of_misses,
           ROUND(sleeps * 100 / SUM(sleeps) OVER (), 2) pct_of_sleeps,
           ROUND(wait_time * 100 / SUM(wait_time) OVER (), 2)
                 pct_of_wait_time
      FROM v\$latch)
SELECT *
FROM latch
WHERE pct_of_wait_time > .1 OR pct_of_sleeps > .1
ORDER BY pct_of_wait_time DESC;

spool off
exit;
!01

cechon "7.3 latch hit ratio is: " red
echo
cat 73.txt
echo
rm -rf 73.txt

#7.21 total waiting time after started the database

column wait_type format a35
column lock_name format a12
column total_waits format 999,999,999
column time_waited_seconds format 999,999.99
column pct format 99.99
WITH system_event AS
(SELECT CASE WHEN event LIKE ‘enq:%‘
              THEN event ELSE wait_class
          END wait_type, e.*
     FROM v\$system_event e)
SELECT wait_type,SUM(total_waits) total_waits,
       round(SUM(time_waited_micro)/1000000,2) time_waited_seconds,
       ROUND( SUM(time_waited_micro)
             * 100
             / SUM(SUM(time_waited_micro)) OVER (), 2) pct
FROM (SELECT wait_type, event, total_waits, time_waited_micro
      FROM    system_event e
      UNION
      SELECT   ‘CPU‘, stat_name, NULL, VALUE
      FROM v\$sys_time_model
      WHERE stat_name IN (‘background cpu time‘, ‘DB CPU‘)) l
WHERE wait_type <> ‘Idle‘
GROUP BY wait_type
ORDER BY 4 DESC
;

spool off
exit;
!01

cechon "7.21 total waiting time after started the database is: " red
echo
cat 721.txt
echo
rm -rf 721.txt

#7.22 find the sql that Caused lock contention

column program format a10
column username format a8
column module format a12
column object_name format a12
column time_ms format 999,999,999
column pct_of_time format 99.99
column sql_text format a70
column lock_type format a4

WITH ash_query AS (
     SELECT substr(event,6,2) lock_type,program,
            h.module, h.action,   object_name,
            SUM(time_waited)/1000 time_ms, COUNT( * ) waits,
            username, sql_text,
            RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
            ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))
                OVER (), 2)             pct_of_time
      FROM v\$active_session_history h
      JOIN dba_users u USING (user_id)
      LEFT OUTER JOIN dba_objects o
           ON (o.object_id = h.current_obj#)
      LEFT OUTER JOIN v\$sql s USING (sql_id)
     WHERE event LIKE ‘enq: %‘
     GROUP BY substr(event,6,2) ,program, h.module, h.action,
         object_name, sql_text, username)
SELECT lock_type,module, username, object_name, time_ms,pct_of_time,
         sql_text
FROM ash_query
WHERE time_rank < 2
ORDER BY time_rank;

spool off
exit;
!01

cechon "7.22 find the sql that Caused lock contention is: " red
echo
cat 722.txt
echo
rm -rf 722.txt

#7.23 top 10 waiting for long time

column sql_text format a40 heading "SQL Text"
column app_time_ms format 99,999,999 heading "AppTime(ms)"
column app_time_pct format 999.99 heading "SQL App of Time%"
column pct_of_app_time format 999.99 heading "Total of App Time% "

WITH sql_app_waits AS
    (SELECT sql_id, SUBSTR(sql_text, 1, 200) sql_text,
            application_wait_time/1000 app_time_ms,
            elapsed_time,
            ROUND(application_wait_time * 100 /
                elapsed_time, 2) app_time_pct,
            ROUND(application_wait_time * 100 /
                SUM(application_wait_time) OVER (), 2) pct_of_app_time,
            RANK() OVER (ORDER BY application_wait_Time DESC) ranking
       FROM v\$sql
      WHERE elapsed_time > 0
        AND application_wait_time>0 )
SELECT sql_text, app_time_ms, app_time_pct,
       pct_of_app_time, ranking
FROM sql_app_waits
WHERE ranking <= 10
ORDER BY ranking ;

spool off
exit;
!01

cechon "7.23 top 10 waiting for long time is: " red
echo
cat 723.txt
echo
rm -rf 723.txt

#7.24 all user wait times

column wait_type format a35
column lock_name format a20
column total_waits format 999,999,999
column time_waited_seconds format 999,999.99
column pct format 99.99

WITH session_event AS
(SELECT CASE WHEN event LIKE ‘enq:%‘
              THEN event ELSE wait_class
          END wait_type, e.*
     FROM v\$session_event e   )
SELECT wait_type,SUM(total_waits) total_waits,
       round(SUM(time_waited_micro)/1000000,2) time_waited_seconds,
       ROUND( SUM(time_waited_micro)
             * 100
             / SUM(SUM(time_waited_micro)) OVER (), 2) pct
FROM (SELECT e.sid, wait_type, event, total_waits, time_waited_micro
      FROM    session_event e
      UNION
      SELECT sid, ‘CPU‘, stat_name, NULL, VALUE
      FROM v\$sess_time_model
      WHERE stat_name IN (‘background cpu time‘, ‘DB CPU‘)) l
WHERE wait_type <> ‘Idle‘
and sid in (select sid from v\$session where username
in
(
select username from dba_users where username not in(‘SYS‘,‘SYSTEM‘)
)
)
GROUP BY wait_type
ORDER BY 4 DESC;

spool off
exit;
!01

cechon "7.24 all user wait times is: " red
echo
cat 724.txt
echo
rm -rf 724.txt

#7.25 who waiting who

column sql_text format a35 heading "SQL text"
column blocking_user format a8 Heading "Blocking|user"
column blocked_user format a8 heading "Blocked|user"
column blocking_sid format 9999 heading "Blocking|SID"
column blocked_sid format 9999 heading "Blocked|SID"
column type format a4 heading "Lock|Type"

WITH sessions AS
       (SELECT /*+ materialize*/ username,sid,sql_id
          FROM v\$session),
     locks AS
        (SELECT /*+ materialize */ *
           FROM v\$lock)
SELECT l2.type,s1.username blocking_user, s1.sid blocking_sid,
        s2.username blocked_user, s2.sid blocked_sid, sq.sql_text
FROM locks l1
JOIN locks l2 USING (id1, id2)
JOIN sessions s1 ON (s1.sid = l1.sid)
JOIN sessions s2 ON (s2.sid = l2.sid)
LEFT OUTER JOIN v\$sql sq
       ON (sq.sql_id = s2.sql_id)
WHERE l1.BLOCK = 1 AND l2.request > 0;

spool off
exit;
!01

cechon "7.25 who waiting who is: " red
echo
cat 725.txt
echo
rm -rf 725.txt

实战:oracle巡检脚本v1

码农公寓

相关文章