资源描述：
《监控数据库性能的语句》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、监控数据库性能的语句上这个论坛没几天，但收获不少，我还是贴点东西。下面是一些监控数据库性能的语句，希望对大家有帮助：监控数据库性能的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.回滚段的争
2、用情况 select name,waits,gets,waits/gets"Ratio" from v$rollstata,v$rollnameb 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$files
3、tatf,dba_data_filesdf where f.file#=df.file_id orderbydf.tablespace_name; 4.监控文件系统的I/O比例 select substr(a.file#,1,2)"#",substr(a.name,1,30)"Name", a.status,a.bytes,b.phyrds,b.phywrts from v$datafilea,v$filestatb where a.file#
4、=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_name=user_indexes.index_nameand user_ind_col
5、umns.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
6、.value)-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+getmiss
7、es)*100"missratio", (1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio" from v$rowcache where gets+getmisses<>0 groupbyparameter,gets,getmisses; 8.监控SGA中共享缓存区的命中率，应该小于1% select sum(pins)"TotalPins",sum(reloads)"TotalRelo
8、ads", sum(reloads)/sum(pins)*100libcache from v$librarycache; select sum(pinhits-reloads)/sum(pins)"hitradio", sum(reloads)/sum(pins)"reloadpercent" from v$librarycache; 9.显示所有数据库对象的类别和大小
