资源描述：
《DBA常用的一些性能优化方面的SQL语句》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、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$rollnameb where a.usn=b.usn; 3.监控表空间的I/O比例
2、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)"#",substr(a.name,1,30)"Name", a.status,a
3、.bytes,b.phyrds,b.phywrts from v$datafilea,v$filestatb where a.file#=b.file#; 5.在某个用户下找所有的索引及状态 selectb.table_name,b.index_name,uniqueness,column_name,b.statusfromuser_ind_columnsa,user_indexesbwherea.index_name=b.index_nameanda.table_name=b.table_nameorderbyb.table_type,b.table_name,b.
4、index_name,column_position;6.监控SGA的命中率 select a.value+b.value"logical_reads", c.value"phys_reads", round(100*((a.value+b.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
5、.监控SGA中字典缓冲区的命中率 select parameter,gets,Getmisses, getmisses/(gets+getmisses)*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(pi
6、ns)"TotalPins",sum(reloads)"TotalReloads", sum(reloads)/sum(pins)*100libcache from v$librarycache; select sum(pinhits-reloads)/sum(pins)"hitradio", sum(reloads)/sum(pins)"reloadpercent" from v$librarycache; 9.显示所有数据库对象的类别和大小 select type, count(name)num_instances,
7、 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(code_size)+sum(error_size)size_requi
