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 https://www.360docs.net/doc/3c1538397.html, "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 https://www.360docs.net/doc/3c1538397.html, = 'sorts (disk)' and https://www.360docs.net/doc/3c1538397.html, = '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 https://www.360docs.net/doc/3c1538397.html, ts_name,

https://www.360docs.net/doc/3c1538397.html, 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 https://www.360docs.net/doc/3c1538397.html, "Table Space", https://www.360docs.net/doc/3c1538397.html, "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, '') 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

--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 (Rows 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;

--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) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000

order by 5 desc;