资源描述：
《oraclesql 性能查看》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、oracle性能监控2009-08-2508:56希望整理一个监控oracle性能的常用sql集锦，希望大家不断更加：1.监控事例的等待 selectevent,sum(decode(wait_Time,0,0,1))"Prev", sum(decode(wait_Time,0,1,0))"Curr",count(*)"Tot" fromv$session_Wait groupbyeventorderby4;2.回滚段的争用情况 selectname,waits,gets,waits/gets"Ratio" fromv$roll
2、stata,v$rollnameb wherea.usn=b.usn;3.监控表空间的I/O比例 selectdf.tablespace_namename,df.file_name"file",f.phyrdspyr, f.phyblkrdpbr,f.phywrtspyw,f.phyblkwrtpbw fromv$filestatf,dba_data_filesdf wheref.file#=df.file_id orderbydf.tablespace_name;4.监控文件系统的I/O比例 selectsubstr
3、(a.file#,1,2)"#",substr(a.name,1,30)"Name", a.status,a.bytes,b.phyrds,b.phywrts fromv$datafilea,v$filestatb wherea.file#=b.file#;5.在某个用户下找所有的索引 selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column_name fromuser_ind_columns,user_indexes whereuser_
4、ind_columns.index_name=user_indexes.index_name anduser_ind_columns.table_name=user_indexes.table_name orderbyuser_indexes.table_type,user_indexes.table_name, user_indexes.index_name,column_position;6.监控SGA的命中率 selecta.value+b.value"logical_reads",c.value"phys_reads",
5、round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFERHITRATIO" fromv$sysstata,v$sysstatb,v$sysstatc wherea.statistic#=38andb.statistic#=39 andc.statistic#=40;7.监控SGA中字典缓冲区的命中率 selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100"missratio", (1-(sum(
6、getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio" fromv$rowcache wheregets+getmisses<>0 groupbyparameter,gets,getmisses;8.监控SGA中共享缓存区的命中率，应该小于1% selectsum(pins)"TotalPins",sum(reloads)"TotalReloads", sum(reloads)/sum(pins)*100libcache fromv$librarycache; sel
7、ectsum(pinhits-reloads)/sum(pins)"hitradio",sum(reloads)/sum(pins)"reloadpercent" fromv$librarycache;9.显示所有数据库对象的类别和大小 selectcount(name)num_instances,type,sum(source_size)source_size, sum(parsed_size)parsed_size,sum(code_size)code_size,sum(error_size)error_size, sum(sour
8、ce_size)+sum(parsed
