#巡检sql脚本
SET feedback OFF verify OFF trimspool ON term OFF timing OFF heading ON
SET pagesize 100 linesize 500
-- 生成的文件 ~/oracle_xj_实例名_日期.txt
column inst_name new_value inst_name noprint
select instance_name inst_name from v$instance;
column txt_time new_value txt_time noprint
select to_char(sysdate,'YYYYMMDD') txt_time from dual;
spool /home/oracle/oracle_xj_&inst_name._&txt_time..txt
PROMPT 说明：如果有的条目没有结果，则该条目状态正常
PROMPT
PROMPT 一、数据库的基本情况
PROMPT
PROMPT 1.1、数据库基本信息
col host_name for a20
SELECT dbid,name db_name,instance_name,version,parallel rac,host_name FROM v$database,v$instance;
PROMPT
PROMPT 1.2、实例状态
SELECT instance_number,instance_name,status,host_name FROM gv$instance;
PROMPT
PROMPT 1.3、数据库运行时间
col "runtime" for a25
SELECT instance_number,
to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "startuptime",
TRUNC(sysdate - (startup_time)) ||'day '
||TRUNC(24*((sysdate-startup_time) -TRUNC(sysdate-startup_time))) ||'hour '
||MOD(TRUNC(1440*((SYSDATE-startup_time)-TRUNC(sysdate-startup_time))),60) ||'min '
||MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME)-TRUNC(SYSDATE-startup_time))),60) ||'second' "runtime"
FROM gv$instance;
PROMPT
PROMPT 二、数据库的cpu、内存情况
PROMPT
PROMPT 2.1、cpu情况
col STAT_NAME for a20
col percent for a10
SELECT stat_name,to_char((value*100/(sum(value) over())),90.99) || '%' percent FROM v$osstat where stat_name in ('IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME');
PROMPT
PROMPT 2.2、SGA及其组件大小
PROMPT -参考值 - Buffer Cache Size：70%SGA，Shared Pool Size：15%SGA左右
col NAME for a35
col percent for a10
with tmp as
(select decode(a.value,0,b.value,null,b.value,a.value) sga_size from
(SELECT name,value from v$parameter where name='sga_target') a,
(SELECT name,value from v$parameter where name='sga_max_size') b)
SELECT name,round(bytes/1024/1024) "size(M)",round(sga_size/1024/1024) "sga(M)",round(100*bytes/sga_size,2) || '%' percent
FROM v$sgainfo,tmp where name in('Buffer Cache Size','Shared Pool Size');
PROMPT
PROMPT 2.3、PGA大小
PROMPT -参考值 - 30% SGA
col NAME for a35
SELECT name,round(value/1024/1024) "size(M)" FROM v$parameter where name = 'pga_aggregate_target';
PROMPT
PROMPT 三、检查各文件状态
PROMPT
PROMPT 3.1、查看参数文件
col NAME for a20
col value for a50
SELECT name,value FROM v$parameter where name = 'spfile';
PROMPT
PROMPT 3.2、查看控制文件
col NAME for a75
SELECT name,status FROM v$controlfile;
PROMPT
PROMPT 3.3、查看在线日志
col MEMBER for a50
select a.group#,a.status,a.type,a.member,(b.bytes/1024/1024) onl_size_mb,(c.bytes/1024/1024) std_size_mb
from v$Logfile a,v$Log b,v$standby_log c where a.group#=b.group#(+) and a.group#=c.group#(+)
order by 3,1;
PROMPT
PROMPT 3.4、检查最近一天日志切换频率
select thread#,sequence#,to_char(first_time, 'yyyymmdd_hh24:mi:ss') firsttime,case when minutes<0 then null else minutes end mitutes from
(SELECT thread#,sequence#,first_time,
round((first_time - lag(first_time) over(order by thread#,first_time)) * 24 * 60,2) minutes
FROM v$log_history
where first_time > sysdate - 2) where first_time > sysdate - 1
order by thread#,firsttime;
PROMPT
PROMPT 3.4.1、检查最近一周日志切换
select to_char(first_time, 'YYYY.MM.DD') day,
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(to_char(first_time, 'DDMMYYYY:HH24:MI'),10,2),'23',1,0)),'999') "23"
from v$log_history where first_time > trunc(sysdate) - 7
group by to_char(first_time, 'YYYY.MM.DD') order by 1 desc;
PROMPT
PROMPT 3.5、查看ASM磁盘组空间
col NAME for a20
col used_percent for 9999
select group_number, name, type, total_mb, usable_file_mb,
round(100 - usable_file_mb * decode(type,'EXTERN',1,'NORMAL',3,'HIGH',5) *100 / (total_mb)) used_percent from v$asm_diskgroup;
PROMPT
PROMPT 3.6、查看表空间的使用情况
select tablespace_name,round(used/1024/1024) "used(M)",round(total/1024/1024) "total(M)",to_char(round(used/total * 100,2),'9999990.99') used_percent from(
select a.tablespace_name,(a.bytes_alloc-b.bytes_free) used,a.bytes_total total from
(SELECT tablespace_name, sum(bytes) bytes_alloc, sum(greatest(bytes,maxbytes)) bytes_total FROM dba_data_files group by tablespace_name) a,
(SELECT tablespace_name, sum(bytes) bytes_free FROM dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) order by 4;
PROMPT
PROMPT 3.7、查看临时表空间使用情况
select tablespace_name,used "used(M)",allocate "allocate(M)",total "total(M)",to_char(round(used/total * 100,2),'990.99') used_percent
from (SELECT tablespace_name,(select sum(blocks)/128 from v$tempseg_usage) used,
round(sum(bytes)/1024/1024) allocate,round(sum(greatest(bytes,maxbytes))/1024/1024) total
FROM dba_temp_files group by tablespace_name);
PROMPT
PROMPT 3.8、查看临时段使用的情况
COL username FOR a20
COL segtype FOR a10
SELECT username, segtype, extents "Extents Allocated", blocks "Blocks Allocated" FROM v$tempseg_usage order by 1,2;
PROMPT
PROMPT 3.9、查看数据文件状态
col NAME for a30
SELECT b.name tablespace_name, a.status, count(*) FROM v$datafile a ,v$tablespace b where a.ts#=b.ts# group by b.name, a.status order by 1;
PROMPT
PROMPT 3.10、归档日志检查
col dest_name for a20
col error for a40
col gap_status for a10
SELECT dest_id, dest_name, status, type, error, gap_status FROM v$archive_dest_status;
PROMPT
PROMPT 3.11、最近一周备份情况
select input_type,start_time,status from V$RMAN_BACKUP_JOB_DETAILS where start_time > sysdate-7 order by start_time desc;
PROMPT
PROMPT 四、检查数据库对象状态
PROMPT
PROMPT 4.1、查看回滚段状态
SELECT status,count(*) FROM dba_rollback_segs group by status;
PROMPT
PROMPT 4.2、检查是否有禁用约束
col owner for a20
SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints WHERE status ='DISABLED'
and owner not in ('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS','RMAN')
order by 1,3;
PROMPT
PROMPT 4.3、检查是否有禁用触发器
col owner for a20
col taigger_name for a10
col table_name for a30
col table_name for a30
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED'
and owner not in ('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS','RMAN')
order by 1,3;
PROMPT
PROMPT 4.4、Oracle Job是否有失败
col what for a50
SELECT job,what,last_date,next_date,failures,broken FROM dba_jobs Where failures>0 or broken='Y';
PROMPT
PROMPT 4.5、查看无效的对象
col object_name for a40
SELECT owner,object_name,object_type,last_ddl_time FROM dba_objects where status != 'VALID'
and owner not in ('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS','RMAN','PUBLIC')
order by 1,3;
PROMPT
PROMPT 4.6、检查失效的索引
SELECT owner,index_name,table_name,tablespace_name,status From dba_indexes Where status not in('VALID','N/A') order by 1,3;
PROMPT
PROMPT 4.7、并行度大于1的索引
col owner for a25
col table_name for a30
col index_name for a30
col degree for a10
select owner,table_name,index_name,degree from dba_indexes where degree>'1' order by 1,2;
PROMPT
PROMPT 4.8、索引冗余
col owner for a25
col table_name for a30
col redundant_index for a30
col sufficient_index for a30
select o1.name owner, x.table_name, n1.name redundant_index, n2.name sufficient_index
from sys.icol$ ic1,
sys.icol$ ic2,
sys.ind$ i1,
sys.obj$ n1,
sys.obj$ n2,
sys.user$ o1,
sys.user$ o2,
dba_indexes x
where ic1.pos# = 1
and ic2.bo# = ic1.bo#
and ic2.obj# != ic1.obj#
and ic2.pos# = 1
and ic2.intcol# = ic1.intcol#
and i1.obj# = ic1.obj#
and bitand(i1.property, 1) = 0
and (select max(pos#) * (max(pos#) + 1) / 2
from sys.icol$
where obj# = ic1.obj#) =
(select sum(xc1.pos#)
from sys.icol$ xc1, sys.icol$ xc2
where xc1.obj# = ic1.obj#
and xc2.obj# = ic2.obj#
and xc1.pos# = xc2.pos#
and xc1.intcol# = xc2.intcol#)
and n1.obj# = ic1.obj#
and n2.obj# = ic2.obj#
and o1.user# = n1.owner#
and o2.user# = n2.owner#
and x.index_name = n1.name
and n1.name not like 'BIN$%'
and o1.name not in ('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS','RMAN')
order by 1,2;
PROMPT
PROMPT 4.9、检查碎片程度高的表
PROMPT -找出总大小超过1G，且使用率不足70%的表
col used_percent for a15
select owner,table_name,used used_mb,total total_mb,round(used * 100/total) || '%' used_percent, num_rows,last_analyzed from (
SELECT owner,
table_name,
ROUND(BLOCKS * 8192/1024/1024) total,
ROUND(num_rows * AVG_ROW_LEN/1024/1024) used,
num_rows,
last_analyzed
FROM dba_tables)
where total>1000 and round(used * 100/total) < 70
order by 1,2;
PROMPT
PROMPT 4.10、检查一些扩展异常的对象
PROMPT -对象的extent数量超过临界值的80%，需要move
SELECT Segment_Name, Segment_Type, TableSpace_Name,(Extents/Max_extents)*100 Percent
From sys.DBA_Segments
Where Max_Extents != 0 and (Extents/Max_extents)>=0.8
order By Percent;
PROMPT
PROMPT 4.11、检查对象的下一扩展与表空间的最大扩展值
PROMPT -表空间的剩余空间已经无法满足对象的下次扩展，需要扩表空间
SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,
(SELECT tablespace_name, max(bytes) as big_chunk
FROM dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk
UNION
SELECT a.index_name, a.next_extent, a.tablespace_name
FROM all_indexes a,
(SELECT tablespace_name, max(bytes) as big_chunk
FROM dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk;
PROMPT
PROMPT 4.12、查看行迁移或行链接
col "Percentage" for a15
SELECT 'Chained Rows' "Ratio"
, ROUND((SELECT value FROM V$SYSSTAT WHERE name = 'table fetch continued row')
/ (SELECT value FROM V$SYSSTAT WHERE name = 'table scan rows gotten')* 100, 2)||'%' "Percentage" FROM DUAL;
PROMPT
PROMPT 4.13、检查system表空间内的内容
select owner,table_name object_name,type from(
SELECT owner,table_name,'table' as type FROM dba_tables
where tablespace_name='SYSTEM' and
owner!='SYS' and owner not in('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS')
UNION
SELECT owner,index_name,'index' as type FROM dba_indexes
where tablespace_name='SYSTEM' and
owner!='SYS' and owner not in('SYS','SYSTEM','WMSYS','DBSNMP','XDB','EXFSYS','CTXSYS','MDSYS','OLAPSYS','SYSMAN','APEX_030200','OUTLN','ORDDATA','ORDSYS'))
order by 1,3;
PROMPT
PROMPT 4.14、检查回收站内的内容
SELECT owner, count(*), max(droptime) FROM dba_recyclebin group by owner;
PROMPT
PROMPT 五、dbtime和连接情况
PROMPT
PROMPT 5.1、查看当前实例最近一天的db_time
SELECT C.snap_id,to_char(b.begin_interval_time, 'yyyymmddhh24mi') begin_snapshot_time,c.DB_TIME
FROM (SELECT a.snap_id, TRUNC((DB_TIME_1 - lag(DB_TIME_1, 1, DB_TIME_1) over(PARTITION BY stat_name,instance_number ORDER BY snap_id)) /1000000 / 60) DB_TIME
FROM (SELECT a.snap_id,
a.dbid,
a.instance_number,
a.stat_name,
SUM(a.value) DB_TIME_1
FROM DBA_HIST_SYS_TIME_MODEL a
WHERE a.stat_name = 'DB time'
AND a.instance_number = (select instance_number from v$instance)
GROUP BY a.snap_id, a.dbid, a.instance_number, a.stat_name) a) c,
dba_hist_snapshot b
WHERE b.instance_number = (select instance_number from v$instance)
AND c.snap_id = b.snap_id
AND b.begin_interval_time > SYSDATE - 1
ORDER BY 1;
PROMPT
PROMPT 5.2、检查数据库连接情况
col machine for a30
SELECT username,program,machine,count(*) FROM v$session where username is not null group by username,program,machine order by 1,4 desc;
PROMPT
PROMPT 5.3、检查Oracle初始化文件中相关参数值
PROMPT -initial_allocation：软限制，limit_value：硬限制
PROMPT 若LIMIT_VALUE - MAX_UTILIZATION <= 5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过参数文件调整。
SELECT resource_name,max_utilization,initial_allocation,limit_value FROM v$resource_limit order by 4,3;
PROMPT
PROMPT 六、检查数据库event和sql
PROMPT
PROMPT 6.1、当前数据库的等待事件
col wait_class for a15
col event for a30
SELECT inst_id,wait_class,event,count(*) FROM gv$session WHERE wait_class <> 'Idle' group by inst_id,wait_class,event order by inst_id,wait_class,event;
PROMPT
PROMPT 6.2、top5-event
PROMPT -TOTAL_WAITS：总等待的次数，AVERAGE_WAIT：每次等待的时间(单位：1s/100)
col EVENT for a30
SELECT * FROM (SELECT EVENT,TOTAL_WAITS,AVERAGE_WAIT,WAIT_CLASS# FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
PROMPT
PROMPT 6.3、物理读top10-sql
col sql_text for a65
select * from
(select sql_id, replace(substr(sql_text,1,300),chr(13),'') sql_text, disk_reads,
executions, round(disk_reads/executions) "DiskReads/Exec"
from v$sqlarea
where disk_reads > 0 and executions>0
order by disk_reads desc)
where rownum <= 10;
PROMPT
PROMPT 6.4、逻辑读top10-sql
select * from
(select sql_id, replace(substr(sql_text,1,300),chr(13),'') sql_text, buffer_gets,
executions, round(buffer_gets/executions) "Gets/Exec"
from v$sqlarea
where buffer_gets > 0 and executions>0
order by buffer_gets desc)
where rownum <= 10;
PROMPT
PROMPT 6.5、物理读top10-object
col value for 999999999999
col object_name for a30
SELECT * FROM (SELECT owner,object_name,value FROM v$segment_statistics where statistic_name='physical reads' order by value desc) where rownum<=10;
PROMPT
PROMPT 6.6、热segment-top10
col object_name for a30
col OWNER for a20
SELECT * FROM
(SELECT
ob.owner, ob.object_name,ob.object_type, sum(b.tch) Touchs
FROM x$bh b , dba_objects ob
where b.obj = ob.data_object_id
and b.ts# > 0
group by ob.owner, ob.object_name, ob.object_type
order by sum(tch) desc)
where rownum <=10;
PROMPT
PROMPT 6.7、查看热点数据文件(从单块读取时间判断)
PROMPT -CS：单块读的时间，READTIM：总的读时间，WRITETIM：总的写时间。单位都为毫秒
COL file_name FOR a60
SELECT * from (select t.file_name,
t.tablespace_name,
round(s.singleblkrdtim / s.singleblkrds /10, 3) AS CS,
round(s.READTIM/10),
round(s.WRITETIM/10)
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id order by cs desc) where rownum <= 10;
PROMPT
PROMPT 6.8、检查运行很久的SQL
COL USERNAME FOR A12
COL OPNAME FOR A16
COL PROGRESS FOR A8
select b.username,
a.sql_text,
b.SID,
b.SERIAL#,
b.SOFAR,
b.TOTALWORK,
ROUND(b.SOFAR / b.TOTALWORK * 100, 2) "%_COMPLETE"
from gv$sql a, gv$session_longops b
where a.SQL_ID = b.SQL_ID
and a.inst_id=b.inst_id
and b.TOTALWORK <> 0
and b.SOFAR <> b.TOTALWORK;
PROMPT
PROMPT 6.9、检查执行计划最近一周发生突变的sql
with A as (
SELECT
SQL.sql_id sql_id,
SQL.PLAN_HASH_VALUE,
min(to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')) Date_Time,
sum(SQL.executions_delta) executions_delta,
round(avg(SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta))) avg_lio,
round(avg((SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta)),2) avg_cputime_s ,
round(avg((SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta)),2) avg_etime_s,
'HIS' as Status
FROM
dba_hist_sqlstat SQL,
dba_hist_sql_plan spl,
dba_hist_snapshot s
WHERE
SQL.dbid =(select dbid from v$database)
and sql.sql_id=spl.sql_id
and spl.object_owner not in ('SYS','SYSTEM','SYSMAN')
and s.snap_id = SQL.snap_id
and s.BEGIN_INTERVAL_TIME between trunc(sysdate-7) and trunc(sysdate)
and SQL.executions_delta>100
and SQL.PLAN_HASH_VALUE <> 0
group by SQL.sql_id,SQL.PLAN_HASH_VALUE
union all
select B.sql_id,B.plan_hash_value,to_char(B.LAST_ACTIVE_TIME,'mm/dd/yy_hh24mi'),b.EXECUTIONS,b.BUFFER_GETS/b.EXECUTIONS,b.CPU_TIME/1000000/b.EXECUTIONS,b.ELAPSED_TIME/1000000/b.EXECUTIONS
,'NOW' from v$sqlstats B where b.EXECUTIONS>100
)
select sql_id,PLAN_HASH_VALUE,Date_Time,executions_delta,round(avg_lio),trunc(avg_cputime_s,3),trunc(avg_etime_s,3),Status from A
where sql_id in(select sql_id from(select sql_id,count(distinct PLAN_HASH_VALUE) from A group by sql_id having count(distinct PLAN_HASH_VALUE)>1)) order by sql_id,Date_Time;
spool OFF
View Code
