资源描述：
《监控数据库性能的SQL语句汇总》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、监控数据库性能的SQL语句汇总下面是一些监控数据库性能的SQL语句，希望对大家有帮助：1.监控事例的等待 select event, sum(decode(wait_Time,0,0,1))"Prev", sum(decode(wait_Time,0,1,0))"Curr", count(*)"Tot" from v$session_Wait groupbyevent orderby4; 2.回滚段的争用情况 select name,waits,gets,waits/gets"Ratio" from v$rollstata,v$rollnam
2、eb where a.usn=b.usn; 3.监控表空间的I/O比例 select df.tablespace_namename,df.file_name"file",f.phyrdspyr, f.phyblkrdpbr,f.phywrtspyw,f.phyblkwrtpbw from v$filestatf,dba_data_filesdf where f.file#=df.file_id orderbydf.tablespace_name; 4.监控文件系统的I/O比例 select substr(a.file#,1,2)"#",subs
3、tr(a.name,1,30)"Name", a.status,a.bytes,b.phyrds,b.phywrts from v$datafilea,v$filestatb where a.file#=b.file#; 5.在某个用户下找所有的索引 select user_indexes.table_name, user_indexes.index_name, uniqueness, column_name from user_ind_columns,user_indexes where user_ind_columns.index_nam
4、e=user_indexes.index_nameand user_ind_columns.table_name=user_indexes.table_name orderbyuser_indexes.table_type,user_indexes.table_name, user_indexes.index_name,column_position; 6.监控SGA的命中率 select a.value+b.value"logical_reads", c.value"phys_reads", round(100*((a.value+b.value
5、)-c.value) /(a.value+b.value))"BUFFERHITRATIO" from v$sysstata,v$sysstatb,v$sysstatc where a.statistic#=38andb.statistic#=39and c.statistic#=40; 7.监控SGA中字典缓冲区的命中率 select parameter,gets,Getmisses, getmisses/(gets+getmisses)*100"missratio", (1-(sum(getmisses)/(sum(gets)+sum(get
6、misses))))*100"Hitratio" from v$rowcache where gets+getmisses<>0 groupbyparameter,gets,getmisses; 8.监控SGA中共享缓存区的命中率，应该小于1% select sum(pins)"TotalPins",sum(reloads)"TotalReloads", sum(reloads)/sum(pins)*100libcache from v$librarycache; select sum(pinhits-reloads)/sum(pins)"hit
7、radio", sum(reloads)/sum(pins)"reloadpercent" from v$librarycache;9.显示所有数据库对象的类别和大小 select type, count(name)num_instances, sum(source_size)source_size, sum(parsed_size)parsed_size, sum(code_size)code_size, sum(error_size)error_size, sum(source_size)+sum(parsed_size) +sum(co
8、de_size)+sum
