oracle数据库性能健康检查脚本***********************查看基本信息#database概况信息检查#检查database基本信息select*fromv$version;selectname,open_mode,log_modefromv$database;selectinstance_number,instance_name,statusfromgv$instance;showparametercpu_cshowparameterblock_sizeselectgroup#,thread#,members,bytes/1024/1024fromgv$log;showsgaselectcount(*)fromv$controlfileselectcount(*)fromv$tempfile;selectcount(*)fromv$datafile;********************************查看数据文件信息#检查表空间数据文件信息coltablespace_namefora30selecttablespace_name,sum(bytes)/1024/1024fromdba_temp_filesgroupbytablespace_name;#检查表空间SELECTTABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENTFROMDBA_TABLESPACES;#检查数据文件状态selectcount(*),statusfromv$datafilegroupbystatus;#检查表空间使用情况selectf.tablespace_name,a.total,f.free,(a.total-f.free)/1024"usedSIZE(G)",round((f.free/a.total)*100)"%Free"from(selecttablespace_name,sum(bytes/(1024*1024))totalfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,round(sum(bytes/(1024*1024)))freefromdba_free_spacegroupbytablespace_name)fWHEREa.tablespace_name=f.tablespace_name(+)orderby"%Free"/#查询临时segment使用情况COLusernameFORMATa10;COLsegtypeFORMATa10;SELECTusername,segtype,extents"ExtentsAllocated",blocks"BlocksAllocated"FROMv$tempseg_usage;#查看数据文件信息,若文件较多可以根据需要字段进行排序输出top10SELECTfs.phyrds"Reads",fs.phywrts"Writes",fs.avgiotim"AverageI/OTime",df.name"Datafile"FROMv$datafiledf,v$filestatfsWHEREdf.file#=fs.file#;#查看所有数据文件i/o情况,若文件太多,可以改写为top10select*(orderbyxxdesc)whererownum<=10COLtsFORMATa10HEADING"Tablespace";COLreadsFORMAT999990;COLwritesFORMAT999990;COLbrFORMAT999990HEADING"BlksRead";COLbwFORMAT999990HEADING"BlksWrite";COLrtimeFORMAT999990;COLwtimeFORMAT999990;SELECTts.nameASts,fs.phyrds"Reads",fs.phywrts"Writes",fs.phyblkrdASbr,fs.phyblkwrtASbw,fs.readtim"RTime",fs.writetim"WTime"FROMv$tablespacets,v$datafiledf,v$filestatfsWHEREts.ts#=df.ts#ANDdf.file#=fs.file#UNIONSELECTts.nameASts,ts.phyrds"Reads",ts.phywrts"Writes",ts.phyblkrdASbr,ts.phyblkwrtASbw,ts.readtim"RTime",ts.writetim"WTi