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

Oracle10g数据库巡检工具使用方法及SQL汇总一、 使用方法使用该工具可以大大简化数据库巡检的工作，并可生成HTML文档，方便阅读和保存。使用软件前需要修改系统时间，一般向前调整2年及可，如：今天是2010年5月10日，使用软件前调整系统时间为2008年5月10日后，再运行该软件即可。执行巡检完成后会在HCReport内生成一个HTML文件。工具的运行界面如下：二、SQL汇总-1. 数据库概要2-2. 参数文件(是spfile还是pfile)3-3. 非默认的参数3-4. 控制文件及其状态3-5. 表空间及数据文件3-6. 重做日志文件信息3-7. 内存分配概况4-8. Library Cache Reload Ratio(1%)4-9. Data Dictionary Miss Ratio(90%)5-12. DB Buffer Cache Advice5-13. 磁盘排序(5%)5-14. Log Buffer latch Contention(=2倍)或过小(1)已经有90%被使用了的Segments9-25. 已经分配超过100 Extents的Segments9-26. 因表空间空间不够将导致不能扩展的Objects9-27. 没有主键的非系统表10-28. 没有索引的外键10-29. 建有6个以上索引的非系统表11-30. 指向对象不存在的Public同义词11-31. 指向对象不存在的非Public同义词11-32. 没有授予给任何角色和用户的角色12-33. 将System表空间作为临时表空间的用户(除Sys外)12-34. 将System表空间作为默认表空间的用户(除Sys外)12-35. 没有授予给任何用户的profiles13-36. 没有和Package相关联的Package Body13-37. 被Disabled的约束13-38. 被Disabled的触发器13-39. Invalid Objects14-40. 执行失败或中断的Jobs14-41. 当前未执行且下一执行日期已经过去的Jobs14-42. 含有未分析的非系统表的Schemas14-43. 含有未分析的非系统分区表的Schemas15-44. 含有未分析的非系统索引的Schemas15-45. 含有未分析的非系统分区索引的Schemas15-46. 死锁检测15-47. top I/O Wait16-48. top 10 wait16-49. Top 10 bad SQL16-50. Top most expensive SQL (Buffer Gets by Executions)17-51. Top most expensive SQL (Physical Reads by Executions)17-52. Top most expensive SQL (Rows Processed by Executions)17-53. Top most expensive SQL (Buffer Gets vs Rows Processed)18-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 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(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 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 b where = sorts (disk) and = sorts (memory);-14. Log Buffer latch Contention( 30 and s.seg_count 50;-21. 表空间上的I/O分布SELECT ts_name, file_name, s.phyrdsphy_reads, s.phyblkrdphy_blockreads, s.phywrtsphy_writes, s.phyblkwrtphy_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%)的SegmentsSelect 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) = 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_extents From dba_segments where 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_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. 因表空间空间不够将导致不能扩展的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_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. 没有授予给任何用户的profilesSelect distinct profile from dba_profiles minus Select distinct profile from dba_users;-36. 没有和Package相关联的Package BodySelect 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, ) table_name, trigger_name from dba_triggers where status = DISABLED ORDER BY 1,2,3;-39. Invalid ObjectsSelect OWNER, OBJECT_NAME, OBJECT_TYPE from dba_objects where 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, what from dba_jobs where broken=Y or failures0;-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, what from dba_jobs where job not in (select job from dba_jobs_running) and broken=N and next_datesysdate;-42. 含有未分析的非系统表的SchemasSelect 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. 含有未分析的非系统分区表的SchemasSelect 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. 含有未分析的非系统索引的SchemasSelect 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. 含有未分析的非系统分区索引的SchemasSelect 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, cess 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 WaitSELECT /*+ 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 waitselect * from ( select event,sum(decode(wait_Time,0,0,1)