资源描述：
《oracle_dba常用sql脚本》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、OracleSQL脚本命令监控SQL1.监控事例的等待：selectevent,sum(decode(wait_time,0,0,1))prev,sum(decode(wait_time,0,1,0))curr,count(*)fromv$session_waitgroupbyeventorderby4;2.回滚段的争用情况：selectname,waits,gets,waits/getsratiofromv$rollstata,v$rollnamebwherea.usn=b.usn;3.监控表空间的I/O比例：selec
2、tdf.tablespace_namename,df.file_name"file",f.phyrdspyr,f.phyblkrdpbr,f.phywrtspyw,f.phyblkwrtpbwfromv$filestatf,dba_data_filesdfwheref.file#=df.file_id4.监空文件系统的I/O比例：selectsubstr(a.file#,1,2)"#",substr(a.name,1,30)"name",a.status,a.bytes,b.phyrds,b.phywrtsfromv$da
3、tafilea,v$filestatbwherea.file#=b.file#5.在某个用户下找所有的索引：selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column_namefromuser_ind_columns,user_indexeswhereuser_ind_columns.index_name=user_indexes.index_nameanduser_ind_columns.table_name=user_indexes.t
4、able_nameorderbyuser_indexes.table_type,user_indexes.table_name,user_indexes.index_name,column_position;6.进程监控：selectdistinctp.spidunix_process,s.terminal,-56-to_char(s.logon_time,'YYYY/MON/DDHH24:MI')Logon_Time,s.usernamefromv$processp,v$sessionswherep.addr=s.pad
5、drorderby27.监控SGA中字典缓冲区的命中率selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100"missratio",(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio"fromv$rowcachewheregets+getmisses<>0groupbyparameter,gets,getmisses;8.监控SGA中共享缓存区的命中率，应该小于1%selectsum(p
6、ins)"TotalPins",sum(reloads)"TotalReloads",sum(reloads)/sum(pins)*100libcachefromv$librarycache;selectsum(pinhits-reloads)/sum(pins)"hitradio",sum(reloads)/sum(pins)"reloadpercent"fromv$librarycache;9.显示所有数据库对象的类别和大小selectcount(name)num_instances,type,sum(source_s
7、ize)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_requiredfromdba_object_sizegroupbytypeorderby2;10.监控SGA中重做日志缓存区的命中率，应该小于1%SELECTname,gets,misses,imme
8、diate_gets,immediate_misses,Decode(gets,0,0,misses/gets*100)ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate
