Oracle10g数据库巡检工具使用方法及SQL汇总

Oracle10g数据库巡检工具使用方法及SQL汇总一、使用方法使用该工具可以大大简化数据库巡检的工作，并可生成HTML文档，方便阅读和保存。

使用软件前需要修改系统时间，一般向前调整2年及可，如：今天是2010年5月10日，使用软件前调整系统时间为2008年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 "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 eWHERE 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$parameterwhere 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_filesorder 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 fwhere l.group#=f.group#;--7. 内存分配概况select name,to_char(value) "value(Byte)"from v$sgaunion allselect name,valuefrom v$parameterwhere 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_statisticsWHERE name = 'DEFAULT';--12. DB Buffer Cache Adviceselect 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 bwhere = 'sorts (disk)' and = '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) bytesfrom dba_data_files group by tablespace_name) a,(select tablespace_name, sum(bytes) bytesfrom dba_free_space group by tablespace_name) fWHERE 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) bytesfrom dba_free_space group by File_id) fWHERE a.file_id=f.file_id(+)order by a.tablespace_name,a.File_id;--17. 不使用临时文件的临时表空间select tablespace_name,contents from dba_tablespaceswhere contents='TEMPORARY' and tablespace_name not in(select tablespace_name from dba_temp_files);--18. 无效的数据文件(offline)select f.tablespace_name,f.file_name,d.statusfrom dba_data_files f,v$datafile dwhere d.status='OFFLINE' and f.file_id=File#(+);--19. 处于恢复模式的文件select f.tablespace_name,f.file_namefrom dba_data_files f, v$recover_file rwhere 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 holesfrom (Select tablespace_name, count(*) seg_countfrom dba_segments group by tablespace_name) s,(Select tablespace_name, count(*) hole_countfrom dba_free_space group by tablespace_name) fwhere s.tablespace_name = f.tablespace_nameand s.tablespace_name in (Select tablespace_namefrom dba_tablespaces where contents = 'PERMANENT')And s.tablespace_name not in ('SYSTEM')and 100 * f.hole_count / (f.hole_count + s.seg_count) > 30and s.seg_count > 50;--21. 表空间上的I/O分布SELECT ts_name, file_name,s.phyrds phy_reads,s.phyblkrd phy_blockreads,s.phywrts phy_writes,s.phyblkwrt phy_blockwritesFROM gv$tablespace t,gv$datafile f,gv$filestat sWHERE t.ts# = f.ts#andf.file# = s.file#ORDER BY s.phyrds desc, s.phywrts desc;--22. 数据文件上的I/O分布Select "Table Space", "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_wrtsfrom V$filestat) fstotWHERE D.FILE# = FS.FILE# AND D.TS# = TS.TS#;--23. Next Extent 相对于段当前已分配字节过大(>=2倍)或过小(<10%)的SegmentsSelect InitCap(SEGMENT_TYPE) "Type", OWNER, SEGMENT_NAME, BYTES, NEXT_EXTENT, ROUND(100 * NEXT_EXTENT / BYTES) "Percent(Next/Bytes)"FROM DBA_SEGMENTSWHERE ((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%被使用了的SegmentsSelect segment_type, owner, Segment_name, Tablespace_name,partition_name, round(bytes /1024/1024) "Size(MB)", extents, max_extentsFrom dba_segmentswhere segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')and extents >= (1 - ( 10 / 100)) * max_extents and max_extents > 1order by bytes / max_extents desc;--25. 已经分配超过100 Extents的SegmentsSelect segment_type, owner, segment_name, extents, partition_namefrom dba_segmentswhere 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. 因表空间空间不够将导致不能扩展的ObjectsSelect 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_mbfrom dba_segments a,(Select df.tablespace_name, nvl(max(fs.bytes), 0) freefrom dba_data_files df,dba_free_space fswhere 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)) autoextensiblefrom dba_data_filesgroup by tablespace_name) cwhere a.tablespace_name = b.tablespace_nameand a.tablespace_name = c.tablespace_nameand ((c.autoextensible = 0) or ((c.autoextensible > 0)and (a.next_extent > c.morebytes)))and a.next_extent > b.freeorder by 1;--27. 没有主键的非系统表Select owner, table_namefrom dba_tableswhere owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') minusSelect owner, table_namefrom dba_constraintswhere 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_nameFROM all_cons_columns acc, all_constraints acWHERE ac.constraint_name = acc.constraint_nameAND 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.positionFROM all_cons_columns acc, all_constraints acWHERE ac.constraint_name = acc.constraint_nameAND ac.constraint_type = 'R'MINUSSELECT table_owner, table_name, column_name, column_positionFROM 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_countfrom dba_indexeswhere table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB') having count(*) > 6group by table_owner, table_nameorder by 3 desc;--30. 指向对象不存在的Public同义词Select s.synonym_name, s.table_owner, s.table_namefrom sys.DBA_synonyms swhere not exists (Select 'x'from sys.DBA_objects owhere o.owner = s.table_ownerand 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_namefrom sys.DBA_synonyms swhere not exists (Select 'x'from sys.DBA_objects owhere o.owner = s.table_ownerand o.object_name = s.table_name)and db_link is null and s.owner <> 'PUBLIC'order by 1;--32. 没有授予给任何角色和用户的角色Select rolefrom dba_roles rwhererole 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') andnot exists (Select 1from dba_role_privs pwhere p.granted_role = r.role);--33. 将System表空间作为临时表空间的用户(除Sys外)Select usernamefrom dba_userswhere temporary_tablespace = 'SYSTEM';--34. 将System表空间作为默认表空间的用户(除Sys外)Select usernamefrom dba_userswhere default_tablespace = 'SYSTEM'and username <> 'SYS';--35. 没有授予给任何用户的profilesSelect distinct profilefrom dba_profilesminusSelect distinct profilefrom dba_users;--36. 没有和Package相关联的Package BodySelect pb.owner, pb.object_namefrom dba_objects pbwhere pb.object_type = 'PACKAGE BODY'and not exists (Select 1from dba_objects pwhere p.object_type = 'PACKAGE'and p.owner = pb.ownerand p.object_name = pb.object_name)order by 1,2;--37. 被Disabled的约束Select owner, table_name, constraint_name, CONSTRAINT_TYPEfrom dba_constraintswhere status = 'DISABLED'ORDER BY 1,2,3;--38. 被Disabled的触发器Select owner, nvl(table_name, '<system trigger>') table_name, trigger_name from dba_triggerswhere status = 'DISABLED'ORDER BY 1,2,3;--39. Invalid ObjectsSelect OWNER, OBJECT_NAME, OBJECT_TYPEfrom dba_objectswhere status = 'INVALID'ORDER BY 1,2,3;--40. 执行失败或中断的Jobsselect 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, whatfrom dba_jobs where broken='Y' or failures>0;--41. 当前未执行且下一执行日期已经过去的Jobsselect 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, whatfrom dba_jobswhere job not in(select job from dba_jobs_running)and broken='N' and next_date<sysdate;--42. 含有未分析的非系统表的SchemasSelect distinct owner "Schema"from DBA_tableswhere num_rows is nulland owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB')order by 1;--43. 含有未分析的非系统分区表的SchemasSelect distinct table_owner "Schema"from DBA_tab_partitionswhere num_rows is nulland table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB')order by 1;--44. 含有未分析的非系统索引的SchemasSelect distinct owner "Schema" from DBA_indexeswhere leaf_blocks is nulland owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS','ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB')order by 1;--45. 含有未分析的非系统分区索引的SchemasSelect distinct index_owner "Schema"from DBA_ind_partitionswhere leaf_blocks is nulland 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_IDfrom v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPSwhere lo.OBJECT_ID = dob.OBJECT_IDand lo.SESSION_ID = vss.SIDAND VSS.paddr = VPS.addrorder by 2,3,DOB.object_name;--47. top I/O WaitSELECT /*+ rule */ event,segment_type,segment_name,file_id,block_id,blocksFROM dba_extents, gv$session_waitWHERE p1text='file#'AND p2text='block#'AND p1=file_id andp2 between block_id AND block_id+blocksORDER BY segment_type,segment_name;--48. top 10 waitselect *from (select event,sum(decode(wait_Time,0,0,1)) "Prev",sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Total"from v$session_Waitgroup by eventorder by 4 desc)where rownum<=10;--49. Top 10 bad SQLSELECT *FROM (SELECT parsing_user_id executions,sorts,command_type,disk_reads,sql_textFROM v$sqlareaORDER 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_textfrom v$sqlareawhere buffer_gets > 50000order 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_textfrom v$sqlareawhere disk_reads > 10000order by disk_reads desc;--52. Top most expensive SQL (Rows Processed by Executions)select rows_processed,executions,rows_processed / decode(executions,0,1, executions) rows_per_exec,hash_value,sql_textfrom v$sqlareawhere rows_processed > 10000order by rows_processed desc;--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_textfrom v$sqlareawhere decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000order by 5 desc;。