Oracle10g数据库巡检工具使用方法及SQL汇总
一、 使用方法
使用该工具可以大大简化数据库巡检的工作，并可生成HTML文档，方便阅读和保存。使用软件前需要修改系统时间，一般向前调整2年及可，如：今天是20xx年5月10日，使用软件前调整系统时间为20xx年5月10日后，再运行该软件即可。
执行巡检完成后会在HCReport内生成一个HTML文件。
工具的运行界面如下：
二、SQL汇总
--1. 数据库概要 ............................................................................................................... 3
--2. 参数文件(是spfile还是pfile) ..................................................................................... 3
--3. 非默认的参数 ............................................................................................................ 4
--4. 控制文件及其状态 ..................................................................................................... 4
--5. 表空间及数据文件 ..................................................................................................... 4
--6. 重做日志文件信息 ..................................................................................................... 4
--7. 内存分配概况 ............................................................................................................ 4
--8. Library Cache Reload Ratio(<1%) ................................................................................... 5
--9. Data Dictionary Miss Ratio(<15%) ................................................................................. 5
--10. 共享池建议.............................................................................................................. 5
--11. DB Buffer Cache(Default) Hit Ratio(>90%) .................................................................... 5
--12. DB Buffer Cache Advice .............................................................................................. 6
--13. 磁盘排序(<5%) ......................................................................................................... 6
--14. Log Buffer latch Contention(<1%) ................................................................................ 6
--15. 表空间状态及其大小使用情况.................................................................................. 6
--16. 数据文件状态及其大小使用情况 .............................................................................. 7
--17. 不使用临时文件的临时表空间.................................................................................. 7
--18. 无效的数据文件(offline) ........................................................................................... 7
--19. 处于恢复模式的文件................................................................................................ 7
--20. 含有50个以上的Extent且30%以上碎片的表空间 ................................................... 8
--21. 表空间上的I/O分布 ................................................................................................ 8
--22. 数据文件上的I/O分布............................................................................................. 9
--23. Next Extent 相对于段当前已分配字节过大(>=2倍)或过小(<10%)的Segments............. 9
--24. Max Extents(>1)已经有90%被使用了的Segments ...................................................... 9
--25. 已经分配超过100 Extents的Segments ................................................................... 10
--26. 因表空间空间不够将导致不能扩展的Objects ......................................................... 10
--27. 没有主键的非系统表.............................................................................................. 11
--28. 没有索引的外键..................................................................................................... 11
--29. 建有6个以上索引的非系统表................................................................................ 11
--30. 指向对象不存在的Public同义词 ............................................................................ 12
--31. 指向对象不存在的非Public同义词 ........................................................................ 12
--32. 没有授予给任何角色和用户的角色......................................................................... 12
--33. 将System表空间作为临时表空间的用户(除Sys外) ................................................ 13
--34. 将System表空间作为默认表空间的用户(除Sys外) ................................................ 13
--35. 没有授予给任何用户的profiles .............................................................................. 13
--36. 没有和Package相关联的Package Body .................................................................. 13
--37. 被Disabled的约束 ................................................................................................. 14
--38. 被Disabled的触发器 ............................................................................................. 14
--39. Invalid Objects......................................................................................................... 14
--40. 执行失败或中断的Jobs .......................................................................................... 14
--41. 当前未执行且下一执行日期已经过去的Jobs .......................................................... 15
--42. 含有未分析的非系统表的Schemas ......................................................................... 15
--43. 含有未分析的非系统分区表的Schemas .................................................................. 15
--44. 含有未分析的非系统索引的Schemas ..................................................................... 16
--45. 含有未分析的非系统分区索引的Schemas .............................................................. 16
--46. 死锁检测 ............................................................................................................... 16
--47. top I/O Wait ............................................................................................................ 16
--48. top 10 wait.............................................................................................................. 17
--49. Top 10 bad SQL ........................................................................................................ 17
--50. Top most expensive SQL (Buffer Gets by Executions)................................................... 17
--51. Top most expensive SQL (Physical Reads by Executions) .............................................. 18
--52. Top most expensive SQL (Rows Processed by Executions) ............................................ 18
--53. Top most expensive SQL (Buffer Gets vs Rows Processed)............................................ 19
--1. 数据库概要
select a.name "DB Name",
e.global_name "Global Name",
c.host_name "Host Name",
c.instance_name "Instance Name" ,
DECODE(c.logins,'RESTRICTED','YES','NO') "Restricted Mode",
a.log_mode "Archive Log Mode"
FROM v$database a, v$version b, v$instance c,global_name e
WHERE b.banner LIKE '%Oracle%';
--2. 参数文件(是spfile还是pfile)
select nvl(value,'pfile') "Parameter_File"
from v$parameter where Name='spfile';
--3. 非默认的参数 select name, rtrim(value) "pvalue" from v$parameter
where isdefault = 'FALSE'
order by name;
--4. 控制文件及其状态 select Name,Status from v$controlfile;
--5. 表空间及数据文件 select tablespace_name,file_name,
bytes/1024/1024 "Total Size(MB)",autoExtensible "Auto" from dba_data_files
order by tablespace_name,file_id;
--6. 重做日志文件信息 select f.group#, f.member "Redo File", f.Type, l.Status,l.bytes/1024/1024 "Size(MB)" from v$log l,v$logfile f
where l.group#=f.group#;
--7. 内存分配概况 select name,to_char(value) "value(Byte)" from v$sga
union all
select name,value
from v$parameter
where name in
('shared pool_size','large_pool_size',
'java_pool_size','lock_sga');
--8. Library Cache Reload Ratio(<1%)
Select round((Sum(Reloads) / Sum (Pins)) * 100, 4) "LC_Reload_Ratio%"
From V$Librarycache;
--9. Data Dictionary Miss Ratio(<15%)
Select Round((((sum(GetMisses)) / sum(Gets)) * 100),4) "DC_Miss_Ratio%"
From V$rowcache;
--10. 共享池建议
select shared_pool_size_for_estimate "Shared Pool Size(estimate)",
SHARED_POOL_SIZE_FACTOR "Factor",
estd_lc_size "Libarary Cache Size",
estd_lc_time_saved "time Saved"
from v$shared_pool_advice;
--11. DB Buffer Cache(Default) Hit Ratio(>90%)
Select round(100 * (1-(physical_reads/(db_block_gets+consistent_gets))), 4) "BC_Hit _Ratio" FROM v$buffer_pool_statistics
WHERE name = 'DEFAULT';
--12. DB Buffer Cache Advice
select Name "Pool Name",Block_size,SIZE_FOR_ESTIMATE "Buffer Size",
SIZE_FACTOR "Factor",ESTD_PHYSICAL_READ_FACTOR "Phy_Read_Factor", ESTD_PHYSICAL_READS "ESTD_PHY_READS"
from v$db_cache_advice where ADVICE_STATUS='ON';
--13. 磁盘排序(<5%)
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) "Disk_Sort_Ratio％"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)' and b.name = 'sorts (memory)';
--14. Log Buffer latch Contention(<1%) SELECT name "Redo Name", gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,round(misses/gets*100,3)) "Miss_Ratio％",
Decode(immediate_gets+immediate_misses,0,0,
round( immediate_misses/(immediate_gets+immediate_misses)*100,3)) "Immediate Misses Ratio%"
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
--15. 表空间状态及其大小使用情况 SELECT d.tablespace_name "Name", d.status "Status", d.contents "Type", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (MB)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0),0)/1024/1024, '99G999G990D900') "Used (MB)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Used％" 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(+);
--16. 数据文件状态及其大小使用情况 SELECT a.tablespace_name "TableSpace Name", a.File_Name "File Name",
a.status "Status", a.AutoExtensible "Auto",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (MB)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0),0)/1024/1024, '99G999G990D900') "Used (MB)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Used %" FROM dba_data_files a,
(select file_id, sum(bytes) bytes
from dba_free_space group by File_id) f
WHERE a.file_id=f.file_id(+)
order by a.tablespace_name,a.File_id;
--17. 不使用临时文件的临时表空间
select tablespace_name,contents from dba_tablespaces
where contents='TEMPORARY' and tablespace_name not in
(select tablespace_name from dba_temp_files);
--18. 无效的数据文件(offline)
select f.tablespace_name,f.file_name,d.status
from dba_data_files f,v$datafile d
where d.status='OFFLINE' and f.file_id=File#(+);
--19. 处于恢复模式的文件
select f.tablespace_name,f.file_name
from dba_data_files f, v$recover_file r
where f.file_id=r.file#;
--20. 含有50个以上的Extent且30%以上碎片的表空间
select s.tablespace_name,
round(100 * f.hole_count / (f.hole_count + s.seg_count)) pct_fragmented, s.seg_count segments, f.hole_count holes
from (Select tablespace_name, count(*) seg_count
from dba_segments group by tablespace_name) s,
(Select tablespace_name, count(*) hole_count
from dba_free_space group by tablespace_name) f
where s.tablespace_name = f.tablespace_name
and s.tablespace_name in (Select tablespace_name
from dba_tablespaces where contents = 'PERMANENT')
And s.tablespace_name not in ('SYSTEM')
and 100 * f.hole_count / (f.hole_count + s.seg_count) > 30
and s.seg_count > 50;
--21. 表空间上的I/O分布
SELECT t.name ts_name,
f.name file_name,
s.phyrds phy_reads,
s.phyblkrd phy_blockreads,
s.phywrts phy_writes,
s.phyblkwrt phy_blockwrites
FROM gv$tablespace t,
gv$datafile f,
gv$filestat s
WHERE t.ts# = f.ts#
and
f.file# = s.file#
ORDER BY s.phyrds desc, s.phywrts desc;
--22. 数据文件上的I/O分布
Select ts.NAME "Table Space", D.NAME "File Name",
FS.PHYRDS "Phys Rds",
decode(fstot.sum_ph_rds, 0, 0,
round(100 * FS.PHYRDS / fstot.sum_ph_rds, 2)) "% Phys Rds", FS.PHYWRTS "Phys Wrts",
decode(fstot.sum_ph_wrts, 0, 0,
round(100 * FS.PHYWRTS / fstot.sum_ph_wrts, 2)) "% Phys Wrts" FROM V$FILESTAT FS, V$DATAFILE d, V$tablespace ts,
(select sum(phyrds) sum_ph_rds, sum(phywrts) sum_ph_wrts, sum(phyblkrd) sum_bl_rds, sum(phyblkwrt) sum_bl_wrts from V$filestat) fstot
WHERE D.FILE# = FS.FILE# AND D.TS# = TS.TS#;
--23. Next Extent 相对于段当前已分配字节过大(>=2倍)或过小(<10%)的Segments Select InitCap(SEGMENT_TYPE) "Type", OWNER, SEGMENT_NAME, BYTES, NEXT_EXTENT, ROUND(100 * NEXT_EXTENT / BYTES) "Percent(Next/Bytes)" FROM DBA_SEGMENTS
WHERE ((ROUND(100 * NEXT_EXTENT / BYTES) < 10) OR
(ROUND(100 * NEXT_EXTENT / BYTES) >= 200))
AND SEGMENT_TYPE NOT IN ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') order by 2,3,1;
--24. Max Extents(>1)已经有90%被使用了的Segments
Select segment_type, owner, Segment_name, Tablespace_name,
partition_name, round(bytes /1024/1024) "Size(MB)", extents, max_extents From dba_segments
where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO') and extents >= (1 - ( 10 / 100)) * max_extents and max_extents > 1 order by bytes / max_extents desc;
--25. 已经分配超过100 Extents的Segments Select segment_type, owner, segment_name, extents, partition_name
from dba_segments
where segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB')
and extents > 100;
--26. 因表空间空间不够将导致不能扩展的Objects
Select a.tablespace_name, a.owner,
decode(a.partition_name, null, a.segment_name,
a.segment_name || '.' || a.partition_name) "Segment Name",
a.extents, round(next_extent/1024) next_extent_kb,
round(b.free / 1024) ts_free_kb,
round(c.morebytes / 1024 / 1024) ts_growth_mb
from dba_segments a,
(Select df.tablespace_name, nvl(max(fs.bytes), 0) free
from dba_data_files df,
dba_free_space fs
where df.file_id = fs.file_id (+)
group by df.tablespace_name) b,
(Select tablespace_name, max(maxbytes - bytes) morebytes,
sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible
from dba_data_files
group by tablespace_name) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
and ((c.autoextensible = 0) or ((c.autoextensible > 0)
and (a.next_extent > c.morebytes)))
and a.next_extent > b.free
order by 1;
--27. 没有主键的非系统表
Select owner, table_name
from dba_tables
where owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') minus
Select owner, table_name
from dba_constraints
where constraint_type = 'P'
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB');
--28. 没有索引的外键
SELECT acc.owner,acc.table_name,acc.constraint_name,acc.column_name FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
and acc.owner not in ('SYS','SYSTEM')
AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN
(SELECT acc.owner, acc.table_name, acc.column_name, acc.position FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position FROM all_ind_columns)
ORDER BY acc.owner,acc.table_name, acc.constraint_name,acc.column_name;
--29. 建有6个以上索引的非系统表 Select table_owner, table_name, count(*) index_count
from dba_indexes
where table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') having count(*) > 6
group by table_owner, table_name
order by 3 desc;
--30. 指向对象不存在的Public同义词 Select s.synonym_name, s.table_owner, s.table_name
from sys.DBA_synonyms s
where not exists (Select 'x'
from sys.DBA_objects o
where o.owner = s.table_owner
and o.object_name = s.table_name)
and db_link is null and s.owner = 'PUBLIC'
order by 1;
--31. 指向对象不存在的非Public同义词 Select s.owner, s.synonym_name, s.table_owner, s.table_name
from sys.DBA_synonyms s
where not exists (Select 'x'
from sys.DBA_objects o
where o.owner = s.table_owner
and o.object_name = s.table_name)
and db_link is null and s.owner <> 'PUBLIC'
order by 1;
--32. 没有授予给任何角色和用户的角色 Select role
from dba_roles r
where
role not in (
'CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE',
'EXP_FULL_DATABASE','WM_ADMIN_ROLE','IMP_FULL_DATABASE',
'RECOVERY_CATALOG_OWNER','AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE','GLOBAL_AQ_USER_ROLE','OEM_MONITOR','HS_ADMIN_ROLE') and
not exists (Select 1
from dba_role_privs p
where p.granted_role = r.role);
--33. 将System表空间作为临时表空间的用户(除Sys外)
Select username
from dba_users
where temporary_tablespace = 'SYSTEM';
--34. 将System表空间作为默认表空间的用户(除Sys外)
Select username
from dba_users
where default_tablespace = 'SYSTEM'
and username <> 'SYS';
--35. 没有授予给任何用户的profiles
Select distinct profile
from dba_profiles
minus
Select distinct profile
from dba_users;
--36. 没有和Package相关联的Package Body Select pb.owner, pb.object_name
from dba_objects pb
where pb.object_type = 'PACKAGE BODY'
and not exists (Select 1
from dba_objects p
where p.object_type = 'PACKAGE'
and p.owner = pb.owner
and p.object_name = pb.object_name) order by 1,2;
--37. 被Disabled的约束
Select owner, table_name, constraint_name, CONSTRAINT_TYPE
from dba_constraints
where status = 'DISABLED'
ORDER BY 1,2,3;
--38. 被Disabled的触发器
Select owner, nvl(table_name, '<system trigger>') table_name, trigger_name from dba_triggers
where status = 'DISABLED'
ORDER BY 1,2,3;
--39. Invalid Objects
Select OWNER, OBJECT_NAME, OBJECT_TYPE
from dba_objects
where status = 'INVALID'
ORDER BY 1,2,3;
--40. 执行失败或中断的Jobs
select job, to_char(last_date,'yyyy-mm-dd hh24:mi:ss') "Last Date", to_char(this_date,'yyyy-mm-dd hh24:mi:ss') "This Date", broken,failures, schema_user, what
from dba_jobs where broken='Y' or failures>0;
--41. 当前未执行且下一执行日期已经过去的Jobs
select job, to_char(last_date,'yyyy-mm-dd hh24:mi:ss') "Last Date",
to_char(this_date,'yyyy-mm-dd hh24:mi:ss') "This Date",
broken,failures, schema_user, what
from dba_jobs
where job not in
(select job from dba_jobs_running)
and broken='N' and next_date<sysdate;
--42. 含有未分析的非系统表的Schemas Select distinct owner "Schema"
from DBA_tables
where num_rows is null
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') order by 1;
--43. 含有未分析的非系统分区表的Schemas
Select distinct table_owner "Schema"
from DBA_tab_partitions
where num_rows is null
and table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') order by 1;
--44. 含有未分析的非系统索引的Schemas Select distinct owner "Schema" from DBA_indexes
where leaf_blocks is null
and owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') order by 1;
--45. 含有未分析的非系统分区索引的Schemas
Select distinct index_owner "Schema"
from DBA_ind_partitions
where leaf_blocks is null
and index_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') order by 1;
--46. 死锁检测
SELECT dob.OBJECT_NAME Table_Name,lo.SESSION_ID,vss.SERIAL#,
vss.action Action,vss.osuser OSUSER,
vss.process AP_Process_ID,VPS.SPID DB_Process_ID
from v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS where lo.OBJECT_ID = dob.OBJECT_ID
and lo.SESSION_ID = vss.SID
AND VSS.paddr = VPS.addr
order by 2,3,DOB.object_name;
--47. top I/O Wait
SELECT /*+ rule */ event,segment_type,segment_name,file_id,block_id,blocks FROM dba_extents, gv$session_wait
WHERE p1text='file#'
AND p2text='block#'
AND p1=file_id and
p2 between block_id AND block_id+blocks
ORDER BY segment_type,segment_name;
--48. top 10 wait
select *
from (
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Total"
from v$session_Wait
group by event
order by 4 desc
)
where rownum<=10;
--49. Top 10 bad 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;
--50. Top most expensive SQL (Buffer Gets by Executions)
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;
--51. Top most expensive SQL (Physical Reads by Executions)
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;
--52. Top most expensive SQL
Processed by Executions)
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;
(Rows
--53. Top most expensive SQL (Buffer Gets vs Rows Processed)
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)
decode(rows_processed,0,1,rows_processed) > 10000
order by 5 desc; * buffer_gets/