資源描述：
《oracle_dba常用sql腳本》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。
1、OracleSQL腳本命令監(jiān)控SQL1.監(jiān)控事例的等待：selectevent,sum(decode(wait_time,0,0,1))prev,sum(decode(wait_time,0,1,0))curr,count(*)fromv$session_waitgroupbyeventorderby4;2.回滾段的爭(zhēng)用情況：selectname,waits,gets,waits/getsratiofromv$rollstata,v$rollnamebwherea.usn=b.usn;3.監(jiān)控表空間的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.監(jiān)空文件系統(tǒng)的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.在某個(gè)用戶下找所有的索引：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.進(jìn)程監(jiān)控：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.監(jiān)控SGA中字典緩沖區(qū)的命中率selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100"missratio",(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio"fromv$rowcachewheregets+getmisses<>0groupbyparameter,gets,getmisses;8.監(jiān)控SGA中共享緩存區(qū)的命中率，應(yīng)該小于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.顯示所有數(shù)據(jù)庫(kù)對(duì)象的類別和大小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.監(jiān)控SGA中重做日志緩存區(qū)的命中率，應(yīng)該小于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
