资源描述：
《oracle数据库性能健康检查脚本》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、oracle数据库性能健康检查脚本***********************查看茶木信息^database概况信息检查#检查database基木信息select*fromv$version;selectname,open_mode,1og_modefromv$database;seiectinstance_number,instance_name,statusfromgv$instance;showparametercpu_cshowparameterblock_sizeselectgroup#,thr
2、ead#,members,bytes/1024/1024fromgv$log;showsgaselectcount(*)fromv$controlfileselectcount(*)fromv$tempfile;selectcount(*)fromv$datafile;#检查表空间数据文件信息coltablespace_namefora30selecttablespace_name,sum(bytes)/1024/1024fromdba_temp_filesgroupbytablespace_namc;#检
3、查表空间SELECTTABLESPACE_NAME,EXTENTJVVNAGEMENT,ALLOCAT1ON_TYPE,SEGMENT_SPACE_MANAGEMENTFROMDBA_TABLESPACES;#检查数据文件状态selectcount(*),statusfromv$datafilegroupbystatus;#检查表空间使川情况selectf.tablespace_name,a.total,f.free,(a.total-f.free)/1024"usedSIZE(G)，z,round((f.
4、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_spacegroupbytablespacename)fWHEREa.tablespace_name=f.tablespace_n
5、ame(+)orderby"%Free"/#查询临吋segment使用悄况COLusernameFORMATalO;COLsegtypeFORMATalO;SELECTusername,segtype,extents"ExtentsAllocated",blocks"BlocksAllocated'7FROMv$tcmpsegusage;#查看数据文件信息，若文件较多可以根据碍要字段进行排序输出top10SELECTfs.phyrds"Reads",fs.phywrts"Writes",fs.avgioti
6、mAverage1/0Time",df.namez/Datafilez，FROMv$datafilcdf,v$f订estatfsWHEREdf.file#=fs.file#;#查看所有数据文件i/o情况,若文件太多,可以改写为top10select*(orderbyxxdesc)whererownum<=10COLtsFORMATalOHEADING"Tablespace";COLreadsFORMAT999990;COLwritesFORMAT999990;COLbrFORMAT999990HEADING
7、〃BlksRead〃；COLbwFORMAT999990HEADING〃BlksWrite〃；COLrtimeFORMAT999990;COLwtimeFORMAT999990;SELECTts.namcASts,fs.phyrds"Reads",fs.phywrts"Writes",fs.phyblkrdASbr,fs.phyblkwrtASbw,fs.readtim"RTime",fs.writetim"WTime"FROMv$tablespacets,v$datafiledf,v$filestatfs
8、WHEREts.ts#二df.ts#ANDdf.file#二fs.file#UNIONSELECTts.nameASts,ts.phyrds"Reads",ts.phywrts"Writes",ts.phyblkrdASbr,ts.phyblkwrtASbw,ts.readtim"RTime",ts.writetim"WTime"FROMv$tablespacets,v$tempfiletf,v$tempstat
