一、巡检项目：
1) 数据库session连接数
select count(*) from v$session;
2) 数据库的并发数
select count(*) from v$session where status='ACTIVE';
3) 是否存在死锁
set linesize 200
column oracle_username for a16
column os_user_name for a12
column object_name for a30
SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,l.process,
l.session_id,s.serial#, l.locked_mode,o.object_name
FROM v$locked_object l,dba_objects o,v$session s
where l.object_id = o.object_id and s.sid = l.session_id;
select t2.username||' '||t2.sid||' '||t2.serial#||' '||t2.logon_time||' '||t3.sql_text
from v$locked_object t1,v$session t2,v$sqltext t3
where t1.session_id=t2.sid
and t2.sql_address=t3.address
order by t2.logon_time;
4) 是否有enqueue等待
select eq_type "lock",total_req# "gets",total_wait# "waits",cum_wait_time from v$enqueue_stat where total_wait#>0;
5) 是否有大量长事务
set linesize 200
column name for a16
column username for a10
select a.name,b.xacts,c.sid,c.serial#,c.username,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn
and b.usn=e.XIDUSN
and c.taddr=e.addr
and c.sql_address=d.ADDRESS
and c.sql_hashvalue=d.hash_value
order by a.name,c.sid,d.piece;
6)表空间使用率
set linesize 150
column file_name format a65
column tablespace_name format a20
select f.tablespace_name tablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g,
round(f.sumbytes/1024/1024/1024,2) free_g,
round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,
round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percent
from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,
(select tablespace_name,sum(bytes) sumbytes from dba_data_files group by tablespace_name) d
where f.tablespace_name= d.tablespace_name
order by d.tablespace_name;
临时文件：
set linesize 200
column file_name format a55
column tablespace_name format a20
select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,
round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,
round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,
round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_g
from dba_temp_files a,dba_free_space b
where a.file_id = b.file_id(+)
group by a.tablespace_name,a.file_name,a.bytes
order by a.tablespace_name;
select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,
round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,
round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,
round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_g
from dba_temp_files a,dba_free_space b
where a.file_id = b.file_id(+)
group by a.tablespace_name,a.file_name,a.bytes
order by a.tablespace_name;
归档的生成频率:
set linesize 120
column begin_time for a26
column end_time for a26
select a.recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') begin_time,
b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,
round((b.first_time - a.first_time)*24*60,2) minutes
from v$log_history a,v$log_history b
where b.recid = a.recid+1;
sql读磁盘的频率:
select a.username,b.disk_reads,b.executions,
round((b.disk_reads/decode(b.executions,0,1,b.executions)),2) disk_read_ratio,b.sql_text
from dba_users a,v$sqlarea b
where a.user_id = b.parsing_user_id
and disk_reads > 5000;
Datafile I/O:
col tbs for a12;
col name for a46;
select c.tablespace_name tbs,b.name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrt
from v$filestat a,v$datafile b,dba_data_files c
where b.file# = a.file#
and b.file# = c.file_id
order by tablespace_name,a.file#;
Disk I/O
select substr(b.name,1,13) disk,c.tablespace_name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,
a.phyblkrd,a.phyblkwrt,((a.readtim/decode(a.phyrds,0,1,a.phyblkrd))/100) avg_rd_time,
((a.writetim/decode(a.phywrts,0,1,a.phyblkwrt))/100) avg_wrt_time
from v$filestat a,v$datafile b,dba_data_files c
where b.file# = a.file#
and b.file# = c.file_id
order by disk,c.tablespace_name,a.file#;
select a.username,round(b.buffer_gets/(1024*1024),2) buffer_gets_M,b.sql_text
from dba_users a,v$sqlarea b
where a.user_id = b.parsing_user_id
and b.buffer_gets > 5000000;
col index_name for a16;
col table_name for a18;
col column_name for a18;
select index_name,table_name,column_name,column_position from user_ind_columns
where table_name = '&tbs';
大事务：
select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,message from v$session_longops
where message like '%RMAN%';
select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,message from v$session_longops
where sofar <> totalwork;
where (sofar/totalwork)*100 < 100;
索引检查：
set linesize 200;
column index_name for a15;
column index_type for a10;
column table_name for a15;
column tablespace_name for a16;
select index_name,index_type,table_name,tablespace_name from user_indexes
where table_name ='&t';
set linesize 200;
column index_name for a26;
column table_name for a26;
column column_name for a22;
column column_position for 999;
column tablespace_name for a16;
select table_name,index_name,column_name,column_position from user_ind_columns where table_name = '&tab';
select table_name,index_name,column_name,column_position from user_ind_columns where index_name = '&ind';
select table_name,index_name,index_type,status,TABLESPACE_NAME from user_indexes where table_name = '&tab';
select table_name,index_name,index_type,status,TABLESPACE_NAME from user_indexes where index_name = '&ind';
set linesize 200;
column index_name for a20;
column table_name for a20;
select index_name,index_type,table_name,partitioned from user_indexes where index_name = '&ind';
等待事件：
set linesize 200
column username for a12
column program for a30
column event for a28
column p1text for a15
column p1 for 999,999,999,999,999
select s.username,s.program,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait sw
where s.sid=sw.sid and s.status='ACTIVE'
order by sw.p1;
select event,p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait
order by event;
where event = 'buffer busy waits';
select owner,segment_name,segment_type,file_id,block_id from dba_extents
where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
column event for a35;
column p1text for a40;
select sid,event,p1,p1text from v$session_wait order by event;
查询相关SQL：
set linesize 200
set pagesize 1000
column username for a8
column program for a36
select s.sid,s.serial#,s.username,s.program,st.sql_text
from v$session s,v$sqltext st
where s.sql_hashvalue=st.hash_value and s.status='ACTIVE'
order by s.sid,st.piece;
select pid,spid from v$process p,v$session s
where s.sid=&sid and p.addr = s.paddr;
select s.sid,s.serial#,s.username,s.program,st.sql_text
from v$session s,v$sqltext st,v$process ps
where s.sql_hashvalue=st.hash_value
and ps.spid=&sid and s.paddr=ps.addr
order by s.sid,st.piece;
select sql_text from v$sqltext
where hash_value in (select sql_hash_value from v$session
where paddr in (select addr from v$process
where spid=&sid))
order by piece;
select sql_text from v$sqltext
where address in (select sql_address from v$session
where paddr in (select addr from v$process
where spid=&sid))
order by piece;
select sql_text from v$sqltext
where hash_value in (select sql_hash_value from v$session where sid=&sid)
order by piece;
select sql_text from v$sqltext
where address in (select sql_address from v$session where sid=&sid)
order by piece;
select ps.addr,ps.pid,ps.spid,ps.username,ps.program,s.sid,s.username,s.program
from v$process ps,v$session s
where ps.spid=&pid
and s.paddr=ps.addr;
select s.sid,s.serial#,s.username,s.program,st.sql_text
from v$session s,v$sqltext st,v$process ps
where s.sql_hashvalue=st.hash_value
and ps.spid='29863' and s.paddr=ps.addr
order by s.sid,st.piece;
column username for a12
column program for a20
select s.username,s.program,s.osuser,status
from v$session s
where s.status='ACTIVE';
query undotbs used percent:
set linesize 300;
select tablespace_name,segment_name,status,count(*),round(sum(bytes)/1024/1024,2) used_M from dba_undo_extents
group by tablespace_name,segment_name,status;
set linesize 300
column username for a10;
column program for a25;
select s.username,s.program,status,p.spid,st.sql_text from v$session s,v$process p,v$sqltext st where s.status='ACTIVE' and p.addr=s.paddr and st.hashvalue=s.sql_hash_value order by s.sid,st.piece;
select snap_id,dbid,instance_number,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') snap_time from stats$snapshot
order by INSTANCE_NUMBER,SNAP_ID,SNAP_TIME;
set linesize 120;
column what form. a30;
select job,log_user,what,instance from dba_jobs;
set linesize 120;
column owner for a12;
column segment_name for a24;
column segment_type for a18;
select owner,segment_name,segment_type,file_id,block_id from dba_extents
where file_id=&file and &block between block_id and block_id + blocks - 1;
select file_id,file_name from dba_data_files where file_id = &file_id;
ANALYZE TABLE ICS_ODS_CUST_ICS_CUR partition(ICS_ODS_CUST_ICS_CUR_PART_1) VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE ODSDATA.&object VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
analyze index SYS_C00311764 validate structure cascade;
column owner for a12;
column segment_name for a26;
column segment_type for a16;
column tablespace_name for a20;
column bytes for 999,999,999,999;
select owner,segment_name,segment_type,tablespace_name,bytes,blocks,buffer_pool from dba_segments
where segment_name='&seg'
order by bytes desc;
select segment_name,segment_type,tablespace_name,partition_name,bytes from user_segments
where segment_name='ODSV_REC_FILE'
and segment_name in (select distinct table_name from user_part_col_statistics where table_name='ODSV_REC_FILE')
order by bytes desc;
col object_name for a26;
select object_name,object_type,status,temporary from user_objects
where object_name = '&o';
set linesize 180
break on hash_value skip 1 dup
col child_number format 999 heading 'CHILD'
col operation format a82
col cost format 999999
col Kbytes format 999999
col object format a25
select hash_value,
child_number,
lpad(' ', 2 * depth) || operation || ' ' || options ||
decode(id, 0, substr(optimizer, 1, 6) || ' Cost=' || to_char(cost)) operation,
object_name object,
cost,
cardinality,
round(bytes / 1024) kbytes
from v$sql_plan
where hashvalue=&hash_value
/*in
(select a.sql_hash_value
from v$session a, v$session_wait b
where a.sid = b.sid and b.event = 'db file scattered read')*/
order by hash_value, child_number, id;
---
实例状态，表空间使用。
prompt ##### select * from v$version##################################################
select * from v$version;
---set heading on
prompt ##### select status from v$instance ###########################################
select status from v$instance;
prompt ##### tablespace surplus proportion ###########################################
select a.tablespace_name, substr((f.free_space / a.total) * 100,1,6) free
from (select tablespace_name, sum(bytes / (1024 * 1024)) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes / 1024 / 1024) free_space
from dba_free_space a
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
order by to_number(free);
prompt ##### temp tablespace ##########################################################
select b.bytes/a.bytes*100 ||'%' from
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a ,
( select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) b;
prompt ##### invalid objects###########################################################
SELECT owner , object_name, object_type,status ,LAST_DDL_TIME FROM dba_objects WHERE status like 'INVALID';
数据库用户状态：
prompt ##### database user status #####################################################
col username for a15
col account_status for a20
col default_tablespace for a10
col temporary_tablespace for a10
select a.username,
a.account_status,
a.default_tablespace,
a.temporary_tablespace
from dba_users a, dba_objects b
where a.username = b.owner(+) and a.account_status='OPEN'
group by a.username,
a.account_status,
a.default_tablespace,
a.temporary_tablespace;
prompt ###### controlfile status ####################################################
col name for a45
col status for a4
col block_size for 999999
select status,name,block_size from v$controlfile;
prompt ##### logfile status #########################################################
col status for a7
col type for a8
col member for a45
col is_recovery_dest_file for a5
select status,type,member,is_recovery_dest_file from v$logfile;
prompt ##### database dbid,log_mode#################################################
col name for a10
Select dbid,name,created,current_scn,log_mode From V$Database;
prompt ##### database constraint####################################################
Select constraint_type,constraint_name,table_name, status From dba_constraints Where status<>'ENABLED' AND owner not in ('SYSTEM','O
LAPSYS');
prompt ##### disabled trigger#######################################################
SELECT owner ,trigger_name,status FROM dba_triggers where status = 'DISABLED' and owner not in ('SYS','EXFSYS') AND TABLE_OWNER='SYS
';
prompt ##### disabled index ########################################################
SELECT owner ,index_name,status FROM dba_indexes where status = 'UNUSABLE';
prompt ##### temp tablespace used G ################################################
select sum(blocks)*8192/1024/1024/1024 from v$sort_usage;
prompt ##### SGA ###################################################################
select * from
(SELECT 1 - (phy.value-phyd.value)/( (cur.value + con.value-phyd.value)) "cache hit>90"
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) "sql library>95" from v$librarycache) ,
(select 1-SUM(GETMISSES)/SUM(GETS) "rowcache>85" from V$ROWCACHE);
prompt ##### tablespace sum########################################################
select nvl(t.owner,'total:') "USER",
sum(bytes)/1024/1024/1024 "percentage"
from dba_segments t
group by rollup(t.owner)
order by 2 ;
prompt ##### memory consumption ###################################################
col sql_txt for a45
col buffer/exes for 99999999
col executions for 999999
select * from (
select trunc(t.BUFFER_GETS/t.EXECUTIONS) "buffer/exes",
t.EXECUTIONS ,t.SQL_TEXT
from v$sql t where t.EXECUTIONS > 0 order by 1 desc )
where rownum <=2;
prompt ##### table don't have index###############################################
col owner for a7
col segment_name for a15
col segment_type for a10
col tablespace_name for a10
select owner, segment_name, segment_type, tablespace_name FROM dba_segments t WHERE NOT EXISTS ( SELECT 'x' FROM dba_indexes i
WHERE t.owner = i.table_owner AND t.segment_name = i.table_name) AND t.segment_type IN ('TABLE', 'TABLE PARTITION') AND t.owner
='WHX' ORDER BY 2 DESC;
prompt ##### lock table##########################################################
col username for a10
col sid for 9999999
col serial for 99999
col logon_time for a15
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logo
n_time;
spool off
用户和事务的死锁：
sqlplus -s < oracle/$1@$2
set feed off
set heading off
spool deadlock.alert
SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
DECODE(REQUEST, 0, 'NO','YES' ) WAITER
FROM V$LOCK
WHERE REQUEST > 0 OR BLOCK > 0
ORDER BY block DESC;
spool off
---
linux系统资源使用情况：
echo "########################### linux###############################################" >>$FILE_NAME
echo "##### listener status ##########################################################" >>$FILE_NAME
lsnrctl status >>$FILE_NAME
echo "##### system model######################################################uname -a" >>$FILE_NAME
uname -a >>$FILE_NAME
cat /etc/redhat-release >>$FILE_NAME
echo "##### linux"
echo "##### hostname#########################################################hostname">>$FILE_NAME
hostname >>$FILE_NAME
echo "##### oracle emergency################################alert_whx.log|ora-err-fail" >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i ora- >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i err- >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i fail- >>$FILE_NAME
echo "##### db_recovery_file_dest_size used###############################alert_whx.log" >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i db_recovery_file_dest_size >>$FILE_NAME
echo "##### system space##########################################################df -k" >>$FILE_NAME
df -k >>$FILE_NAME
echo "##### runtime##############################################################uptime" >>$FILE_NAME
uptime >>$FILE_NAME
echo "##### system user# #################################################cat/etc/passwd" >>$FILE_NAME
tail -10 /etc/passwd >>$FILE_NAME
echo "##### disk I/O ##########################################################sar 3 4" >>$FILE_NAME
sar 3 4 >>$FILE_NAME
echo "##### system log in###########################################################who" >>$FILE_NAME
who >>$FILE_NAME
echo "##### scheduled tast ##################################################crontab -l" >>$FILE_NAME
crontab -l >>$FILE_NAME
echo "##### cpu status#######################################################vmstat 2 4" >>$FILE_NAME
vmstat 2 4 >>$FILE_NAME
echo "##### free status ###########################################################free" >>$FILE_NAME
free >>$FILE_NAME
echo "##### disk status######################################################iostat 2 3" >>$FILE_NAME
iostat 2 3 >iostat23
head -10 iostat23 >>$FILE_NAME
echo "##### memory status###########################################################top" >>$FILE_NAME
top -b -n 1 -d 1 >top10
head -10 top10 >>$FILE_NAME
echo "##### system user group ###########################################cat /etc/group" >>$FILE_NAME
tail -10 /etc/group >>$FILE_NAME
echo "##### route ##########################################################netstat -rn" >>$FILE_NAME
netstat -rn >>$FILE_NAME
echo "##### THE END ##############################################" >>$FILE_NAME
二、巡检脚本：
# ---------------------------------------------------- #
# 系统负载 #
# ---------------------------------------------------- #
top -n1|head -20
netstat -apn|wc -l
netstat -pn -l|grep 1521 -c
df -h
# ---------------------------------------------------- #
# 检测实例 #
# ---------------------------------------------------- #
ps -ef|grep ora_.mon |grep -v grep
ps -ef|egrep "_pmon_|_smon_|_arc._|_dbw._|_lgwr_|_ckpt_" |egrep -v egrep
# ---------------------------------------------------- #
# 检测监听 #
# ---------------------------------------------------- #
netstat -pn -l -t|grep 1521|grep -v grep
ps -ef|grep tnslsnr|grep -v grep
# ---------------------------------------------------- #
# 检测Alert日志 #
# ---------------------------------------------------- #
sed -n "/`date -d "-3 day" +"%a %b %e"`/,$"p ${ORACLE_BASE}/admin/${ORACLE_SID}/bdump/alert_${ORACLE_SID}.log |grep ORA- -B 2 -A 5
# ---------------------------------------------------- #
# 查询数据库状态 #
# ---------------------------------------------------- #
column PLATFORM_NAME format a30
column DB_UNIQUE_NAME format a20
select DBID,NAME,DB_UNIQUE_NAME,to_char(CREATED,'yyyy-mm-dd') CREATD,
to_char(RESETLOGS_TIME,'yyyy-mm-dd') RSTIME,
LOG_MODE,OPEN_MODE,
DATABASE_ROLE DB_ROLE,
GUARD_STATUS DGSTAT,
PLATFORM_NAME,
FLASHBACK_ON
from v$database;
# ---------------------------------------------------- #
# 表空间使用 #
# ---------------------------------------------------- #
column name format a20
column "Used (M)" format a20
column "Free (MB)" format a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes/1024/1024, 0),'99,999,990.900') "Size (M)",
NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024 ||'/'||NVL(a.bytes/1024/1024, 0) "Used (M)",
TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes , 0)* 100, '990.00')||'%' "Used %" ,
nvl(f.bytes,0)/1024/1024||'/'||nvl(a.bytes/1024/1024,0) "Free (MB)",
to_char(nvl(nvl(f.bytes,0) / a.bytes,0)*100,'990.00')||'%' "Free (%)"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status",d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
NVL(t.bytes, 0)/1024/1024||'/'||NVL(a.bytes/1024/1024, 0) "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes , 0)* 100, '990.00')||'%' "Used %",
(a.bytes-nvl(t.bytes,0))/1024/1024||'/'||nvl(a.bytes/1024/1024,0) "Free (MB)",
to_char(nvl((a.bytes-nvl(t.bytes,0)) / a.bytes,0)*100,'990.00')||'%' "Free (%)"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
# ---------------------------------------------------- #
# 数据文件使用 #
# ---------------------------------------------------- #
col file_name format a50
SELECT df.file_id, df.file_name, df.size_mb,
NVL (free.maxfree, 0) maxfree_mb,
ROUND (NVL (free.free_mb, 0), 2) free_mb,
100 - ROUND (100.0 * NVL (free.free_mb, 0) / df.size_mb, 2) pct_used,
ROUND (100.0 * NVL (free.free_mb, 0) / df.size_mb, 2) pct_free,
df.status
FROM (SELECT file_id, file_name, tablespace_name, BYTES / 1048576 size_mb,status
FROM dba_data_files) df,
(SELECT file_id, SUM (BYTES) / 1048576 free_mb,
TRUNC (MAX (BYTES / 1024 / 1024), 2) maxfree
FROM dba_free_space
GROUP BY file_id) free
WHERE df.file_id = free.file_id(+)
ORDER BY 2;
# ---------------------------------------------------- #
# 等待事件 #
# ---------------------------------------------------- #
select event, sum(seconds_in_wait) "wait time(s)",count(sid) "count" from v$session_wait group by event;
# ---------------------------------------------------- #
# 引起等待事件的语句 #
# ---------------------------------------------------- #
select s.sql_text, sw.event
from v$session b,v$session_wait sw,v$sqltext s
where b.sid=sw.sid
and sw.event not like '%SQL*Net%'
and sw.EVENT NOT LIKE 'rdbms%'
and s.hash_value=b.sql_hash_value
and s.sql_id=b.sql_id
order by s.address,s.piece;
# ---------------------------------------------------- #
# redo log 切换频率 #
# ---------------------------------------------------- #
SELECT TO_CHAR (first_time, 'yyyy-mm-dd') DAY, COUNT (*) switch_times, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h00, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) h01, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) h02, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) h03, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) h04, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) h05, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) h06, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) h07, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) h08,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) h09,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) h10,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) h11,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) h12,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) h13,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) h14,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) h15,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) h16,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) h17,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) h18,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) h19,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) h20,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) h21,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) h22,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) h23
FROM v$log_history
WHERE first_time > TRUNC (SYSDATE - 1)
GROUP BY ROLLUP (TO_CHAR (first_time, 'yyyy-mm-dd'));
# ---------------------------------------------------- #
# redo log 缓冲区 #
# ---------------------------------------------------- #
期望结果：应该小于5%。
巡检说明：由于数据库的所有DML和DLL操作在执行之前必须在重做日志缓冲区生成一条记录，故重做日志缓冲区内的竞争将严重影响数据库的性能。在重做日志缓冲区内的竞争主要有两类，latch竞争和过量请求竞争。
SELECT TO_CHAR (ROUND ((r.VALUE / e.VALUE) * 100, 2), '990.99' ) || '%' "redolog buffer retry ratio" FROM v$sysstat r, v$sysstat e WHERE r.NAME = 'redo buffer allocation retries' AND e.NAME = 'redo entries';
# ---------------------------------------------------- #
# 检查无效的对象 #
# ---------------------------------------------------- #
select owner,object_id,object_name,object_type,status from dba_objects where owner in ( 'SMS', 'MMS', 'WAP', 'UNINET', 'SYS', 'SYSTEM') and status != 'VALID' order by owner;
SELECT object_id, owner || '.' || object_name object_name, object_type, status, TO_CHAR (created, 'yy-mm-dd hh24:mi:ss') created, TO_CHAR (last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') last_ddl_time FROM all_objects WHERE status != 'VALID';
表：1、监控表的增长
select segment_name,segment_type,bytes/1024/1024 from dba_segments where wner='FOUNDER' ORDER BY bytes/1024/1024 desc；
2、表和索引分析信息
SELECT 'table', COUNT (*) FROM dba_tables WHERE last_analyzed IS NOT NULL GROUP BY 'table'
UNION ALL
SELECT 'index', COUNT (*) FROM dba_indexes WHERE last_analyzed IS NOT NULL GROUP BY 'index';
3.未建索引的表
SELECT /*+ rule */
owner, segment_name, segment_type, tablespace_name,
TRUNC (BYTES / 1024 / 1024, 1) size_mb
FROM dba_segments t
WHERE NOT EXISTS (
SELECT 'x'
FROM dba_indexes i
WHERE t.owner = i.table_owner
AND t.segment_name = i.table_name)
AND t.segment_type IN ('TABLE', 'TABLE PARTITION')
AND t.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY 5 DESC;
升序用ASC
9.sort_segment检查
select tablespace_name,extent_size db_blocks_per_extent,total_extents,
used_extents,free_extents from v$sort_segment;
10.数据库总大小
select round(sum(space)) all_space_M from
(
select sum(bytes)/1024/1024 space from dba_data_files
union all
select nvl(sum(bytes)/1024/1024,0) space from dba_temp_files
union all
select sum(bytes)/1024/1024 space from v$log
);
11.检测连接数情况
（1）
select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT
from v$session S,v$session_wait SW where S.Username is not null and SW.Sid=S.Sid
and SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc;
（2）
select count(*) from v$session;
（3）
select sid,serial#,username,program,machine,status from v$session;
11.回滚段信息
1）信息1
col segment_name format a20
col tablespace_name format a20
select segment_name,owner,tablespace_name,
dba_rollback_segs.status
from dba_rollback_segs,v$Datafile where file_id=file#;
2）信息2
select segment_name,initial_extent,next_extent,min_extents,
owner,dba_rollback_segs.status status,optsize
from dba_rollback_segs,v$rollstat
where dba_rollback_segs.segment_id=v$rollstat.usn;
3）信息3
col Rollback_Name for a16
select substr(V$rollname.NAME,1,20) "Rollback_Name",
substr(V$rollstat.EXTENTS,1,6) "EXTENT",
v$rollstat.RSSIZE, v$rollstat.WRITES,
substr(v$rollstat.XACTS,1,6) "XACTS",
v$rollstat.GETS,
substr(v$rollstat.WAITS,1,6) "WAITS",
v$rollstat.HWMSIZE, v$rollstat.SHRINKS,
substr(v$rollstat.WRAPS,1,6) "WRAPS",
substr(v$rollstat.EXTENDS,1,6) "EXTEND",
v$rollstat.AVESHRINK,
v$rollstat.AVEACTIVE
from v$rollname, v$rollstat
where v$rollname.USN = v$rollstat.USN
order by v$rollname.USN;
4）信息4
select r.name Rollback_Name,
p.pid Oracle_PID,
p.spid OS_PID,
nvl(p.username,'NO TRANSACTION') Transaction,
p.terminal Terminal
from v$lock l, v$process p, v$rollname r
where l.addr = p.addr(+)
and trunc(l.id1(+)/65536)=r.usn
and l.type(+) = 'TX'
and l.lmode(+) = 6
order by r.name;
5）回滚段的争用情况
select name ,waits ,gets ,waits/gets "Ratio" from v$rollstat a ,v$rollname b where a.usn=b.usn;
6）rollback信息
select substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",
substr(sys.dba_segments.OWNER,1,8) "Owner",
substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",
substr(sys.dba_segments.SEGMENT_NAME,1,12) "Rollback Name",
substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",
substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",
substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",
substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",
substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",
substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",
substr(sys.dba_segments.EXTENTS,1,6) "Extent#",
substr(sys.dba_rollback_segs.STATUS,1,10) "Status"
from sys.dba_segments, sys.dba_rollback_segs
where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and
sys.dba_segments.segment_type = 'ROLLBACK'
order by sys.dba_rollback_segs.segment_id;
12.Redo log信息检查
1）Redo Log 文件状态
col member for a56
select f.member "member",
f.group# "group",
l.bytes/1024/1024 "size",
l.status
from v$logfile f, v$log l
where f.group#=l.group#
order by f.group#,f.member;
2）LogGroup信息
SELECT group#, sequence#, bytes, members, status from v$log;
3）关于log_buffer
select name,value from v$sysstat where name in('redo entries','redo buffer allocation retries');
4）查询LOG大小及频率
set linesize 300
set pages 100
column d1 form. a20 heading "Date"
column sw_cnt form. 99999 heading 'Number|of|Switches'
column Mb form. 999,999 heading "Redo Size"
column redoMbytes form. 999,999,9999 heading "Redo Log File Size (Mb)"
break on report
compute sum of sw_cnt on report
compute sum of Mb on report
var redoMbytes number;
begin
select max(bytes)/1024/1024 into :redoMbytes from v$log;
end;
/
print redoMbytes
select trunc(first_time) d1
, count(*) sw_cnt
, count(*) * :redoMbytes Mb
from v$log_history
group by trunc(first_time)
/
13.IO情况检查
col file_name for a46
select
df.name file_name,
fs.phyrds reads,
fs.phywrts writes,
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) readtime,
(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) writetime
from
v$datafile df,v$filestat fs
where df.file#=fs.file#
order by df.name;
select count(*) from v$session;
14.命中率相关检查
1）Shared Pool Size 命中率
select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"
from v$librarycache where namespace
in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');
2）数据字典命中率
select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %"
from v$rowcache;
3）锁竞争
select substr(ln.name,1,25) Name,
l.gets, l.misses,
100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)"
from v$latch l, v$latchname ln
where ln.name in ('cache buffers lru chain')
and ln.latch# = l.latch#;
4）排序命中率
select a.value "Sort(Disk)", b.value "Sort(Memory)",
round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)';
5）数据缓冲区命中率
select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio
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';
6）Miss LRU Hit命中率
column "Miss LRU Hit%" format 99.9999999;
col name format a40
select name, (sleeps/gets) "Miss LRU Hit%"
from v$latch where name ='cache buffers lru chain';
7）检查内存排序性能
select a.name, to_char(value)
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');
8）redo log buffer retry ratio
select to_char(r.value/e.value) "redo log buffer retry ratio"
from v$sysstat r,v$sysstat e
where r.name='redo buffer allocation retries'
and e.name='redo entries';
9）wait等待检查
select count(*) total_in_wait from v$session_wait
where event='log buffer space';
select event,total_waits,time_waited,average_wait
from v$system_event
where event like '%undo%';
select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%'
and event not like 'rdbms%';
15、查询lock锁
SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
ORDER BY id1, request;
三、生成报告：
这个就要看客户的需要了，当然一份完美的报告靠个人办公软件处理文档的功底了！可以参照oracle 公司的巡检报告（rda工具跑完后）整理所得，还是比较工整明了的。
