oracle数据库性能健康检查脚本.docVIP

oracle数据库性能健康检查脚本 *********************** 查看 茶木信息 ^database概况信息检查 #检查database基木信息 select * from v$version; select name , open_mode, 1og_mode from v$database; seiect instance_number, instance_name ,status from gv$instance; show parameter cpu_c show parameter block_size select group#,thread#,members, bytes/1024/1024 from gv$log; show sga select count (*) from v$controlfile select count (*) from v$tempfile; select count (*) from v$datafile; #检查表空间数据文件信息 col tablespace_name for a30 select tablespace_name , sum (bytes)/1024/1024 from dba_temp_files group by tablespace_namc; #检查表空间 SELECT TABLESPACE_NAME, EXTENTJVVNAGEMENT, ALLOCAT1ON_TYPE, SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES; #检查数据文件状态 select count (*),status from v$datafile group by status; #检查表空间使川情况 select f. tablespace_name, a. total, f. free, (a. total-f. free)/1024 used SIZE (G)，z ,round((f. free/a. total)*100) % Free from (select tablespace_ name, sum (bytes/ (1024*1024)) total from dba_data_f iles group by tablespace_name) a, (select tablespace_name, round (sum(bytes/(1024*1024))) free from dba_free_space group by tablespace name) f WHERE a. tablespace_name = f. tablespace_name (+) order by % Free / #查询临吋segment使用悄况 COL username FORMAT alO; COL segtype FORMAT alO; SELECT username, segtype, extents Extents Allocated ,blocks Blocks Allocated7 FROM v$tcmpseg usage; #查看数据文件信息，若文件较多可以根据碍要字段进行排序输出top 10 SELECT fs. phyrds Reads, fs. phywrts Writes ,fs. avgiotim Average 1/0 Time, df. name z/Datafilez， FROM v$datafilcdf, v$f订estatfs WHERE df. file# = fs. file#; #查看所有数据文件i/o情况,若文件太多,可以改写为top 10 select *( order by xx desc) where rownum=10 COL ts FORMAT alO HEADING Tablespace; COL reads FORMAT 999990; COL writes FORMAT 999990; COL br FORMAT 999990 HEADING 〃BlksRead〃； COL bw FORMAT 999990 HEADING 〃BlksWrite〃； COL rtime FORMAT 999990; COL wtime FORMAT 999990; SELECT ts. namc AS ts, fs. phyrds Reads, fs. phywrts Writes ,fs. phyblkrd AS br, fs. phyblkwrt AS bw ,fs. readtim RTime, fs. writetim WTime FROM v$tablespacets,